-- ==============================================================================
-- DigiMobiz Migration v2 - GRN tables, new permissions, stock movement type
-- ==============================================================================

USE digimobiz;

SET FOREIGN_KEY_CHECKS = 0;

-- ==============================================================================
-- 1. Add grn_receive to stock_movements.movement_type ENUM
-- ==============================================================================
ALTER TABLE stock_movements
MODIFY COLUMN movement_type ENUM('purchase','sale','repair_usage','return','adjustment','damaged','grn_receive') NOT NULL;

-- ==============================================================================
-- 2. GRN (Goods Received Note) tables
-- ==============================================================================
DROP TABLE IF EXISTS grn_item_imeis;
DROP TABLE IF EXISTS grn_items;
DROP TABLE IF EXISTS grns;

CREATE TABLE grns (
    id                  INT             AUTO_INCREMENT PRIMARY KEY,
    grn_no              VARCHAR(100)    NOT NULL UNIQUE COMMENT 'Auto-generated, e.g. GRN-000001',
    supplier_id         INT             NOT NULL,
    received_date       DATE            NOT NULL,
    supplier_invoice_no VARCHAR(200),
    purchase_order_no   VARCHAR(100),
    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,
    status              ENUM('Draft','Received','Cancelled') NOT NULL DEFAULT 'Draft',
    note                TEXT,
    received_by         INT             COMMENT 'User ID who received the goods',
    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_grn_no (grn_no),

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

CREATE TABLE grn_items (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    grn_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_gi_grn     FOREIGN KEY (grn_id)    REFERENCES grns(id) ON DELETE CASCADE,
    CONSTRAINT fk_gi_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE grn_item_imeis (
    id              INT             AUTO_INCREMENT PRIMARY KEY,
    grn_item_id     INT             NOT NULL,
    product_id      INT             NOT NULL,
    imei_number     VARCHAR(100)    NOT NULL UNIQUE,
    serial_number   VARCHAR(100),
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_gii_imei (imei_number),

    CONSTRAINT fk_gii_item    FOREIGN KEY (grn_item_id) REFERENCES grn_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_gii_product FOREIGN KEY (product_id)  REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- 3. New Permissions for Repair Parts, Payment, Invoice, GRN
-- ==============================================================================
INSERT INTO permissions (permission_key, permission_name, module_name, description) VALUES
('repair_parts_manage',  'Repair Parts Management',  'Repairs', 'Add and remove spare parts from repair jobs'),
('repair_payment',       'Repair Payment',           'Repairs', 'Collect payments for repair jobs'),
('repair_invoice_view',  'Repair Invoice View',      'Repairs', 'View and print repair invoices'),
('grn_manage',           'GRN Management',           'Purchases', 'Manage GRN (Goods Received Notes)'),
('grn_create',           'GRN Create',               'Purchases', 'Create new GRN'),
('grn_view',             'GRN View',                 'Purchases', 'View GRN details'),
('grn_print',            'GRN Print',                'Purchases', 'Print GRN documents');

-- Super Admin (role_id=1) gets all new permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions p
WHERE p.permission_key IN ('repair_parts_manage','repair_payment','repair_invoice_view','grn_manage','grn_create','grn_view','grn_print')
AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id = 1 AND rp.permission_id = p.id);

-- Admin (role_id=2) gets repair and GRN permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions p
WHERE p.permission_key IN ('repair_parts_manage','repair_payment','repair_invoice_view','grn_manage','grn_create','grn_view','grn_print')
AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id = 2 AND rp.permission_id = p.id);

-- Technician (role_id=4) gets repair parts manage
INSERT INTO role_permissions (role_id, permission_id)
SELECT 4, id FROM permissions p
WHERE p.permission_key IN ('repair_parts_manage')
AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id = 4 AND rp.permission_id = p.id);

-- Cashier (role_id=3) gets repair payment and invoice view
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions p
WHERE p.permission_key IN ('repair_payment','repair_invoice_view')
AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id = 3 AND rp.permission_id = p.id);

-- Stock Manager (role_id=5) gets GRN permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT 5, id FROM permissions p
WHERE p.permission_key IN ('grn_manage','grn_create','grn_view','grn_print')
AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id = 5 AND rp.permission_id = p.id);

-- Accountant (role_id=6) gets invoice view
INSERT INTO role_permissions (role_id, permission_id)
SELECT 6, id FROM permissions p
WHERE p.permission_key IN ('repair_invoice_view','grn_view','grn_print')
AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id = 6 AND rp.permission_id = p.id);

SET FOREIGN_KEY_CHECKS = 1;
