-- ============================================================
--  MJPOS – Point of Sale & Inventory Management System
--  Full Database Schema + Seed Data
--  Engine: MySQL 8.0+ / MariaDB 10.6+
--  Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `mjpos_db`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `mjpos_db`;

-- ============================================================
-- 1. ROLES
-- ============================================================
CREATE TABLE `roles` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_name`  VARCHAR(50)  NOT NULL UNIQUE,
  `description` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. PERMISSIONS
-- ============================================================
CREATE TABLE `permissions` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `module`          VARCHAR(50)  NOT NULL,
  `action`          VARCHAR(50)  NOT NULL,
  `permission_key`  VARCHAR(100) NOT NULL UNIQUE,
  UNIQUE KEY `uq_module_action` (`module`, `action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. ROLE PERMISSIONS (pivot)
-- ============================================================
CREATE TABLE `role_permissions` (
  `role_id`       INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`)
    REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`)
    REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. BRANCHES
-- ============================================================
CREATE TABLE `branches` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `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    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. USERS
-- ============================================================
CREATE TABLE `users` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_id`         INT UNSIGNED NOT NULL,
  `branch_id`       INT UNSIGNED DEFAULT NULL,
  `name`            VARCHAR(100) NOT NULL,
  `username`        VARCHAR(50)  NOT NULL UNIQUE,
  `email`           VARCHAR(100) DEFAULT NULL UNIQUE,
  `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    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  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;

-- ============================================================
-- 6. SETTINGS
-- ============================================================
CREATE TABLE `settings` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key`   VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT         DEFAULT NULL,
  `updated_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. AUDIT LOGS
-- ============================================================
CREATE TABLE `audit_logs` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT UNSIGNED DEFAULT NULL,
  `action`      VARCHAR(100) NOT NULL,
  `module`      VARCHAR(50)  NOT NULL,
  `description` TEXT         DEFAULT NULL,
  `old_value`   JSON         DEFAULT NULL,
  `new_value`   JSON         DEFAULT NULL,
  `ip_address`  VARCHAR(45)  DEFAULT NULL,
  `user_agent`  VARCHAR(255) DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE SET NULL,
  INDEX `idx_audit_user`   (`user_id`),
  INDEX `idx_audit_module` (`module`),
  INDEX `idx_audit_date`   (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. CATEGORIES
-- ============================================================
CREATE TABLE `categories` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_name` VARCHAR(100) NOT NULL,
  `description`   TEXT         DEFAULT NULL,
  `status`        ENUM('active','inactive') DEFAULT 'active',
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. BRANDS
-- ============================================================
CREATE TABLE `brands` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `brand_name`  VARCHAR(100) NOT NULL,
  `description` TEXT         DEFAULT NULL,
  `status`      ENUM('active','inactive') DEFAULT 'active',
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. UNITS
-- ============================================================
CREATE TABLE `units` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unit_name`  VARCHAR(50)  NOT NULL,
  `short_name` VARCHAR(20)  NOT NULL,
  `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. PRODUCTS
-- ============================================================
CREATE TABLE `products` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_id`     INT UNSIGNED DEFAULT NULL,
  `brand_id`        INT UNSIGNED DEFAULT NULL,
  `unit_id`         INT UNSIGNED DEFAULT NULL,
  `product_code`    VARCHAR(50)  NOT NULL UNIQUE,
  `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           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  COMMENT '1 = track batches (pharmacy)',
  `is_serial`       TINYINT(1)    NOT NULL DEFAULT 0  COMMENT '1 = track serial numbers',
  `status`          ENUM('active','inactive') DEFAULT 'active',
  `created_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_prod_cat`   FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prod_brand` FOREIGN KEY (`brand_id`)    REFERENCES `brands`     (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_prod_unit`  FOREIGN KEY (`unit_id`)     REFERENCES `units`      (`id`) ON DELETE SET NULL,
  INDEX `idx_prod_code`    (`product_code`),
  INDEX `idx_prod_barcode` (`barcode`),
  INDEX `idx_prod_name`    (`product_name`),
  INDEX `idx_prod_status`  (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. PRODUCT BATCHES  (pharmacy / expiry tracking)
-- ============================================================
CREATE TABLE `product_batches` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id`    INT 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    DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_batch_prod` FOREIGN KEY (`product_id`)
    REFERENCES `products` (`id`) ON DELETE CASCADE,
  INDEX `idx_batch_expiry` (`expiry_date`),
  INDEX `idx_batch_prod`   (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. SUPPLIERS
-- ============================================================
CREATE TABLE `suppliers` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `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  COMMENT 'Outstanding payable',
  `status`          ENUM('active','inactive') DEFAULT 'active',
  `created_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. CUSTOMERS
-- ============================================================
CREATE TABLE `customers` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `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  COMMENT 'Positive = customer owes us',
  `loyalty_points`  INT UNSIGNED NOT NULL DEFAULT 0,
  `status`          ENUM('active','inactive') DEFAULT 'active',
  `created_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. PURCHASES
-- ============================================================
CREATE TABLE `purchases` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `supplier_id`   INT UNSIGNED NOT NULL,
  `branch_id`     INT UNSIGNED DEFAULT NULL,
  `invoice_no`    VARCHAR(50)  NOT NULL UNIQUE,
  `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 UNSIGNED  DEFAULT NULL,
  `created_at`    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_pur_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_pur_branch`   FOREIGN KEY (`branch_id`)   REFERENCES `branches`  (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pur_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL,
  INDEX `idx_pur_date`     (`purchase_date`),
  INDEX `idx_pur_supplier` (`supplier_id`),
  INDEX `idx_pur_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 16. PURCHASE ITEMS
-- ============================================================
CREATE TABLE `purchase_items` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_id` INT UNSIGNED NOT NULL,
  `product_id`  INT UNSIGNED NOT NULL,
  `batch_id`    INT 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,
  CONSTRAINT `fk_pi_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases`      (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pi_product`  FOREIGN KEY (`product_id`)  REFERENCES `products`       (`id`),
  CONSTRAINT `fk_pi_batch`    FOREIGN KEY (`batch_id`)    REFERENCES `product_batches` (`id`) ON DELETE SET NULL,
  INDEX `idx_pi_purchase` (`purchase_id`),
  INDEX `idx_pi_product`  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 17. PURCHASE RETURNS
-- ============================================================
CREATE TABLE `purchase_returns` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_id`  INT UNSIGNED NOT NULL,
  `supplier_id`  INT UNSIGNED NOT NULL,
  `invoice_no`   VARCHAR(50)  NOT NULL UNIQUE,
  `return_date`  DATE         NOT NULL,
  `total`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `note`         TEXT          DEFAULT NULL,
  `created_by`   INT UNSIGNED  DEFAULT NULL,
  `created_at`   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_pr_purchase`  FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`),
  CONSTRAINT `fk_pr_supplier`  FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_pr_user`      FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 18. PURCHASE RETURN ITEMS
-- ============================================================
CREATE TABLE `purchase_return_items` (
  `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_return_id` INT UNSIGNED NOT NULL,
  `product_id`        INT 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,
  CONSTRAINT `fk_pri_return`  FOREIGN KEY (`purchase_return_id`) REFERENCES `purchase_returns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pri_product` FOREIGN KEY (`product_id`)         REFERENCES `products`         (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 19. SALES
-- ============================================================
CREATE TABLE `sales` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `customer_id`    INT UNSIGNED DEFAULT NULL,
  `branch_id`      INT UNSIGNED DEFAULT NULL,
  `invoice_no`     VARCHAR(50)  NOT NULL UNIQUE,
  `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 UNSIGNED  DEFAULT NULL,
  `created_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_sale_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sale_branch`   FOREIGN KEY (`branch_id`)   REFERENCES `branches`  (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sale_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL,
  INDEX `idx_sale_date`     (`sale_date`),
  INDEX `idx_sale_customer` (`customer_id`),
  INDEX `idx_sale_cashier`  (`created_by`),
  INDEX `idx_sale_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 20. SALE ITEMS
-- ============================================================
CREATE TABLE `sale_items` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sale_id`       INT UNSIGNED NOT NULL,
  `product_id`    INT UNSIGNED NOT NULL,
  `batch_id`      INT UNSIGNED DEFAULT NULL,
  `quantity`      DECIMAL(12,2) NOT NULL,
  `cost_price`    DECIMAL(12,2) NOT NULL  COMMENT 'Snapshot at time of sale for profit calc',
  `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,
  CONSTRAINT `fk_si_sale`    FOREIGN KEY (`sale_id`)    REFERENCES `sales`          (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_si_product` FOREIGN KEY (`product_id`) REFERENCES `products`       (`id`),
  CONSTRAINT `fk_si_batch`   FOREIGN KEY (`batch_id`)   REFERENCES `product_batches` (`id`) ON DELETE SET NULL,
  INDEX `idx_si_sale`    (`sale_id`),
  INDEX `idx_si_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 21. SALE PAYMENTS
-- ============================================================
CREATE TABLE `sale_payments` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sale_id`        INT 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 COMMENT 'Card ref / transaction id',
  `note`           VARCHAR(255)  DEFAULT NULL,
  `created_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_sp_sale` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE,
  INDEX `idx_sp_sale`   (`sale_id`),
  INDEX `idx_sp_method` (`payment_method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 22. SALES RETURNS
-- ============================================================
CREATE TABLE `sales_returns` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sale_id`     INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED DEFAULT NULL,
  `invoice_no`  VARCHAR(50)  NOT NULL UNIQUE,
  `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 UNSIGNED  DEFAULT NULL,
  `created_at`  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_sr_sale`     FOREIGN KEY (`sale_id`)     REFERENCES `sales`     (`id`),
  CONSTRAINT `fk_sr_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sr_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 23. SALES RETURN ITEMS
-- ============================================================
CREATE TABLE `sales_return_items` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sales_return_id` INT UNSIGNED NOT NULL,
  `product_id`      INT 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,
  CONSTRAINT `fk_sri_return`  FOREIGN KEY (`sales_return_id`) REFERENCES `sales_returns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sri_product` FOREIGN KEY (`product_id`)      REFERENCES `products`      (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 24. STOCK MOVEMENTS  (single source of truth for stock)
-- ============================================================
CREATE TABLE `stock_movements` (
  `id`             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `product_id`     INT UNSIGNED NOT NULL,
  `batch_id`       INT 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 COMMENT 'Running balance after this movement',
  `reference_no`   VARCHAR(50)   DEFAULT NULL,
  `note`           TEXT          DEFAULT NULL,
  `created_by`     INT UNSIGNED  DEFAULT NULL,
  `created_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_sm_product` FOREIGN KEY (`product_id`) REFERENCES `products`       (`id`),
  CONSTRAINT `fk_sm_batch`   FOREIGN KEY (`batch_id`)   REFERENCES `product_batches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_sm_user`    FOREIGN KEY (`created_by`) REFERENCES `users`           (`id`) ON DELETE SET NULL,
  INDEX `idx_sm_product` (`product_id`),
  INDEX `idx_sm_type`    (`movement_type`),
  INDEX `idx_sm_date`    (`created_at`),
  INDEX `idx_sm_ref`     (`reference_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 25. EXPENSE CATEGORIES
-- ============================================================
CREATE TABLE `expense_categories` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_name` VARCHAR(100) NOT NULL,
  `description`   TEXT         DEFAULT NULL,
  `status`        ENUM('active','inactive') DEFAULT 'active',
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 26. EXPENSES
-- ============================================================
CREATE TABLE `expenses` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `expense_category_id` INT UNSIGNED NOT NULL,
  `branch_id`           INT 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 UNSIGNED  DEFAULT NULL,
  `created_at`          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_exp_cat`    FOREIGN KEY (`expense_category_id`) REFERENCES `expense_categories` (`id`),
  CONSTRAINT `fk_exp_branch` FOREIGN KEY (`branch_id`)           REFERENCES `branches`          (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_user`   FOREIGN KEY (`created_by`)          REFERENCES `users`             (`id`) ON DELETE SET NULL,
  INDEX `idx_exp_date`   (`expense_date`),
  INDEX `idx_exp_cat`    (`expense_category_id`),
  INDEX `idx_exp_branch` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 27. HELD BILLS  (POS hold/recall)
-- ============================================================
CREATE TABLE `held_bills` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `hold_ref`    VARCHAR(50)  NOT NULL UNIQUE,
  `customer_id` INT UNSIGNED DEFAULT NULL,
  `cart_data`   JSON         NOT NULL  COMMENT 'Serialised cart items',
  `subtotal`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `note`        VARCHAR(255)  DEFAULT NULL,
  `created_by`  INT UNSIGNED  DEFAULT NULL,
  `created_at`  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  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;

-- ============================================================
-- 28. SUPPLIER PAYMENTS
-- ============================================================
CREATE TABLE `supplier_payments` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `supplier_id`    INT UNSIGNED NOT NULL,
  `purchase_id`    INT 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 UNSIGNED  DEFAULT NULL,
  `created_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_spay_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_spay_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_spay_user`     FOREIGN KEY (`created_by`)  REFERENCES `users`     (`id`) ON DELETE SET NULL,
  INDEX `idx_spay_supplier` (`supplier_id`),
  INDEX `idx_spay_date`     (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 29. CUSTOMER PAYMENTS
-- ============================================================
CREATE TABLE `customer_payments` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `customer_id`    INT UNSIGNED NOT NULL,
  `sale_id`        INT UNSIGNED DEFAULT NULL,
  `amount`         DECIMAL(12,2) NOT NULL,
  `payment_date`   DATE          NOT NULL,
  `payment_method` ENUM('cash','card','bank_transfer','mobile') DEFAULT 'cash',
  `reference`      VARCHAR(100)  DEFAULT NULL,
  `note`           TEXT          DEFAULT NULL,
  `created_by`     INT UNSIGNED  DEFAULT NULL,
  `created_at`     TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  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,
  INDEX `idx_cpay_customer` (`customer_id`),
  INDEX `idx_cpay_date`     (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- ==================== SEED DATA ============================
-- ============================================================

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

-- PERMISSIONS
INSERT INTO `permissions` (`module`, `action`, `permission_key`) VALUES
  ('dashboard',  'view',   'dashboard.view'),
  ('users',      'view',   'users.view'),
  ('users',      'create', 'users.create'),
  ('users',      'edit',   'users.edit'),
  ('users',      'delete', 'users.delete'),
  ('products',   'view',   'products.view'),
  ('products',   'create', 'products.create'),
  ('products',   'edit',   'products.edit'),
  ('products',   'delete', 'products.delete'),
  ('purchases',  'view',   'purchases.view'),
  ('purchases',  'create', 'purchases.create'),
  ('purchases',  'edit',   'purchases.edit'),
  ('sales',      'view',   'sales.view'),
  ('sales',      'create', 'sales.create'),
  ('sales',      'return', 'sales.return'),
  ('customers',  'view',   'customers.view'),
  ('customers',  'create', 'customers.create'),
  ('customers',  'edit',   'customers.edit'),
  ('suppliers',  'view',   'suppliers.view'),
  ('suppliers',  'create', 'suppliers.create'),
  ('suppliers',  'edit',   'suppliers.edit'),
  ('stock',      'view',   'stock.view'),
  ('stock',      'adjust', 'stock.adjust'),
  ('expenses',   'view',   'expenses.view'),
  ('expenses',   'create', 'expenses.create'),
  ('reports',    'view',   'reports.view'),
  ('settings',   'view',   'settings.view'),
  ('settings',   'edit',   'settings.edit'),
  ('backup',     'manage', 'backup.manage');

-- BRANCHES
INSERT INTO `branches` (`name`, `address`, `phone`, `is_main`, `status`) VALUES
  ('Main Branch', '123 High Street, Colombo', '+94 11 234 5678', 1, 'active');

-- ADMIN USER  (password: Admin@1234)
-- password_hash('Admin@1234', PASSWORD_BCRYPT)
INSERT INTO `users` (`role_id`, `branch_id`, `name`, `username`, `email`, `password_hash`, `status`) VALUES
  (1, 1, 'Super Admin', 'superadmin', 'superadmin@mjpos.com',
   '$2y$12$kMVS06.h92M0baZzECnJjefG5GlyWsrqz7FLDgKdVTp6YLGG.o9PW', 'active'),
  (2, 1, 'Shop Admin',  'admin',      'admin@mjpos.com',
   '$2y$12$kMVS06.h92M0baZzECnJjefG5GlyWsrqz7FLDgKdVTp6YLGG.o9PW', 'active'),
  (4, 1, 'Cashier One', 'cashier1',   'cashier1@mjpos.com',
   '$2y$12$kMVS06.h92M0baZzECnJjefG5GlyWsrqz7FLDgKdVTp6YLGG.o9PW', 'active');

-- SETTINGS
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
  ('shop_name',       'MJPOS Shop'),
  ('shop_address',    '123 High Street, Colombo'),
  ('shop_phone',      '+94 11 234 5678'),
  ('shop_email',      'info@mjpos.com'),
  ('shop_logo',       ''),
  ('currency_symbol', 'Rs.'),
  ('currency_code',   'LKR'),
  ('tax_percent',     '0'),
  ('tax_name',        'VAT'),
  ('receipt_footer',  'Thank you for shopping with us!'),
  ('invoice_prefix',  'INV'),
  ('purchase_prefix', 'PUR'),
  ('return_prefix',   'RET'),
  ('low_stock_alert', '10'),
  ('expiry_alert_days','30'),
  ('allow_negative_stock', '0'),
  ('receipt_show_tax', '1'),
  ('receipt_show_logo','1'),
  ('backup_path',     'storage/backups/'),
  ('timezone',        'Asia/Colombo');

-- CATEGORIES
INSERT INTO `categories` (`category_name`, `status`) VALUES
  ('Groceries',         'active'),
  ('Beverages',         'active'),
  ('Dairy Products',    'active'),
  ('Personal Care',     'active'),
  ('Household',         'active'),
  ('Electronics',       'active'),
  ('Medicine',          'active'),
  ('Spare Parts',       'active'),
  ('Stationery',        'active'),
  ('Confectionery',     'active');

-- BRANDS
INSERT INTO `brands` (`brand_name`, `status`) VALUES
  ('Anchor',      'active'),
  ('Maliban',     'active'),
  ('Nestlé',      'active'),
  ('Unilever',    'active'),
  ('Toyota',      'active'),
  ('Bosch',       'active'),
  ('Generic',     'active'),
  ('Sunshine',    'active');

-- 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'),
  ('Pair',       'pr');

-- SAMPLE PRODUCTS
INSERT INTO `products`
  (`category_id`,`brand_id`,`unit_id`,`product_code`,`barcode`,`product_name`,
   `cost_price`,`selling_price`,`wholesale_price`,`reorder_level`,`current_stock`,`status`)
VALUES
  (1,7,1,'P001','8901234560001','White Rice 5kg',  750.00, 890.00, 870.00, 10, 50, 'active'),
  (2,3,8,'P002','8901234560002','Nestlé Milo 400g',620.00, 750.00, 730.00, 5,  30, 'active'),
  (3,1,8,'P003','8901234560003','Anchor Milk Powder 400g',580.00,695.00,680.00,5,25,'active'),
  (10,2,7,'P004','8901234560004','Maliban Cream Crackers',95.00,120.00,115.00,20,100,'active'),
  (4,4,8,'P005','8901234560005','Sunlight Dish Soap 400ml',135.00,165.00,160.00,15,60,'active'),
  (7,7,1,'P006','8901234560006','Paracetamol 500mg (100 tabs)',45.00,75.00,70.00,10,200,'active'),
  (6,6,1,'P007','8901234560007','Bosch Drill Bit Set',1800.00,2250.00,2150.00,3,15,'active'),
  (2,8,8,'P008','8901234560008','Sunshine Orange Juice 1L',165.00,210.00,200.00,10,40,'active');

-- EXPENSE CATEGORIES
INSERT INTO `expense_categories` (`category_name`, `status`) VALUES
  ('Rent',            'active'),
  ('Salaries',        'active'),
  ('Electricity',     'active'),
  ('Water',           'active'),
  ('Internet',        'active'),
  ('Transport',       'active'),
  ('Repairs & Maintenance', 'active'),
  ('Office Supplies', 'active'),
  ('Advertising',     'active'),
  ('Miscellaneous',   'active');

-- SAMPLE SUPPLIERS
INSERT INTO `suppliers` (`supplier_name`,`contact_person`,`phone`,`email`,`address`,`opening_balance`,`current_balance`,`status`) VALUES
  ('ABC Distributors',    'Ahmed Silva',   '+94 77 111 2222', 'abc@dist.com',   'Colombo 03', 0.00, 0.00, 'active'),
  ('Sunshine Wholesale',  'Mary Perera',   '+94 77 333 4444', 'sun@whole.com',  'Kandy',      0.00, 0.00, 'active'),
  ('MedPlus Pharma',      'Dr. Nimal',     '+94 77 555 6666', 'med@plus.com',   'Gampaha',    0.00, 0.00, 'active'),
  ('AutoParts Lanka',     'Ranjith Kumar', '+94 77 777 8888', 'auto@parts.com', 'Negombo',    0.00, 0.00, 'active');

-- SAMPLE CUSTOMERS
INSERT INTO `customers` (`customer_name`,`phone`,`email`,`address`,`credit_limit`,`opening_balance`,`current_balance`,`status`) VALUES
  ('Walk-in Customer', '',               '',                  '',             0.00,    0.00, 0.00, 'active'),
  ('Kamal Jayawardena','+94 71 234 5678','kamal@email.com',   'Nugegoda',  50000.00, 0.00, 0.00, 'active'),
  ('Nirosha Fernando',  '+94 77 987 6543','nirosha@email.com','Moratuwa',  25000.00, 0.00, 0.00, 'active'),
  ('Ravi Enterprises',  '+94 11 456 7890','ravi@ent.com',     'Pettah',   100000.00, 0.00, 0.00, 'active');

-- ============================================================
-- 30. CHEQUE TRACKING  (Sales & Purchases)
-- ============================================================
CREATE TABLE `cheque_tracking` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_type`    ENUM('sale','purchase') NOT NULL DEFAULT 'sale',
  `sale_payment_id`     INT UNSIGNED DEFAULT NULL,
  `purchase_payment_id` INT UNSIGNED DEFAULT NULL,
  `sale_id`             INT UNSIGNED DEFAULT NULL,
  `purchase_id`         INT UNSIGNED DEFAULT NULL,
  `party_name`          VARCHAR(150) DEFAULT NULL  COMMENT 'Customer or Supplier name',
  `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 UNSIGNED DEFAULT NULL,
  `updated_by`          INT UNSIGNED DEFAULT NULL,
  `created_at`          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_ct_sale_payment`     FOREIGN KEY (`sale_payment_id`)     REFERENCES `sale_payments`     (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ct_purchase_payment` FOREIGN KEY (`purchase_payment_id`) REFERENCES `supplier_payments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ct_sale`             FOREIGN KEY (`sale_id`)             REFERENCES `sales`             (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ct_purchase`         FOREIGN KEY (`purchase_id`)         REFERENCES `purchases`         (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ct_user`             FOREIGN KEY (`created_by`)          REFERENCES `users`             (`id`) ON DELETE SET NULL,
  INDEX `idx_ct_status`      (`status`),
  INDEX `idx_ct_type`        (`transaction_type`),
  INDEX `idx_ct_cheque_date` (`cheque_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================

-- v_low_stock: products at or below reorder level
CREATE OR REPLACE VIEW `v_low_stock` AS
SELECT
  p.id, p.product_code, p.product_name,
  p.current_stock, p.reorder_level,
  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 <= p.reorder_level;

-- v_expiry_alert: batches expiring within next 30 days
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 <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
  AND pb.quantity > 0
ORDER BY pb.expiry_date ASC;

-- v_daily_sales: today's sales summary per cashier
CREATE OR REPLACE VIEW `v_daily_sales` AS
SELECT
  DATE(s.sale_date)          AS sale_day,
  u.name                     AS cashier_name,
  COUNT(s.id)                AS invoice_count,
  SUM(s.subtotal)            AS subtotal,
  SUM(s.discount)            AS total_discount,
  SUM(s.tax)                 AS total_tax,
  SUM(s.total)               AS gross_total,
  SUM(s.paid)                AS total_paid,
  SUM(s.balance)             AS total_balance,
  SUM(si.quantity * (si.selling_price - si.cost_price) - si.discount) AS gross_profit
FROM sales s
JOIN users u ON u.id = s.created_by
JOIN sale_items si ON si.sale_id = s.id
WHERE s.status = 'completed'
GROUP BY DATE(s.sale_date), s.created_by
ORDER BY sale_day DESC;

-- v_product_stock_value: current inventory value
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;

-- ============================================================
-- ROLE PERMISSIONS
-- Permission IDs match INSERT order above (1=dashboard.view … 29=backup.manage)
-- ============================================================

-- super_admin (1) — all permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9),(1,10),
(1,11),(1,12),(1,13),(1,14),(1,15),(1,16),(1,17),(1,18),(1,19),(1,20),
(1,21),(1,22),(1,23),(1,24),(1,25),(1,26),(1,27),(1,28),(1,29);

-- admin (2) — all permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),(2,7),(2,8),(2,9),(2,10),
(2,11),(2,12),(2,13),(2,14),(2,15),(2,16),(2,17),(2,18),(2,19),(2,20),
(2,21),(2,22),(2,23),(2,24),(2,25),(2,26),(2,27),(2,28),(2,29);

-- manager (3) — everything except user management, settings, backup
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
(3,1),(3,6),(3,7),(3,8),(3,9),
(3,10),(3,11),(3,12),(3,13),(3,14),(3,15),
(3,16),(3,17),(3,18),(3,19),(3,20),(3,21),
(3,22),(3,23),(3,24),(3,25),(3,26);

-- cashier (4) — dashboard + POS/new sale + stock view only
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
(4,1),(4,14),(4,22);

-- stock_keeper (5) — products, stock, purchases, suppliers
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
(5,1),(5,6),(5,7),(5,8),(5,9),
(5,10),(5,11),(5,12),(5,19),(5,20),(5,21),(5,22),(5,23);

-- accountant (6) — dashboard, sales view, expenses, reports
INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES
(6,1),(6,13),(6,24),(6,25),(6,26);

-- ============================================================
-- END OF SCHEMA
-- ============================================================
