-- ==============================================================================
-- DigiMobiz - Mobile Phone Sales, Accessories, Spare Parts & Repair Management
-- Production Database Schema
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- ==============================================================================

CREATE DATABASE IF NOT EXISTS digimobiz
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE digimobiz;

-- Disable foreign key checks temporarily for safe re-runs
SET FOREIGN_KEY_CHECKS = 0;

-- ==============================================================================
-- 1. ROLES
-- ==============================================================================
DROP TABLE IF EXISTS roles;
CREATE TABLE roles (
    id          INT             AUTO_INCREMENT PRIMARY KEY,
    role_name   VARCHAR(100)    NOT NULL,
    description TEXT,
    status      TINYINT(1)      NOT NULL DEFAULT 1 COMMENT '1=Active, 0=Inactive',
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 2. PERMISSIONS
-- ==============================================================================
DROP TABLE IF EXISTS permissions;
CREATE TABLE permissions (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    permission_key  VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Dot-notation key, e.g. dashboard.view',
    permission_name VARCHAR(200)    NOT NULL COMMENT 'Human-readable name',
    module_name     VARCHAR(100)    NOT NULL COMMENT 'Module grouping, e.g. Dashboard, Users',
    description     TEXT,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 3. ROLE PERMISSIONS (junction table)
-- ==============================================================================
DROP TABLE IF EXISTS role_permissions;
CREATE TABLE role_permissions (
    id              INT         AUTO_INCREMENT PRIMARY KEY,
    role_id         INT         NOT NULL,
    permission_id   INT         NOT NULL,
    created_at      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uk_role_permission (role_id, permission_id),

    CONSTRAINT fk_rp_role       FOREIGN KEY (role_id)       REFERENCES roles(id)        ON DELETE CASCADE,
    CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 4. USERS
-- ==============================================================================
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    role_id         INT             NOT NULL,
    name            VARCHAR(200)    NOT NULL,
    email           VARCHAR(100)    NOT NULL UNIQUE,
    phone           VARCHAR(20),
    password        VARCHAR(255)    NOT NULL COMMENT 'bcrypt hash',
    profile_image   VARCHAR(255),
    status          TINYINT(1)      NOT NULL DEFAULT 1 COMMENT '1=Active, 0=Inactive',
    last_login_at           DATETIME,
    reset_token             VARCHAR(255)    COMMENT 'Password reset token (bin2hex)',
    reset_token_expires     DATETIME        COMMENT 'Token expiration timestamp',
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_users_email (email),

    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 5. CUSTOMERS
-- ==============================================================================
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    customer_code   VARCHAR(50)     UNIQUE COMMENT 'Auto-generated, e.g. CUST-0001',
    name            VARCHAR(200)    NOT NULL,
    phone           VARCHAR(20)     NOT NULL,
    whatsapp        VARCHAR(20),
    email           VARCHAR(100),
    nic             VARCHAR(50)     COMMENT 'National ID / passport number',
    address         TEXT,
    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',
    status          TINYINT(1)      NOT NULL DEFAULT 1,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_customers_phone (phone),
    INDEX idx_customers_name  (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 6. SUPPLIERS
-- ==============================================================================
DROP TABLE IF EXISTS suppliers;
CREATE TABLE suppliers (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    supplier_code   VARCHAR(50)     UNIQUE COMMENT 'Auto-generated, e.g. SUP-0001',
    supplier_name   VARCHAR(200)    NOT NULL,
    contact_person  VARCHAR(200),
    phone           VARCHAR(20)     NOT NULL,
    email           VARCHAR(100),
    address         TEXT,
    company_name    VARCHAR(200),
    opening_balance DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    current_balance DECIMAL(12,2)   NOT NULL DEFAULT 0.00 COMMENT 'Positive = we owe supplier',
    status          TINYINT(1)      NOT NULL DEFAULT 1,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_suppliers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 7. CATEGORIES
-- ==============================================================================
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    category_name   VARCHAR(200)    NOT NULL,
    category_type   ENUM('Phone','Accessory','Spare Part') NOT NULL,
    description     TEXT,
    status          TINYINT(1)      NOT NULL DEFAULT 1,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 8. BRANDS
-- ==============================================================================
DROP TABLE IF EXISTS brands;
CREATE TABLE brands (
    id          INT             AUTO_INCREMENT PRIMARY KEY,
    brand_name  VARCHAR(200)    NOT NULL,
    description TEXT,
    status      TINYINT(1)      NOT NULL DEFAULT 1,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 9. PRODUCTS
-- ==============================================================================
DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id                  INT             AUTO_INCREMENT PRIMARY KEY,
    product_code        VARCHAR(50)     UNIQUE COMMENT 'Auto-generated, e.g. PROD-0001',
    product_name        VARCHAR(255)    NOT NULL,
    category_id         INT,
    brand_id            INT,
    supplier_id         INT,
    product_type        ENUM('phone','accessory','spare_part') NOT NULL,
    model               VARCHAR(200),
    sku                 VARCHAR(100)    UNIQUE,
    barcode             VARCHAR(100),
    compatible_models   TEXT            COMMENT 'Comma-separated list of phone models this spare part fits',
    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)   NOT NULL DEFAULT 0.00,
    current_stock       INT             NOT NULL DEFAULT 0,
    low_stock_qty       INT             NOT NULL DEFAULT 5 COMMENT 'Alert when stock falls below this',
    warranty_period_days INT            NOT NULL DEFAULT 0 COMMENT 'Warranty in days, 0 = no warranty',
    product_image       VARCHAR(255),
    description         TEXT,
    status              TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_products_code    (product_code),
    INDEX idx_products_sku     (sku),
    INDEX idx_products_barcode (barcode),
    INDEX idx_products_type    (product_type),

    CONSTRAINT fk_products_category  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_products_brand     FOREIGN KEY (brand_id)    REFERENCES brands(id)    ON DELETE SET NULL,
    CONSTRAINT fk_products_supplier  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 10. PRODUCT IMEI / SERIAL NUMBERS
-- ==============================================================================
DROP TABLE IF EXISTS product_imei;
CREATE TABLE product_imei (
    id                      INT             AUTO_INCREMENT PRIMARY KEY,
    product_id              INT             NOT NULL,
    imei_number             VARCHAR(100)    NOT NULL UNIQUE,
    serial_number           VARCHAR(100),
    purchase_item_id        INT,
    sales_invoice_item_id   INT,
    status                  ENUM('Available','Sold','Returned','Damaged','Warranty Claim') NOT NULL DEFAULT 'Available',
    warranty_start_date     DATE,
    warranty_end_date       DATE,
    created_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_imei_number   (imei_number),
    INDEX idx_imei_status   (status),

    CONSTRAINT fk_imei_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 11. STOCK MOVEMENTS
-- ==============================================================================
DROP TABLE IF EXISTS stock_movements;
CREATE TABLE stock_movements (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    product_id      INT             NOT NULL,
    movement_type   ENUM('purchase','sale','repair_usage','return','adjustment','damaged') NOT NULL,
    reference_type  VARCHAR(100)    COMMENT 'Parent entity type: purchase, sales_invoice, repair_job, etc.',
    reference_id    INT             COMMENT 'Parent entity ID',
    quantity_in     INT             NOT NULL DEFAULT 0,
    quantity_out    INT             NOT NULL DEFAULT 0,
    balance_after   INT             NOT NULL DEFAULT 0 COMMENT 'Stock balance for this product after this movement',
    note            TEXT,
    created_by      INT,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_sm_product      (product_id),
    INDEX idx_sm_reference    (reference_type, reference_id),

    CONSTRAINT fk_sm_product  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 12. PURCHASES
-- ==============================================================================
DROP TABLE IF EXISTS purchases;
CREATE TABLE purchases (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    purchase_no     VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. PO-000001',
    supplier_id     INT             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_amount    DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    paid_amount     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    due_amount      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    payment_method  ENUM('Cash','Card','Bank Transfer','Online Payment') DEFAULT 'Cash',
    purchase_status ENUM('Pending','Received','Cancelled') NOT NULL DEFAULT 'Pending',
    note            TEXT,
    created_by      INT,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_purchases_no (purchase_no),

    CONSTRAINT fk_purchases_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 13. PURCHASE ITEMS
-- ==============================================================================
DROP TABLE IF EXISTS purchase_items;
CREATE TABLE purchase_items (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    purchase_id     INT             NOT NULL,
    product_id      INT             NOT NULL,
    quantity        INT             NOT NULL DEFAULT 1,
    cost_price      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    selling_price   DECIMAL(12,2)   NOT NULL DEFAULT 0.00 COMMENT 'Suggested retail price at time of purchase',
    total_price     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 14. SALES INVOICES
-- ==============================================================================
DROP TABLE IF EXISTS sales_invoices;
CREATE TABLE sales_invoices (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    invoice_no      VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. INV-000001',
    customer_id     INT,
    invoice_date    DATETIME        NOT NULL,
    subtotal        DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    discount_type   ENUM('Percentage','Fixed') DEFAULT 'Percentage',
    discount_value  DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    tax_amount      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    total_amount    DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    paid_amount     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    due_amount      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    payment_status  ENUM('Paid','Partial','Due') NOT NULL DEFAULT 'Paid',
    invoice_status  ENUM('Active','Cancelled','Refunded') NOT NULL DEFAULT 'Active',
    payment_method  ENUM('Cash','Card','Bank Transfer','Online Payment','Partial') DEFAULT 'Cash',
    note            TEXT,
    created_by      INT,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_si_invoice_no (invoice_no),
    INDEX idx_si_date       (invoice_date),

    CONSTRAINT fk_si_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 15. SALES INVOICE ITEMS
-- ==============================================================================
DROP TABLE IF EXISTS sales_invoice_items;
CREATE TABLE sales_invoice_items (
    id                  INT             AUTO_INCREMENT PRIMARY KEY,
    sales_invoice_id    INT             NOT NULL,
    product_id          INT             NOT NULL,
    imei_id             INT             COMMENT 'NULL for accessories/spare parts (required for phones)',
    product_name        VARCHAR(255)    NOT NULL COMMENT 'Snapshot of product name at time of sale',
    quantity            INT             NOT NULL DEFAULT 1,
    cost_price          DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    selling_price       DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    discount_amount     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    tax_amount          DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    total_price         DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    warranty_period_days INT            NOT NULL DEFAULT 0,
    warranty_start_date DATE,
    warranty_end_date   DATE,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_sii_invoice FOREIGN KEY (sales_invoice_id) REFERENCES sales_invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_sii_product FOREIGN KEY (product_id)       REFERENCES products(id),
    CONSTRAINT fk_sii_imei    FOREIGN KEY (imei_id)          REFERENCES product_imei(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 16. PAYMENTS
-- ==============================================================================
DROP TABLE IF EXISTS payments;
CREATE TABLE payments (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    payment_no      VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. PMT-000001',
    payment_type    ENUM('sale','repair','supplier','expense','customer_due') NOT NULL,
    reference_id    INT             COMMENT 'ID of the referenced invoice, repair job, etc.',
    customer_id     INT,
    supplier_id     INT,
    amount          DECIMAL(12,2)   NOT NULL,
    payment_method  ENUM('Cash','Card','Bank Transfer','Online Payment') NOT NULL DEFAULT 'Cash',
    payment_date    DATETIME        NOT NULL,
    note            TEXT,
    received_by     INT             COMMENT 'User ID who collected the payment',
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_payments_no   (payment_no),
    INDEX idx_payments_ref  (payment_type, reference_id),

    CONSTRAINT fk_payments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    CONSTRAINT fk_payments_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 17. REPAIR JOBS
-- ==============================================================================
DROP TABLE IF EXISTS repair_jobs;
CREATE TABLE repair_jobs (
    id                          INT             AUTO_INCREMENT PRIMARY KEY,
    job_no                      VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. JOB-000001',
    customer_id                 INT             NOT NULL,
    device_brand                VARCHAR(200),
    device_model                VARCHAR(200),
    device_imei                 VARCHAR(100),
    device_serial               VARCHAR(100),
    device_color                VARCHAR(50),
    problem_description         TEXT            NOT NULL,
    device_condition            TEXT,
    device_password_note        TEXT            COMMENT 'Password/pattern/PIN if provided by customer',
    accessories_received        TEXT            COMMENT 'Comma-separated: Charger,Box,SIM Tray,Memory Card,Cover,Cable',
    estimated_cost              DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    advance_payment             DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    labour_charge               DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    parts_total                 DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    discount_amount             DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    final_amount                DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    paid_amount                 DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    due_amount                  DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    expected_delivery_date      DATE,
    delivered_date              DATETIME,
    assigned_technician_id      INT             COMMENT 'User ID with Technician role',
    priority                    ENUM('Normal','Urgent') NOT NULL DEFAULT 'Normal',
    approval_status             ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending' COMMENT 'Customer approval for estimate',
    repair_status               ENUM('Received','Checking','Waiting for Customer Approval','Waiting for Parts','Repairing','Completed','Delivered','Cancelled','Cannot Repair') NOT NULL DEFAULT 'Received',
    repair_warranty_days        INT             NOT NULL DEFAULT 30 COMMENT 'Warranty period in days after delivery',
    repair_warranty_start_date  DATE,
    repair_warranty_end_date    DATE,
    internal_notes              TEXT,
    customer_notes              TEXT,
    created_by                  INT,
    created_at                  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_rj_job_no  (job_no),
    INDEX idx_rj_status  (repair_status),
    INDEX idx_rj_imei    (device_imei),

    CONSTRAINT fk_rj_customer   FOREIGN KEY (customer_id)            REFERENCES customers(id),
    CONSTRAINT fk_rj_technician FOREIGN KEY (assigned_technician_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 18. REPAIR JOB PARTS
-- ==============================================================================
DROP TABLE IF EXISTS repair_job_parts;
CREATE TABLE repair_job_parts (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    repair_job_id   INT             NOT NULL,
    product_id      INT             NOT NULL,
    quantity        INT             NOT NULL DEFAULT 1,
    cost_price      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    selling_price   DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    total_price     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_rjp_job     FOREIGN KEY (repair_job_id) REFERENCES repair_jobs(id) ON DELETE CASCADE,
    CONSTRAINT fk_rjp_product FOREIGN KEY (product_id)    REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 19. REPAIR JOB STATUS HISTORY
-- ==============================================================================
DROP TABLE IF EXISTS repair_job_status_history;
CREATE TABLE repair_job_status_history (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    repair_job_id   INT             NOT NULL,
    old_status      VARCHAR(100)    COMMENT 'Previous status value',
    new_status      VARCHAR(100)    NOT NULL COMMENT 'New status value',
    note            TEXT,
    changed_by      INT             COMMENT 'User ID who changed the status',
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_rjsh_job FOREIGN KEY (repair_job_id) REFERENCES repair_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 20. WARRANTY RECORDS
-- ==============================================================================
DROP TABLE IF EXISTS warranty_records;
CREATE TABLE warranty_records (
    id                  INT             AUTO_INCREMENT PRIMARY KEY,
    warranty_type       ENUM('product','repair') NOT NULL,
    reference_type      VARCHAR(100)    NOT NULL COMMENT 'Entity type: sales_invoice_item, repair_job',
    reference_id        INT             NOT NULL COMMENT 'Entity ID',
    customer_id         INT,
    product_id          INT,
    imei_id             INT,
    warranty_start_date DATE            NOT NULL,
    warranty_end_date   DATE            NOT NULL,
    warranty_status     ENUM('Active','Expired','Claimed','Cancelled') NOT NULL DEFAULT 'Active',
    terms               TEXT,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_warranty_ref (warranty_type, reference_type, reference_id),

    CONSTRAINT fk_warranty_customer FOREIGN KEY (customer_id) REFERENCES customers(id)     ON DELETE SET NULL,
    CONSTRAINT fk_warranty_product  FOREIGN KEY (product_id)  REFERENCES products(id)       ON DELETE SET NULL,
    CONSTRAINT fk_warranty_imei     FOREIGN KEY (imei_id)     REFERENCES product_imei(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 21. WARRANTY CLAIMS
-- ==============================================================================
DROP TABLE IF EXISTS warranty_claims;
CREATE TABLE warranty_claims (
    id                      INT             AUTO_INCREMENT PRIMARY KEY,
    claim_no                VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. CLM-000001',
    warranty_record_id      INT             NOT NULL,
    customer_id             INT,
    claim_date              DATE            NOT NULL,
    issue_description       TEXT            NOT NULL,
    claim_status            ENUM('Pending','Checking','Approved','Rejected','Completed') NOT NULL DEFAULT 'Pending',
    decision_note           TEXT,
    replacement_product_id  INT             COMMENT 'Product ID if replacement is approved',
    replacement_imei_id     INT             COMMENT 'New IMEI if replacement phone is given',
    handled_by              INT             COMMENT 'User ID who handled the claim',
    created_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_claims_no (claim_no),

    CONSTRAINT fk_wc_warranty   FOREIGN KEY (warranty_record_id)     REFERENCES warranty_records(id) ON DELETE CASCADE,
    CONSTRAINT fk_wc_customer   FOREIGN KEY (customer_id)            REFERENCES customers(id)        ON DELETE SET NULL,
    CONSTRAINT fk_wc_replacement FOREIGN KEY (replacement_product_id) REFERENCES products(id)        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 22. RETURNS (sales returns & exchanges)
-- ==============================================================================
DROP TABLE IF EXISTS returns_;
CREATE TABLE returns_ (
    id                INT             AUTO_INCREMENT PRIMARY KEY,
    return_no         VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. RET-000001',
    sales_invoice_id  INT,
    customer_id       INT,
    return_date       DATE            NOT NULL,
    return_type       ENUM('return','exchange') NOT NULL,
    refund_amount     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    reason            TEXT,
    status            ENUM('Pending','Approved','Rejected','Completed') NOT NULL DEFAULT 'Pending',
    created_by        INT,
    created_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_returns_no (return_no),

    CONSTRAINT fk_returns_invoice   FOREIGN KEY (sales_invoice_id) REFERENCES sales_invoices(id) ON DELETE SET NULL,
    CONSTRAINT fk_returns_customer  FOREIGN KEY (customer_id)      REFERENCES customers(id)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 23. RETURN ITEMS
-- ==============================================================================
DROP TABLE IF EXISTS return_items;
CREATE TABLE return_items (
    id                      INT             AUTO_INCREMENT PRIMARY KEY,
    return_id               INT             NOT NULL,
    sales_invoice_item_id   INT,
    product_id              INT             NOT NULL,
    imei_id                 INT,
    quantity                INT             NOT NULL DEFAULT 1,
    refund_amount           DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    created_at              TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_ri_return   FOREIGN KEY (return_id)             REFERENCES returns_(id)        ON DELETE CASCADE,
    CONSTRAINT fk_ri_item     FOREIGN KEY (sales_invoice_item_id) REFERENCES sales_invoice_items(id) ON DELETE SET NULL,
    CONSTRAINT fk_ri_product  FOREIGN KEY (product_id)            REFERENCES products(id),
    CONSTRAINT fk_ri_imei     FOREIGN KEY (imei_id)               REFERENCES product_imei(id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 24. EXPENSE CATEGORIES
-- ==============================================================================
DROP TABLE IF EXISTS expense_categories;
CREATE TABLE expense_categories (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    category_name   VARCHAR(200)    NOT NULL,
    description     TEXT,
    status          TINYINT(1)      NOT NULL DEFAULT 1,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 25. EXPENSES
-- ==============================================================================
DROP TABLE IF EXISTS expenses;
CREATE TABLE expenses (
    id                  INT             AUTO_INCREMENT PRIMARY KEY,
    expense_no          VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. EXP-000001',
    expense_category_id INT,
    expense_date        DATE            NOT NULL,
    amount              DECIMAL(12,2)   NOT NULL,
    payment_method      ENUM('Cash','Card','Bank Transfer','Online Payment') DEFAULT 'Cash',
    description         TEXT,
    created_by          INT,
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_expenses_no   (expense_no),
    INDEX idx_expenses_date (expense_date),

    CONSTRAINT fk_expenses_category FOREIGN KEY (expense_category_id) REFERENCES expense_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 26. NOTIFICATION TEMPLATES
-- ==============================================================================
DROP TABLE IF EXISTS notification_templates;
CREATE TABLE notification_templates (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    template_key    VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Dot-notation key, e.g. repair.completed',
    template_name   VARCHAR(200)    NOT NULL,
    message_body    TEXT            NOT NULL COMMENT 'Template with placeholders like {customer_name}',
    status          TINYINT(1)      NOT NULL DEFAULT 1,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 27. SETTINGS
-- ==============================================================================
DROP TABLE IF EXISTS settings;
CREATE TABLE settings (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    setting_key     VARCHAR(100)    NOT NULL UNIQUE,
    setting_value   TEXT,
    setting_group   VARCHAR(100)    NOT NULL DEFAULT 'general' COMMENT 'Grouping: general, tax, invoice, warranty',
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 28. AUDIT LOGS
-- ==============================================================================
DROP TABLE IF EXISTS audit_logs;
CREATE TABLE audit_logs (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    user_id         INT             COMMENT 'NULL for system actions',
    action          VARCHAR(100)    NOT NULL COMMENT 'e.g. create, update, delete, login, logout',
    module_name     VARCHAR(100)    NOT NULL COMMENT 'e.g. Products, Sales, Repairs',
    record_id       INT             COMMENT 'ID of the affected record',
    old_data        JSON            COMMENT 'Snapshot of data before change',
    new_data        JSON            COMMENT 'Snapshot of data after change',
    ip_address      VARCHAR(45),
    user_agent      TEXT,
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_audit_user   (user_id),
    INDEX idx_audit_module (module_name, record_id),
    INDEX idx_audit_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- Re-enable foreign key checks
-- ==============================================================================
SET FOREIGN_KEY_CHECKS = 1;
