-- Gold Jewellery Software - Schema Gap Fixes
-- Migration 002: RBAC, Settings/Invoice Numbering, Tax Config, Making-Charge Snapshot
-- Depends on: gold_jewellery_software_database.sql (migration 001)
-- MySQL 8.4 LTS
USE gold_jewellery;

-- ---------------------------------------------------------------------
-- 1. Granular RBAC (roles table already exists; this adds real
--    permission checks instead of hardcoded role-name checks in code)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS permissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 slug VARCHAR(100) NOT NULL UNIQUE,      -- e.g. 'sales.create', 'inventory.adjust'
 description VARCHAR(255),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_permissions (
 role_id BIGINT UNSIGNED NOT NULL,
 permission_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY (role_id, permission_id),
 FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
 FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seed a starter permission set. Extend per module as you build.
INSERT INTO permissions (slug, description) VALUES
 ('dashboard.view','View dashboard & KPIs'),
 ('customer.manage','Create/edit customers'),
 ('supplier.manage','Create/edit suppliers'),
 ('jewellery.manage','Create/edit jewellery master items'),
 ('gold_rate.manage','Set/update daily gold rates'),
 ('purchase.manage','Record purchases / stock in'),
 ('inventory.adjust','Adjust or transfer stock'),
 ('karagir.manage','Issue/receive karagir transactions'),
 ('sales.create','Create sales / invoices'),
 ('sales.void','Cancel or return a posted sale'),
 ('order.manage','Manage customer orders'),
 ('payment.record','Record payments/receipts/refunds'),
 ('reports.view','View reports & analytics'),
 ('settings.manage','Manage system settings & tax config'),
 ('audit.view','View audit logs')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- Example seeding: ADMIN gets everything, SALES gets the sales-floor subset.
-- Run once roles are seeded; adjust to your actual role IDs.
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.name = 'ADMIN'
ON DUPLICATE KEY UPDATE role_id = role_id;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
  ON p.slug IN ('dashboard.view','customer.manage','sales.create','payment.record','reports.view')
WHERE r.name = 'SALES'
ON DUPLICATE KEY UPDATE role_id = role_id;

-- ---------------------------------------------------------------------
-- 2. System settings (key-value) + dedicated invoice numbering table
--    (atomic increments need row locking, which a plain settings
--    key-value table makes awkward under concurrency)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
 setting_key VARCHAR(100) NOT NULL PRIMARY KEY,
 setting_value TEXT,
 updated_by BIGINT UNSIGNED NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
 ('shop_name', 'Your Jewellery Shop'),
 ('shop_gstin', ''),
 ('shop_state_code', '28'),          -- GST state code, e.g. 28 = Andhra Pradesh
 ('default_purity', '22K'),
 ('rate_fallback_max_days', '3')     -- how many days back GoldRateService may look for a fallback rate
ON DUPLICATE KEY UPDATE setting_value = setting_value;

CREATE TABLE IF NOT EXISTS invoice_sequences (
 series VARCHAR(30) NOT NULL,           -- e.g. 'SALE', 'PURCHASE', 'ORDER'
 financial_year VARCHAR(9) NOT NULL,    -- e.g. '2026-27'
 prefix VARCHAR(20) NOT NULL DEFAULT '',
 next_number INT UNSIGNED NOT NULL DEFAULT 1,
 padding TINYINT UNSIGNED NOT NULL DEFAULT 5,
 PRIMARY KEY (series, financial_year)
) ENGINE=InnoDB;

INSERT INTO invoice_sequences (series, financial_year, prefix, next_number, padding) VALUES
 ('SALE', '2026-27', 'INV', 1, 5),
 ('PURCHASE', '2026-27', 'PUR', 1, 5),
 ('ORDER', '2026-27', 'ORD', 1, 5)
ON DUPLICATE KEY UPDATE series = series;

-- ---------------------------------------------------------------------
-- 3. Tax / GST configuration (rate + HSN, with CGST/SGST/IGST split
--    resolved in code based on customer state vs shop state)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tax_rates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 hsn_code VARCHAR(20) NOT NULL,
 description VARCHAR(150),
 rate_percent DECIMAL(5,2) NOT NULL,      -- total GST %, e.g. 3.00 for gold
 effective_from DATE NOT NULL,
 effective_to DATE NULL,
 is_default BOOLEAN NOT NULL DEFAULT FALSE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_hsn_effective (hsn_code, effective_from)
) ENGINE=InnoDB;

INSERT INTO tax_rates (hsn_code, description, rate_percent, effective_from, is_default) VALUES
 ('7113', 'Gold jewellery', 3.00, '2026-01-01', TRUE),
 ('9988', 'Karagir making/job work', 5.00, '2026-01-01', FALSE)
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- Add a customer/supplier state code so TaxService can decide
-- intra-state (CGST+SGST) vs inter-state (IGST) without guessing.
ALTER TABLE customers ADD COLUMN IF NOT EXISTS state_code VARCHAR(4) NULL AFTER gstin;
ALTER TABLE suppliers ADD COLUMN IF NOT EXISTS state_code VARCHAR(4) NULL AFTER gstin;

-- ---------------------------------------------------------------------
-- 4. Snapshot making_charge_type onto transaction line items.
--    Without this, a change to jewellery_items.making_charge_type
--    after a sale would retroactively change how historical invoices
--    are interpreted.
-- ---------------------------------------------------------------------
ALTER TABLE sales_items
  ADD COLUMN IF NOT EXISTS making_charge_type ENUM('AMOUNT','PER_GRAM','PERCENT')
    NOT NULL DEFAULT 'AMOUNT' AFTER making_charge;

ALTER TABLE purchase_items
  ADD COLUMN IF NOT EXISTS making_charge_type ENUM('AMOUNT','PER_GRAM','PERCENT')
    NOT NULL DEFAULT 'AMOUNT' AFTER making_charge;

ALTER TABLE order_items
  ADD COLUMN IF NOT EXISTS making_charge_type ENUM('AMOUNT','PER_GRAM','PERCENT')
    NOT NULL DEFAULT 'AMOUNT' AFTER making_charge;

-- Also snapshot the HSN code used at sale time, since tax_rates can change.
ALTER TABLE sales_items
  ADD COLUMN IF NOT EXISTS hsn_code VARCHAR(20) NULL AFTER tax;