-- =============================================================================
-- CetaPOS — Full schema + Super Admin seed
-- Generated from the Sequelize models (source of truth) + applied migrations.
-- Safe to import into a FRESH, EMPTY database (e.g. via phpMyAdmin → Import).
--
-- What it creates:
--   * All 33 tables with primary keys, foreign keys, ENUMs, JSON columns and the
--     unique/lookup indexes added by migrations (order_number, idempotency_key,
--     stocks.shop_id, purchases.shop_id).
--   * A "Super Admin" role with every permission, and a "Sales Person" role.
--   * A superadmin login, plus a starter Business and Shop so the app is usable.
--
-- LOGIN (change the password immediately after first sign-in):
--   email:    superadmin@pos.com
--   username: superadmin
--   password: ChangeMe@123
--
-- Notes:
--   * Tables use CREATE TABLE IF NOT EXISTS. The CREATE INDEX / INSERT statements
--     are NOT idempotent — run this once on a clean DB.
--   * FOREIGN_KEY_CHECKS is disabled around the CREATE block so table order never
--     matters; tables are already listed parents-first regardless.
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- ---------------------------------------------------------------------------
-- Tables
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `roles` (`id` BIGINT auto_increment , `name` VARCHAR(255), `permissions` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `shops` (`id` BIGINT auto_increment , `name` VARCHAR(255) NOT NULL, `description` TEXT, `address` JSON, `contact` JSON, `currency` VARCHAR(10) DEFAULT 'GHS', `tax_rate` DECIMAL(5,2) DEFAULT 0, `payment_methods` JSON, `status` TINYINT DEFAULT 1, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `businesses` (`id` INTEGER auto_increment , `name` VARCHAR(255) NOT NULL DEFAULT 'My Business', `address` JSON, `contact` JSON, `tax_rate` DECIMAL(5,2) DEFAULT 0, `currency` VARCHAR(10) DEFAULT 'GHS', `logo_url` VARCHAR(255), `receipt_notes` TEXT, `printer_type` ENUM('normal', 'terminal') DEFAULT 'normal', `allow_negative_stock` TINYINT(1) DEFAULT false COMMENT 'Allow selling products with insufficient stock (negative stock tracking)', `imagekit_public_key` VARCHAR(255), `imagekit_private_key` VARCHAR(255), `imagekit_url_endpoint` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `pcategories` (`id` BIGINT auto_increment , `name` VARCHAR(255) NOT NULL, `slug` VARCHAR(255), `image` VARCHAR(255), `status` INTEGER DEFAULT 1, `tax` DECIMAL(11,2) DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `psub_categories` (`id` BIGINT auto_increment , `category_id` BIGINT, `name` VARCHAR(255) NOT NULL, `slug` VARCHAR(255), `status` INTEGER DEFAULT 1, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`category_id`) REFERENCES `pcategories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `brands` (`id` BIGINT auto_increment , `name` VARCHAR(255) NOT NULL, `slug` VARCHAR(255), `image` VARCHAR(255), `status` INTEGER DEFAULT 1, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `units` (`id` BIGINT auto_increment , `name` VARCHAR(255) NOT NULL, `short_name` VARCHAR(255), `allow_decimal` TINYINT DEFAULT 0, `status` INTEGER DEFAULT 1, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `suppliers` (`id` BIGINT auto_increment , `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255), `phone` VARCHAR(255), `address` TEXT, `status` INTEGER DEFAULT 1, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `customers` (`id` BIGINT auto_increment , `name` VARCHAR(255), `email` VARCHAR(255), `phone` VARCHAR(255), `address` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `products` (`id` BIGINT auto_increment , `title` VARCHAR(255) NOT NULL, `slug` VARCHAR(255), `barcode` VARCHAR(255) UNIQUE, `feature_image` VARCHAR(255), `brochure_url` VARCHAR(255), `summary` TEXT, `description` TEXT, `variations` TEXT, `addons` TEXT, `category_id` BIGINT, `subcategory_id` BIGINT, `brand_id` BIGINT, `unit_id` BIGINT, `shop_id` BIGINT, `buying_price` DECIMAL(11,2) DEFAULT 0, `current_price` DECIMAL(11,2) DEFAULT 0, `wholesale_price` DECIMAL(11,2) DEFAULT 0, `previous_price` DECIMAL(11,2) DEFAULT 0, `rating` DECIMAL(11,2) DEFAULT 0, `status` INTEGER DEFAULT 1, `is_feature` TINYINT DEFAULT 0, `is_special` TINYINT DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`category_id`) REFERENCES `pcategories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`subcategory_id`) REFERENCES `psub_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `product_units` (`id` BIGINT auto_increment , `product_id` BIGINT NOT NULL, `unit_id` BIGINT, `name` VARCHAR(255), `factor_to_base` DECIMAL(12,3) NOT NULL DEFAULT 1, `price` DECIMAL(11,2) NOT NULL DEFAULT 0, `wholesale_price` DECIMAL(11,2), `barcode` VARCHAR(255), `is_base` TINYINT DEFAULT 0, `status` INTEGER DEFAULT 1, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `admins` (`id` BIGINT auto_increment , `role_id` BIGINT, `shop_id` BIGINT, `allowed_shops` TEXT COMMENT 'JSON array of shop IDs user can view data from', `shop_sell_permissions` TEXT COMMENT 'JSON array of shop IDs user can sell from (in addition to their assigned shop_id)', `username` VARCHAR(255), `email` VARCHAR(255) UNIQUE, `first_name` VARCHAR(255), `last_name` VARCHAR(255), `image` VARCHAR(255), `password` VARCHAR(255), `status` TINYINT DEFAULT 1, `timezone` VARCHAR(100) DEFAULT 'UTC', `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `purchases` (`id` BIGINT auto_increment , `supplier_id` BIGINT NOT NULL, `invoice_number` VARCHAR(255), `subtotal` DECIMAL(11,2) DEFAULT 0, `discount_type` ENUM('none', 'percentage', 'fixed') DEFAULT 'none', `discount_value` DECIMAL(11,2) DEFAULT 0, `discount_amount` DECIMAL(11,2) DEFAULT 0, `tax_rate` DECIMAL(5,2) DEFAULT 0, `tax_amount` DECIMAL(11,2) DEFAULT 0, `total` DECIMAL(11,2) NOT NULL, `amount_paid` DECIMAL(11,2) DEFAULT 0, `payment_status` ENUM('paid', 'partial', 'unpaid') DEFAULT 'unpaid', `purchase_status` ENUM('ordered', 'received', 'pending', 'cancelled') DEFAULT 'received', `due_date` DATETIME, `received_date` DATETIME, `notes` TEXT, `received_by` BIGINT, `shop_id` BIGINT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`received_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `purchase_items` (`id` BIGINT auto_increment , `purchase_id` BIGINT NOT NULL, `product_id` BIGINT NOT NULL, `quantity` INTEGER NOT NULL DEFAULT 0, `unit_id` BIGINT, `unit_name` VARCHAR(255), `conversion_factor` DECIMAL(12,3) DEFAULT 1, `base_qty` INTEGER, `unit_cost` DECIMAL(11,2) DEFAULT 0, `total_cost` DECIMAL(11,2) DEFAULT 0, `batch_number` VARCHAR(255), `expiry_date` DATE, `notes` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `stocks` (`id` BIGINT auto_increment , `product_id` BIGINT NOT NULL, `supplier_id` BIGINT, `purchase_id` BIGINT, `shop_id` BIGINT, `quantity` INTEGER DEFAULT 0, `cost` DECIMAL(11,2), `expiry_date` DATE, `batch_number` VARCHAR(255), `type` ENUM('simple', 'serialized') DEFAULT 'simple', `data` JSON, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `product_orders` (`id` BIGINT auto_increment , `type` ENUM('retail', 'wholesale') DEFAULT 'retail', `payment_status` ENUM('paid', 'partial', 'unpaid') DEFAULT 'paid', `status` ENUM('completed', 'pending', 'returned', 'partial_return', 'cancelled', 'draft') DEFAULT 'completed', `tax` DECIMAL(11,2) DEFAULT 0, `discount` DECIMAL(11,2) DEFAULT 0, `user_id` BIGINT, `shop_id` BIGINT, `billing_fname` VARCHAR(255), `billing_lname` VARCHAR(255), `billing_address` VARCHAR(255), `billing_email` VARCHAR(255), `billing_number` VARCHAR(255), `total` DECIMAL(11,2), `method` VARCHAR(255), `order_number` VARCHAR(255), `token_no` INTEGER, `due_date` DATETIME, `amount_paid` DECIMAL(11,2) DEFAULT 0, `idempotency_key` VARCHAR(64), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `customer_id` BIGINT, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `order_items` (`id` BIGINT auto_increment , `product_order_id` BIGINT NOT NULL, `product_id` BIGINT NOT NULL, `title` VARCHAR(255), `qty` INTEGER, `unit_id` BIGINT, `unit_name` VARCHAR(255), `conversion_factor` DECIMAL(12,3) DEFAULT 1, `base_qty` INTEGER, `image` VARCHAR(255), `variations` TEXT, `addons` TEXT, `product_price` DECIMAL(11,2), `total` DECIMAL(11,2), `returned_qty` INTEGER DEFAULT 0, `imei` VARCHAR(255) COMMENT 'Optional IMEI or Serial Number for the sold item', `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`product_order_id`) REFERENCES `product_orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `stock_histories` (`id` BIGINT auto_increment , `stock_id` BIGINT NOT NULL, `user_id` BIGINT, `previous_quantity` INTEGER NOT NULL DEFAULT 0, `new_quantity` INTEGER NOT NULL DEFAULT 0, `change_quantity` INTEGER NOT NULL, `reason` VARCHAR(255), `type` ENUM('initial', 'adjustment', 'sale', 'return', 'purchase', 'transfer') DEFAULT 'adjustment', `supplier_id` BIGINT, `purchase_id` BIGINT, `sale_id` BIGINT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`stock_id`) REFERENCES `stocks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`user_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`sale_id`) REFERENCES `product_orders` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `stock_reservations` (`id` BIGINT auto_increment , `product_id` BIGINT NOT NULL, `shop_id` BIGINT NOT NULL, `quantity` INTEGER NOT NULL DEFAULT 0, `reason` VARCHAR(255), `released` TINYINT NOT NULL DEFAULT 0, `created_by` BIGINT, `released_by` BIGINT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `stock_transfers` (`id` BIGINT auto_increment , `reference_number` VARCHAR(50) NOT NULL UNIQUE, `from_shop_id` BIGINT NOT NULL, `to_shop_id` BIGINT NOT NULL, `transferred_by` BIGINT NOT NULL, `status` ENUM('pending', 'received', 'cancelled') NOT NULL DEFAULT 'received', `received_by` BIGINT, `received_at` DATETIME, `notes` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`from_shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`to_shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`transferred_by`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`received_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `stock_transfer_items` (`id` BIGINT auto_increment , `transfer_id` BIGINT NOT NULL, `product_id` BIGINT NOT NULL, `quantity` INTEGER NOT NULL, `expiry_date` DATE, `cost` DECIMAL(11,2), `batch_number` VARCHAR(255), `source_stock_id` BIGINT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`transfer_id`) REFERENCES `stock_transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `purchase_returns` (`id` BIGINT auto_increment , `purchase_id` BIGINT NOT NULL, `return_number` VARCHAR(255), `return_date` DATETIME, `subtotal` DECIMAL(11,2) DEFAULT 0, `total` DECIMAL(11,2) NOT NULL, `refund_amount` DECIMAL(11,2) DEFAULT 0, `refund_status` ENUM('pending', 'refunded', 'credited') DEFAULT 'pending', `reason` TEXT, `status` ENUM('pending', 'approved', 'rejected', 'completed') DEFAULT 'pending', `processed_by` BIGINT, `notes` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`processed_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `purchase_return_items` (`id` BIGINT auto_increment , `return_id` BIGINT NOT NULL, `product_id` BIGINT NOT NULL, `quantity` INTEGER NOT NULL, `unit_cost` DECIMAL(11,2) DEFAULT 0, `total` DECIMAL(11,2) DEFAULT 0, `reason` ENUM('damaged', 'defective', 'wrong_item', 'excess', 'expired', 'other') DEFAULT 'defective', `notes` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`return_id`) REFERENCES `purchase_returns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `sale_returns` (`id` BIGINT auto_increment , `sale_id` BIGINT NOT NULL, `return_number` VARCHAR(255), `return_date` DATETIME, `return_type` ENUM('full', 'partial') NOT NULL, `reason` TEXT, `refund_amount` DECIMAL(11,2) DEFAULT 0, `refund_method` VARCHAR(255) DEFAULT 'Cash', `total_qty_returned` INTEGER DEFAULT 0, `processed_by` BIGINT, `shop_id` BIGINT, `notes` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`sale_id`) REFERENCES `product_orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`processed_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `sale_return_items` (`id` BIGINT auto_increment , `return_id` BIGINT NOT NULL, `sale_item_id` BIGINT, `product_id` BIGINT NOT NULL, `product_name` VARCHAR(255), `quantity_returned` INTEGER NOT NULL, `unit_price` DECIMAL(11,2) DEFAULT 0, `total` DECIMAL(11,2) DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`return_id`) REFERENCES `sale_returns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`sale_item_id`) REFERENCES `order_items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `payments` (`id` BIGINT auto_increment , `sale_id` BIGINT NOT NULL, `amount` DECIMAL(11,2) NOT NULL, `payment_method` VARCHAR(255) DEFAULT 'Cash', `reference` VARCHAR(255), `notes` TEXT, `received_by` BIGINT, `payment_date` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`sale_id`) REFERENCES `product_orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`received_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `supplier_payments` (`id` BIGINT auto_increment , `purchase_id` BIGINT NOT NULL, `amount` DECIMAL(11,2) NOT NULL, `payment_method` VARCHAR(255) DEFAULT 'Cash', `reference` VARCHAR(255), `notes` TEXT, `paid_by` BIGINT, `payment_date` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`paid_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `trade_ins` (`id` BIGINT auto_increment , `sale_id` BIGINT, `product_name` VARCHAR(255), `description` TEXT, `value` DECIMAL(11,2) DEFAULT 0, `condition` VARCHAR(255), `resellable` TINYINT DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`sale_id`) REFERENCES `product_orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `surplus_sales` (`id` BIGINT auto_increment , `amount` DECIMAL(11,2) NOT NULL COMMENT 'Surplus/additional sales amount', `reason` TEXT NOT NULL COMMENT 'Reason for the surplus sale', `sales_date` DATETIME NOT NULL COMMENT 'The date this surplus is attributed to', `user_id` BIGINT NOT NULL COMMENT 'User who recorded this surplus', `shop_id` BIGINT COMMENT 'Shop where this surplus was recorded', `notes` TEXT COMMENT 'Additional notes', `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `expenses` (`id` BIGINT auto_increment , `amount` DECIMAL(11,2) NOT NULL, `description` TEXT, `category` VARCHAR(255), `date` DATETIME, `user_id` BIGINT, `supplier_id` BIGINT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `activities` (`id` BIGINT auto_increment , `user_id` BIGINT, `description` TEXT, `type` VARCHAR(255) DEFAULT 'general', `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `account_histories` (`id` BIGINT auto_increment , `user_id` BIGINT NOT NULL, `dates` JSON, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `price_histories` (`id` BIGINT auto_increment , `product_id` BIGINT NOT NULL, `price_type` ENUM('buying_price', 'current_price', 'wholesale_price') NOT NULL, `old_price` DECIMAL(11,2) NOT NULL DEFAULT 0, `new_price` DECIMAL(11,2) NOT NULL DEFAULT 0, `changed_by` BIGINT, `reason` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`changed_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Migration-only indexes (unique constraints + lookup indexes not inline above)
-- ---------------------------------------------------------------------------
CREATE UNIQUE INDEX `idx_order_number_unique` ON `product_orders` (`order_number`);
CREATE UNIQUE INDEX `uq_product_orders_idempotency_key` ON `product_orders` (`idempotency_key`);
CREATE INDEX `idx_stocks_shop_id` ON `stocks` (`shop_id`);
CREATE INDEX `idx_purchases_shop_id` ON `purchases` (`shop_id`);

-- ---------------------------------------------------------------------------
-- Seed: roles, superadmin user, starter business + shop
-- ---------------------------------------------------------------------------

-- Roles. "Super Admin" is the privileged role: its name triggers the Super Admin
-- bypass in auth.middleware, its substring satisfies the shop-scope helpers, and
-- it carries every permission so all permission gates pass.
INSERT INTO `roles` (`id`, `name`, `permissions`, `createdAt`, `updatedAt`) VALUES
(1, 'Super Admin', '["Dashboard","ViewDashboardStats","Sales","ViewAllSales","Products","AddProducts","EditProducts","DeleteProducts","ViewAllProducts","ViewCostPrice","Purchases","Inventory","Stock","Transfers","Customers","Suppliers","Users","Roles","Reports","ViewReports","Settings","Expenses","Returns","ProcessCrossShopReturns","AcceptPayments","AcceptCrossShopPayments","ManagePayments","ManageSurplusSales"]', NOW(), NOW()),
(2, 'Sales Person', '["Dashboard","Sales","Products","Customers"]', NOW(), NOW());

-- Starter business (settings) and one shop so the system is immediately usable.
INSERT INTO `businesses` (`id`, `name`, `currency`, `printer_type`, `allow_negative_stock`, `createdAt`, `updatedAt`) VALUES
(1, 'My Business', 'GHS', 'normal', 0, NOW(), NOW());

INSERT INTO `shops` (`id`, `name`, `description`, `currency`, `status`, `createdAt`, `updatedAt`) VALUES
(1, 'Main Shop', 'Default shop', 'GHS', 1, NOW(), NOW());

-- Superadmin login. Password 'ChangeMe@123' (bcrypt, 10 rounds) — CHANGE IT after
-- first sign-in. Assigned to the Super Admin role; shop_id NULL = sees all shops.
INSERT INTO `admins` (`id`, `role_id`, `shop_id`, `allowed_shops`, `shop_sell_permissions`, `username`, `email`, `first_name`, `last_name`, `password`, `status`, `timezone`, `createdAt`, `updatedAt`) VALUES
(1, 1, NULL, '[]', '[]', 'superadmin', 'superadmin@pos.com', 'Super', 'Admin', '$2b$10$ITkDtoiOJTVfrXpAyFJHOeoFpFZ48qiWzL4mO4qEnOWOHvvwQ83q.', 1, 'UTC', NOW(), NOW());

SET FOREIGN_KEY_CHECKS = 1;
-- Done. Sign in with superadmin@pos.com / ChangeMe@123 and change the password.