-- ============================================================
--  MJPOS – Fresh Setup (Empty Database)
--  Only superadmin user — no sample data
--  Import via phpMyAdmin → Import
-- ============================================================

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

-- ── Create & select database ─────────────────────────────────
DROP DATABASE IF EXISTS `mjpos_db`;
CREATE DATABASE `mjpos_db`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE `mjpos_db`;

-- ============================================================
--  TABLES
-- ============================================================

CREATE TABLE `roles` (
  `id`          int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_name`   varchar(50)      NOT NULL,
  `description` varchar(255)     DEFAULT NULL,
  `created_at`  timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `branches` (
  `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name`       varchar(150)     NOT NULL,
  `address`    text             DEFAULT NULL,
  `phone`      varchar(20)      DEFAULT NULL,
  `email`      varchar(100)     DEFAULT NULL,
  `is_main`    tinyint(1)       NOT NULL DEFAULT 0,
  `status`     enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
  `id`            int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id`       int(10) unsigned NOT NULL,
  `branch_id`     int(10) unsigned DEFAULT NULL,
  `name`          varchar(100)     NOT NULL,
  `username`      varchar(50)      NOT NULL,
  `email`         varchar(100)     DEFAULT NULL,
  `password_hash` varchar(255)     NOT NULL,
  `phone`         varchar(20)      DEFAULT NULL,
  `avatar`        varchar(255)     DEFAULT NULL,
  `status`        enum('active','inactive') DEFAULT 'active',
  `last_login`    datetime         DEFAULT NULL,
  `created_at`    timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at`    timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `fk_users_role`   (`role_id`),
  KEY `fk_users_branch` (`branch_id`),
  CONSTRAINT `fk_users_role`   FOREIGN KEY (`role_id`)   REFERENCES `roles`    (`id`),
  CONSTRAINT `fk_users_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permissions` (
  `id`             int(10) unsigned NOT NULL AUTO_INCREMENT,
  `module`         varchar(50)      NOT NULL,
  `action`         varchar(50)      NOT NULL,
  `permission_key` varchar(100)     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_key`  (`permission_key`),
  UNIQUE KEY `uq_module_action`(`module`,`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_permissions` (
  `role_id`       int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `fk_rp_perm` (`permission_id`),
  CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions`  (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`)       REFERENCES `roles`         (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
  `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id`     int(10) unsigned    DEFAULT NULL,
  `action`      varchar(100)        NOT NULL,
  `module`      varchar(50)         NOT NULL,
  `description` text                DEFAULT NULL,
  `old_value`   longtext            CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_value`)),
  `new_value`   longtext            CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_value`)),
  `ip_address`  varchar(45)         DEFAULT NULL,
  `user_agent`  varchar(255)        DEFAULT NULL,
  `created_at`  timestamp           NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_user`   (`user_id`),
  KEY `idx_audit_module` (`module`),
  KEY `idx_audit_date`   (`created_at`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `settings` (
  `id`           int(10) unsigned NOT NULL AUTO_INCREMENT,
  `setting_key`  varchar(100)     NOT NULL,
  `setting_value`text             DEFAULT NULL,
  `updated_at`   timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `categories` (
  `id`            int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100)     NOT NULL,
  `description`   text             DEFAULT NULL,
  `status`        enum('active','inactive') DEFAULT 'active',
  `created_at`    timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `brands` (
  `id`          int(10) unsigned NOT NULL AUTO_INCREMENT,
  `brand_name`  varchar(100)     NOT NULL,
  `description` text             DEFAULT NULL,
  `status`      enum('active','inactive') DEFAULT 'active',
  `created_at`  timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `units` (
  `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
  `unit_name`  varchar(50)      NOT NULL,
  `short_name` varchar(20)      NOT NULL,
  `created_at` timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `id`               int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id`      int(10) unsigned DEFAULT NULL,
  `brand_id`         int(10) unsigned DEFAULT NULL,
  `unit_id`          int(10) unsigned DEFAULT NULL,
  `product_code`     varchar(50)      NOT NULL,
  `barcode`          varchar(100)     DEFAULT NULL,
  `product_name`     varchar(200)     NOT NULL,
  `generic_name`     varchar(200)     DEFAULT NULL,
  `description`      text             DEFAULT NULL,
  `cost_price`       decimal(12,2)    NOT NULL DEFAULT 0.00,
  `selling_price`    decimal(12,2)    NOT NULL DEFAULT 0.00,
  `wholesale_price`  decimal(12,2)    DEFAULT 0.00,
  `reorder_level`    int(11)          NOT NULL DEFAULT 0,
  `current_stock`    decimal(12,2)    NOT NULL DEFAULT 0.00,
  `tax_percent`      decimal(5,2)     DEFAULT 0.00,
  `discount_percent` decimal(5,2)     DEFAULT 0.00,
  `product_image`    varchar(255)     DEFAULT NULL,
  `is_batch`         tinyint(1)       NOT NULL DEFAULT 0,
  `is_serial`        tinyint(1)       NOT NULL DEFAULT 0,
  `status`           enum('active','inactive') DEFAULT 'active',
  `created_at`       timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at`       timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `fk_prod_cat`   (`category_id`),
  KEY `fk_prod_brand` (`brand_id`),
  KEY `fk_prod_unit`  (`unit_id`),
  KEY `idx_prod_name` (`product_name`),
  KEY `idx_prod_status`(`status`),
  CONSTRAINT `fk_prod_brand` FOREIGN KEY (`brand_id`)    REFERENCES `brands`     (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prod_cat`   FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prod_unit`  FOREIGN KEY (`unit_id`)     REFERENCES `units`      (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_batches` (
  `id`             int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id`     int(10) unsigned NOT NULL,
  `batch_no`       varchar(100)     NOT NULL,
  `mfg_date`       date             DEFAULT NULL,
  `expiry_date`    date             DEFAULT NULL,
  `purchase_price` decimal(12,2)    NOT NULL DEFAULT 0.00,
  `quantity`       decimal(12,2)    NOT NULL DEFAULT 0.00,
  `created_at`     timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_batch_expiry` (`expiry_date`),
  KEY `idx_batch_prod`   (`product_id`),
  CONSTRAINT `fk_batch_prod` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stock_movements` (
  `id`            bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id`    int(10) unsigned    NOT NULL,
  `batch_id`      int(10) unsigned    DEFAULT NULL,
  `movement_type` enum('opening','purchase','purchase_return','sale','sale_return','adjustment_add','adjustment_sub','transfer_in','transfer_out','damaged') NOT NULL,
  `quantity_in`   decimal(12,2)       NOT NULL DEFAULT 0.00,
  `quantity_out`  decimal(12,2)       NOT NULL DEFAULT 0.00,
  `stock_after`   decimal(12,2)       NOT NULL DEFAULT 0.00,
  `reference_no`  varchar(50)         DEFAULT NULL,
  `note`          text                DEFAULT NULL,
  `created_by`    int(10) unsigned    DEFAULT NULL,
  `created_at`    timestamp           NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_sm_batch`   (`batch_id`),
  KEY `fk_sm_user`    (`created_by`),
  KEY `idx_sm_product`(`product_id`),
  KEY `idx_sm_type`   (`movement_type`),
  KEY `idx_sm_date`   (`created_at`),
  CONSTRAINT `fk_sm_batch`   FOREIGN KEY (`batch_id`)   REFERENCES `product_batches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sm_product` FOREIGN KEY (`product_id`) REFERENCES `products`        (`id`),
  CONSTRAINT `fk_sm_user`    FOREIGN KEY (`created_by`) REFERENCES `users`           (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customers` (
  `id`              int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_name`   varchar(150)     NOT NULL,
  `phone`           varchar(20)      DEFAULT NULL,
  `email`           varchar(100)     DEFAULT NULL,
  `address`         text             DEFAULT NULL,
  `credit_limit`    decimal(12,2)    NOT NULL DEFAULT 0.00,
  `opening_balance` decimal(12,2)    NOT NULL DEFAULT 0.00,
  `current_balance` decimal(12,2)    NOT NULL DEFAULT 0.00,
  `loyalty_points`  int(10) unsigned NOT NULL DEFAULT 0,
  `status`          enum('active','inactive') DEFAULT 'active',
  `created_at`      timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at`      timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `suppliers` (
  `id`              int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_name`   varchar(150)     NOT NULL,
  `contact_person`  varchar(100)     DEFAULT NULL,
  `phone`           varchar(20)      DEFAULT NULL,
  `email`           varchar(100)     DEFAULT NULL,
  `address`         text             DEFAULT NULL,
  `opening_balance` decimal(12,2)    NOT NULL DEFAULT 0.00,
  `current_balance` decimal(12,2)    NOT NULL DEFAULT 0.00,
  `status`          enum('active','inactive') DEFAULT 'active',
  `created_at`      timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at`      timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sales` (
  `id`             int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id`    int(10) unsigned DEFAULT NULL,
  `branch_id`      int(10) unsigned DEFAULT NULL,
  `invoice_no`     varchar(50)      NOT NULL,
  `sale_date`      datetime         NOT NULL,
  `subtotal`       decimal(12,2)    NOT NULL DEFAULT 0.00,
  `discount`       decimal(12,2)    NOT NULL DEFAULT 0.00,
  `tax`            decimal(12,2)    NOT NULL DEFAULT 0.00,
  `total`          decimal(12,2)    NOT NULL DEFAULT 0.00,
  `paid`           decimal(12,2)    NOT NULL DEFAULT 0.00,
  `balance`        decimal(12,2)    NOT NULL DEFAULT 0.00,
  `payment_status` enum('paid','partial','due') DEFAULT 'paid',
  `note`           text             DEFAULT NULL,
  `status`         enum('completed','held','cancelled','returned') DEFAULT 'completed',
  `created_by`     int(10) unsigned DEFAULT NULL,
  `created_at`     timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at`     timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_no` (`invoice_no`),
  KEY `fk_sale_branch`   (`branch_id`),
  KEY `idx_sale_date`    (`sale_date`),
  KEY `idx_sale_customer`(`customer_id`),
  KEY `idx_sale_cashier` (`created_by`),
  KEY `idx_sale_status`  (`status`),
  CONSTRAINT `fk_sale_branch`   FOREIGN KEY (`branch_id`)   REFERENCES `branches`  (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sale_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sale_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sale_items` (
  `id`            int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id`       int(10) unsigned NOT NULL,
  `product_id`    int(10) unsigned NOT NULL,
  `batch_id`      int(10) unsigned DEFAULT NULL,
  `quantity`      decimal(12,2)    NOT NULL,
  `cost_price`    decimal(12,2)    NOT NULL,
  `selling_price` decimal(12,2)    NOT NULL,
  `discount`      decimal(12,2)    NOT NULL DEFAULT 0.00,
  `tax`           decimal(12,2)    NOT NULL DEFAULT 0.00,
  `total`         decimal(12,2)    NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_si_batch`    (`batch_id`),
  KEY `idx_si_sale`    (`sale_id`),
  KEY `idx_si_product` (`product_id`),
  CONSTRAINT `fk_si_batch`   FOREIGN KEY (`batch_id`)   REFERENCES `product_batches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_si_product` FOREIGN KEY (`product_id`) REFERENCES `products`        (`id`),
  CONSTRAINT `fk_si_sale`    FOREIGN KEY (`sale_id`)    REFERENCES `sales`           (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sale_payments` (
  `id`             int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id`        int(10) unsigned NOT NULL,
  `payment_method` enum('cash','card','bank_transfer','mobile','credit','cheque') NOT NULL,
  `amount`         decimal(12,2)    NOT NULL,
  `reference`      varchar(100)     DEFAULT NULL,
  `note`           varchar(255)     DEFAULT NULL,
  `created_at`     timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sp_sale`   (`sale_id`),
  KEY `idx_sp_method` (`payment_method`),
  CONSTRAINT `fk_sp_sale` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sales_returns` (
  `id`            int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id`       int(10) unsigned NOT NULL,
  `customer_id`   int(10) unsigned DEFAULT NULL,
  `invoice_no`    varchar(50)      NOT NULL,
  `return_date`   date             NOT NULL,
  `total`         decimal(12,2)    NOT NULL DEFAULT 0.00,
  `refund_method` enum('cash','card','bank_transfer','mobile','credit') DEFAULT 'cash',
  `note`          text             DEFAULT NULL,
  `created_by`    int(10) unsigned DEFAULT NULL,
  `created_at`    timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_no` (`invoice_no`),
  KEY `fk_sr_sale`     (`sale_id`),
  KEY `fk_sr_customer` (`customer_id`),
  KEY `fk_sr_user`     (`created_by`),
  CONSTRAINT `fk_sr_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sr_sale`     FOREIGN KEY (`sale_id`)     REFERENCES `sales`     (`id`),
  CONSTRAINT `fk_sr_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sales_return_items` (
  `id`              int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sales_return_id` int(10) unsigned NOT NULL,
  `product_id`      int(10) unsigned NOT NULL,
  `quantity`        decimal(12,2)    NOT NULL,
  `selling_price`   decimal(12,2)    NOT NULL,
  `total`           decimal(12,2)    NOT NULL,
  `reason`          varchar(255)     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sri_return`  (`sales_return_id`),
  KEY `fk_sri_product` (`product_id`),
  CONSTRAINT `fk_sri_product` FOREIGN KEY (`product_id`)      REFERENCES `products`      (`id`),
  CONSTRAINT `fk_sri_return`  FOREIGN KEY (`sales_return_id`) REFERENCES `sales_returns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_payments` (
  `id`             int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id`    int(10) unsigned NOT NULL,
  `sale_id`        int(10) unsigned DEFAULT NULL,
  `amount`         decimal(12,2)    NOT NULL,
  `payment_date`   date             NOT NULL,
  `payment_method` enum('cash','card','bank_transfer','mobile','cheque') DEFAULT 'cash',
  `reference`      varchar(100)     DEFAULT NULL,
  `note`           text             DEFAULT NULL,
  `created_by`     int(10) unsigned DEFAULT NULL,
  `created_at`     timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_cpay_sale` (`sale_id`),
  KEY `fk_cpay_user` (`created_by`),
  KEY `idx_cpay_customer` (`customer_id`),
  KEY `idx_cpay_date`     (`payment_date`),
  CONSTRAINT `fk_cpay_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `fk_cpay_sale`     FOREIGN KEY (`sale_id`)     REFERENCES `sales`     (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cpay_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchases` (
  `id`             int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id`    int(10) unsigned NOT NULL,
  `branch_id`      int(10) unsigned DEFAULT NULL,
  `invoice_no`     varchar(50)      NOT NULL,
  `purchase_date`  date             NOT NULL,
  `subtotal`       decimal(12,2)    NOT NULL DEFAULT 0.00,
  `discount`       decimal(12,2)    NOT NULL DEFAULT 0.00,
  `tax`            decimal(12,2)    NOT NULL DEFAULT 0.00,
  `total`          decimal(12,2)    NOT NULL DEFAULT 0.00,
  `paid`           decimal(12,2)    NOT NULL DEFAULT 0.00,
  `balance`        decimal(12,2)    NOT NULL DEFAULT 0.00,
  `payment_status` enum('paid','partial','due') DEFAULT 'due',
  `payment_method` enum('cash','card','bank_transfer','mobile','credit') DEFAULT 'cash',
  `note`           text             DEFAULT NULL,
  `status`         enum('received','pending','cancelled') DEFAULT 'received',
  `created_by`     int(10) unsigned DEFAULT NULL,
  `created_at`     timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at`     timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_no` (`invoice_no`),
  KEY `fk_pur_branch`  (`branch_id`),
  KEY `fk_pur_user`    (`created_by`),
  KEY `idx_pur_date`   (`purchase_date`),
  KEY `idx_pur_supplier`(`supplier_id`),
  KEY `idx_pur_status` (`status`),
  CONSTRAINT `fk_pur_branch`   FOREIGN KEY (`branch_id`)   REFERENCES `branches`  (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pur_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_pur_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchase_items` (
  `id`          int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_id` int(10) unsigned NOT NULL,
  `product_id`  int(10) unsigned NOT NULL,
  `batch_id`    int(10) unsigned DEFAULT NULL,
  `quantity`    decimal(12,2)    NOT NULL,
  `cost_price`  decimal(12,2)    NOT NULL,
  `discount`    decimal(12,2)    NOT NULL DEFAULT 0.00,
  `tax`         decimal(12,2)    NOT NULL DEFAULT 0.00,
  `total`       decimal(12,2)    NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pi_batch`    (`batch_id`),
  KEY `idx_pi_purchase`(`purchase_id`),
  KEY `idx_pi_product` (`product_id`),
  CONSTRAINT `fk_pi_batch`    FOREIGN KEY (`batch_id`)   REFERENCES `product_batches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pi_product`  FOREIGN KEY (`product_id`) REFERENCES `products`        (`id`),
  CONSTRAINT `fk_pi_purchase` FOREIGN KEY (`purchase_id`)REFERENCES `purchases`       (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchase_returns` (
  `id`          int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_id` int(10) unsigned NOT NULL,
  `supplier_id` int(10) unsigned NOT NULL,
  `invoice_no`  varchar(50)      NOT NULL,
  `return_date` date             NOT NULL,
  `total`       decimal(12,2)    NOT NULL DEFAULT 0.00,
  `note`        text             DEFAULT NULL,
  `created_by`  int(10) unsigned DEFAULT NULL,
  `created_at`  timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_no` (`invoice_no`),
  KEY `fk_pr_purchase2` (`purchase_id`),
  KEY `fk_pr_supplier2` (`supplier_id`),
  KEY `fk_pr_user2`     (`created_by`),
  CONSTRAINT `fk_pr_purchase2` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`),
  CONSTRAINT `fk_pr_supplier2` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_pr_user2`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `purchase_return_items` (
  `id`                 int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_return_id` int(10) unsigned NOT NULL,
  `product_id`         int(10) unsigned NOT NULL,
  `quantity`           decimal(12,2)    NOT NULL,
  `cost_price`         decimal(12,2)    NOT NULL,
  `total`              decimal(12,2)    NOT NULL,
  `reason`             varchar(255)     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pri_return`  (`purchase_return_id`),
  KEY `fk_pri_product` (`product_id`),
  CONSTRAINT `fk_pri_product` FOREIGN KEY (`product_id`)          REFERENCES `products`         (`id`),
  CONSTRAINT `fk_pri_return`  FOREIGN KEY (`purchase_return_id`)  REFERENCES `purchase_returns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `supplier_payments` (
  `id`             int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id`    int(10) unsigned NOT NULL,
  `purchase_id`    int(10) unsigned DEFAULT NULL,
  `amount`         decimal(12,2)    NOT NULL,
  `payment_date`   date             NOT NULL,
  `payment_method` enum('cash','card','bank_transfer','mobile','cheque') DEFAULT 'cash',
  `reference`      varchar(100)     DEFAULT NULL,
  `note`           text             DEFAULT NULL,
  `created_by`     int(10) unsigned DEFAULT NULL,
  `created_at`     timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_spay_purchase` (`purchase_id`),
  KEY `fk_spay_user`     (`created_by`),
  KEY `idx_spay_supplier`(`supplier_id`),
  KEY `idx_spay_date`    (`payment_date`),
  CONSTRAINT `fk_spay_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_spay_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_spay_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cheque_tracking` (
  `id`               int(10) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_type` enum('sale','purchase') NOT NULL DEFAULT 'sale',
  `sale_payment_id`  int(10) unsigned DEFAULT NULL,
  `purchase_payment_id` int(10) unsigned DEFAULT NULL,
  `sale_id`          int(10) unsigned DEFAULT NULL,
  `purchase_id`      int(10) unsigned DEFAULT NULL,
  `party_name`       varchar(150)     DEFAULT NULL,
  `party_phone`      varchar(20)      DEFAULT NULL,
  `cheque_no`        varchar(50)      NOT NULL,
  `bank_name`        varchar(100)     DEFAULT NULL,
  `branch`           varchar(100)     DEFAULT NULL,
  `cheque_date`      date             DEFAULT NULL,
  `amount`           decimal(12,2)    NOT NULL DEFAULT 0.00,
  `status`           enum('pending','cleared','bounced') DEFAULT 'pending',
  `cleared_date`     date             DEFAULT NULL,
  `note`             text             DEFAULT NULL,
  `created_by`       int(10) unsigned DEFAULT NULL,
  `updated_by`       int(10) unsigned DEFAULT NULL,
  `created_at`       timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at`       timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `expense_categories` (
  `id`            int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100)     NOT NULL,
  `description`   text             DEFAULT NULL,
  `status`        enum('active','inactive') DEFAULT 'active',
  `created_at`    timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `expenses` (
  `id`                  int(10) unsigned NOT NULL AUTO_INCREMENT,
  `expense_category_id` int(10) unsigned NOT NULL,
  `branch_id`           int(10) unsigned DEFAULT NULL,
  `title`               varchar(200)     NOT NULL,
  `amount`              decimal(12,2)    NOT NULL DEFAULT 0.00,
  `expense_date`        date             NOT NULL,
  `reference`           varchar(100)     DEFAULT NULL,
  `note`                text             DEFAULT NULL,
  `created_by`          int(10) unsigned DEFAULT NULL,
  `created_at`          timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_exp_user`   (`created_by`),
  KEY `idx_exp_date`  (`expense_date`),
  KEY `idx_exp_cat`   (`expense_category_id`),
  KEY `idx_exp_branch`(`branch_id`),
  CONSTRAINT `fk_exp_branch` FOREIGN KEY (`branch_id`)           REFERENCES `branches`           (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_cat`    FOREIGN KEY (`expense_category_id`) REFERENCES `expense_categories` (`id`),
  CONSTRAINT `fk_exp_user`   FOREIGN KEY (`created_by`)          REFERENCES `users`              (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `held_bills` (
  `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hold_ref`   varchar(50)      NOT NULL,
  `customer_id`int(10) unsigned DEFAULT NULL,
  `cart_data`  longtext         CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`cart_data`)),
  `subtotal`   decimal(12,2)    NOT NULL DEFAULT 0.00,
  `note`       varchar(255)     DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `hold_ref` (`hold_ref`),
  KEY `fk_hb_customer` (`customer_id`),
  KEY `fk_hb_user`     (`created_by`),
  CONSTRAINT `fk_hb_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_hb_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_resets` (
  `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id`    int(10) unsigned NOT NULL,
  `token`      varchar(64)      NOT NULL,
  `expires_at` datetime         NOT NULL,
  `used`       tinyint(1)       NOT NULL DEFAULT 0,
  `created_at` timestamp        NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_pwreset_user` (`user_id`),
  CONSTRAINT `fk_pwreset_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  VIEWS
-- ============================================================

CREATE OR REPLACE VIEW `v_low_stock` AS
SELECT p.*, c.category_name, u.short_name AS unit
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
LEFT JOIN units      u ON u.id = p.unit_id
WHERE p.status = 'active' AND p.current_stock > 0
  AND p.reorder_level > 0 AND p.current_stock <= p.reorder_level;

CREATE OR REPLACE VIEW `v_expiry_alert` AS
SELECT pb.id AS batch_id, pb.batch_no, pb.expiry_date, pb.quantity,
       p.product_name, p.product_code,
       DATEDIFF(pb.expiry_date, CURDATE()) AS days_to_expiry
FROM product_batches pb JOIN products p ON p.id = pb.product_id
WHERE pb.expiry_date IS NOT NULL AND pb.expiry_date >= CURDATE()
  AND pb.expiry_date <= CURDATE() + INTERVAL 30 DAY AND pb.quantity > 0;

CREATE OR REPLACE VIEW `v_stock_value` AS
SELECT p.id, p.product_code, p.product_name, p.current_stock,
       p.cost_price, p.selling_price,
       p.current_stock * p.cost_price    AS cost_value,
       p.current_stock * p.selling_price AS retail_value
FROM products p WHERE p.status = 'active'
ORDER BY cost_value DESC;

-- ============================================================
--  SEED DATA — minimal (roles, permissions, 1 user, settings)
-- ============================================================

-- Roles
INSERT INTO `roles` (`id`,`role_name`,`description`) VALUES
(1,'super_admin', 'Full system access across all branches'),
(2,'admin',       'Shop owner/admin – manage shop, users, products'),
(3,'manager',     'Manage stock, purchases, sales, and reports'),
(4,'cashier',     'Create bills, manage daily sales and returns'),
(5,'stock_keeper','Manage products, stock, and purchases'),
(6,'accountant',  'View sales, expenses, payments, and profit reports');

-- Permissions
INSERT INTO `permissions` (`id`,`module`,`action`,`permission_key`) VALUES
(1,'dashboard','view','dashboard.view'),
(2,'users','view','users.view'),(3,'users','create','users.create'),
(4,'users','edit','users.edit'),(5,'users','delete','users.delete'),
(6,'products','view','products.view'),(7,'products','create','products.create'),
(8,'products','edit','products.edit'),(9,'products','delete','products.delete'),
(10,'purchases','view','purchases.view'),(11,'purchases','create','purchases.create'),
(12,'purchases','edit','purchases.edit'),
(13,'sales','view','sales.view'),(14,'sales','create','sales.create'),
(15,'sales','return','sales.return'),
(16,'customers','view','customers.view'),(17,'customers','create','customers.create'),
(18,'customers','edit','customers.edit'),
(19,'suppliers','view','suppliers.view'),(20,'suppliers','create','suppliers.create'),
(21,'suppliers','edit','suppliers.edit'),
(22,'stock','view','stock.view'),(23,'stock','adjust','stock.adjust'),
(24,'expenses','view','expenses.view'),(25,'expenses','create','expenses.create'),
(26,'reports','view','reports.view'),
(27,'settings','view','settings.view'),(28,'settings','edit','settings.edit'),
(29,'backup','manage','backup.manage');

-- Super admin gets all permissions
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 1, id FROM permissions;

-- Admin gets all permissions
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 2, id FROM permissions;

-- Manager: all except users/settings/backup
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 3, id FROM permissions WHERE permission_key NOT IN
('users.view','users.create','users.edit','users.delete','settings.view','settings.edit','backup.manage');

-- Cashier: sales + view products/customers
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 4, id FROM permissions WHERE permission_key IN
('dashboard.view','sales.view','sales.create','sales.return','customers.view','customers.create','products.view');

-- Stock keeper
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 5, id FROM permissions WHERE permission_key IN
('dashboard.view','products.view','products.create','products.edit','products.delete',
 'purchases.view','purchases.create','purchases.edit','suppliers.view','suppliers.create','suppliers.edit',
 'stock.view','stock.adjust');

-- Accountant
INSERT INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 6, id FROM permissions WHERE permission_key IN
('dashboard.view','sales.view','expenses.view','expenses.create','reports.view','customers.view');

-- Branch
INSERT INTO `branches` (`id`,`name`,`address`,`phone`,`is_main`,`status`) VALUES
(1, 'Main Branch', '', '', 1, 'active');

-- Super Admin user  (password: Admin@1234)
INSERT INTO `users` (`id`,`role_id`,`branch_id`,`name`,`username`,`email`,`password_hash`,`status`) VALUES
(1, 1, 1, 'Super Admin', 'superadmin', 'superadmin@mjpos.com',
 '$2y$12$kMVS06.h92M0baZzECnJjefG5GlyWsrqz7FLDgKdVTp6YLGG.o9PW', 'active');

-- Walk-in Customer (required by POS)
INSERT INTO `customers` (`id`,`customer_name`,`status`) VALUES
(1, 'Walk-in Customer', 'active');

-- Default settings
INSERT INTO `settings` (`setting_key`,`setting_value`) VALUES
('shop_name',         'MJPOS System'),
('shop_address',      ''),
('shop_phone',        ''),
('shop_email',        ''),
('currency_symbol',   'Rs.'),
('currency_code',     'LKR'),
('tax_percent',       '0'),
('tax_name',          'VAT'),
('invoice_prefix',    'INV'),
('purchase_prefix',   'PUR'),
('return_prefix',     'RET'),
('low_stock_alert',   '10'),
('expiry_alert_days', '30'),
('receipt_footer',    'Thank you for shopping with us!'),
('receipt_show_logo', '1'),
('allow_negative_stock',''),
('receipt_show_tax',  ''),
('backup_path',       'storage/backups/'),
('timezone',          'Asia/Colombo');

-- Default units
INSERT INTO `units` (`unit_name`,`short_name`) VALUES
('Piece','pcs'),('Kilogram','kg'),('Gram','g'),
('Litre','l'),('Millilitre','ml'),('Box','box'),
('Packet','pkt'),('Bottle','btl'),('Dozen','doz');

-- Default expense categories
INSERT INTO `expense_categories` (`category_name`) VALUES
('Rent'),('Salaries'),('Electricity'),('Water'),
('Internet'),('Transport'),('Repairs & Maintenance'),
('Advertising'),('Miscellaneous');

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
SELECT '✅ MJPOS Fresh Setup Complete!' AS '';
SELECT CONCAT('Login → username: superadmin  |  password: Admin@1234') AS '';
SELECT CONCAT('URL → http://localhost:8080/mjpos/public') AS '';
-- ============================================================
