/* GCMA Production Upgrade (SAFE for existing databases)
   Target: https://gcma.ait-academy.com
   Run on an EXISTING production database that already has data.
   Idempotent (safe to re-run). Does NOT wipe transactional data.
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

/* 1) Ensure migrations ledger exists */
CREATE TABLE IF NOT EXISTS migrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    migration VARCHAR(255) NOT NULL UNIQUE,
    executed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

/* 2) Item Code table + zipper columns (create if missing, alter if partial) */
CREATE TABLE IF NOT EXISTS item_code (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL,
    item_code_description TEXT NULL,
    prefix_id BIGINT UNSIGNED NULL,
    item_type VARCHAR(80) NULL,
    supplier_item_code VARCHAR(100) NULL,
    description VARCHAR(255) NULL,
    composition VARCHAR(255) NULL,
    composition_json TEXT NULL,
    construction VARCHAR(120) NULL,
    weight VARCHAR(50) NULL,
    weight_type VARCHAR(50) NULL,
    color_id BIGINT UNSIGNED NULL,
    finish_id BIGINT UNSIGNED NULL,
    finish VARCHAR(120) NULL,
    width_1 VARCHAR(50) NULL,
    width_type_1 VARCHAR(50) NULL,
    width_2 VARCHAR(50) NULL,
    width_type_2 VARCHAR(50) NULL,
    tex VARCHAR(50) NULL,
    tkt VARCHAR(50) NULL,
    yarn_count VARCHAR(50) NULL,
    length_value VARCHAR(50) NULL,
    length_type VARCHAR(50) NULL,
    zipper_no VARCHAR(80) NULL,
    lock_type VARCHAR(80) NULL,
    end_type VARCHAR(80) NULL,
    zipper_tape_color_id BIGINT UNSIGNED NULL,
    default_uom_id BIGINT UNSIGNED NULL,
    future_1 VARCHAR(120) NULL,
    future_2 VARCHAR(120) NULL,
    future_3 VARCHAR(120) NULL,
    future_4 VARCHAR(120) NULL,
    future_5 VARCHAR(120) NULL,
    future_6 VARCHAR(120) NULL,
    other_information TEXT NULL,
    supplier_id BIGINT UNSIGNED NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_item_code (code),
    KEY idx_item_code_prefix (prefix_id),
    KEY idx_item_code_supplier (supplier_id),
    KEY idx_item_code_type (item_type),
    KEY idx_item_code_zipper_tape (zipper_tape_color_id)
) ENGINE=InnoDB;

/* Add missing columns on older item_code tables */
SET @db := DATABASE();

SET @sql := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='item_code' AND COLUMN_NAME='composition_json'),
    'SELECT 1',
    'ALTER TABLE item_code ADD COLUMN composition_json TEXT NULL AFTER composition'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='item_code' AND COLUMN_NAME='finish'),
    'SELECT 1',
    'ALTER TABLE item_code ADD COLUMN finish VARCHAR(120) NULL AFTER finish_id'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='item_code' AND COLUMN_NAME='length_type'),
    'SELECT 1',
    'ALTER TABLE item_code ADD COLUMN length_type VARCHAR(50) NULL AFTER length_value'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='item_code' AND COLUMN_NAME='zipper_no'),
    'SELECT 1',
    'ALTER TABLE item_code ADD COLUMN zipper_no VARCHAR(80) NULL AFTER length_type'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='item_code' AND COLUMN_NAME='lock_type'),
    'SELECT 1',
    'ALTER TABLE item_code ADD COLUMN lock_type VARCHAR(80) NULL AFTER zipper_no'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='item_code' AND COLUMN_NAME='end_type'),
    'SELECT 1',
    'ALTER TABLE item_code ADD COLUMN end_type VARCHAR(80) NULL AFTER lock_type'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='item_code' AND COLUMN_NAME='zipper_tape_color_id'),
    'SELECT 1',
    'ALTER TABLE item_code ADD COLUMN zipper_tape_color_id BIGINT UNSIGNED NULL AFTER end_type'
  )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 3) CRUD + Item Code permissions (fixes Role Permissions save errors) */
INSERT INTO permissions (code, name, module) VALUES
('master.view', 'View Master Data', 'master'),
('master.add', 'Add Master Data', 'master'),
('master.edit', 'Edit Master Data', 'master'),
('master.delete', 'Delete Master Data', 'master'),
('item_code.view', 'View Item Code', 'item_code'),
('item_code.add', 'Add Item Code', 'item_code'),
('item_code.edit', 'Edit Item Code', 'item_code'),
('item_code.delete', 'Delete Item Code', 'item_code'),
('cost.view', 'View Cost Engine', 'cost'),
('cost.add', 'Add Cost Engine', 'cost'),
('cost.edit', 'Edit Cost Engine', 'cost'),
('cost.delete', 'Delete Cost Engine', 'cost'),
('costing_op.view', 'View Costing OP', 'costing_op'),
('costing_op.add', 'Add Costing OP', 'costing_op'),
('costing_op.edit', 'Edit Costing OP', 'costing_op'),
('costing_op.delete', 'Delete Costing OP', 'costing_op'),
('bom.view', 'View BOM Management', 'bom'),
('bom.add', 'Add BOM Management', 'bom'),
('bom.edit', 'Edit BOM Management', 'bom'),
('bom.delete', 'Delete BOM Management', 'bom'),
('mrp.view', 'View MRP Management', 'mrp'),
('mrp.add', 'Add MRP Management', 'mrp'),
('mrp.edit', 'Edit MRP Management', 'mrp'),
('mrp.delete', 'Delete MRP Management', 'mrp'),
('purchase.view', 'View Purchase Management', 'purchase'),
('purchase.add', 'Add Purchase Management', 'purchase'),
('purchase.edit', 'Edit Purchase Management', 'purchase'),
('purchase.delete', 'Delete Purchase Management', 'purchase'),
('inventory.view', 'View Inventory Management', 'inventory'),
('inventory.add', 'Add Inventory Management', 'inventory'),
('inventory.edit', 'Edit Inventory Management', 'inventory'),
('inventory.delete', 'Delete Inventory Management', 'inventory'),
('production.view', 'View Production Management', 'production'),
('production.add', 'Add Production Management', 'production'),
('production.edit', 'Edit Production Management', 'production'),
('production.delete', 'Delete Production Management', 'production'),
('quality.view', 'View Quality Management', 'quality'),
('quality.add', 'Add Quality Management', 'quality'),
('quality.edit', 'Edit Quality Management', 'quality'),
('quality.delete', 'Delete Quality Management', 'quality'),
('hr.view', 'View HR Management', 'hr'),
('hr.add', 'Add HR Management', 'hr'),
('hr.edit', 'Edit HR Management', 'hr'),
('hr.delete', 'Delete HR Management', 'hr'),
('accounting.view', 'View Accounting Management', 'accounting'),
('accounting.add', 'Add Accounting Management', 'accounting'),
('accounting.edit', 'Edit Accounting Management', 'accounting'),
('accounting.delete', 'Delete Accounting Management', 'accounting'),
('shipment.view', 'View Shipment Management', 'shipment'),
('shipment.add', 'Add Shipment Management', 'shipment'),
('shipment.edit', 'Edit Shipment Management', 'shipment'),
('shipment.delete', 'Delete Shipment Management', 'shipment'),
('reports.view', 'View Reports Management', 'reports'),
('reports.add', 'Add Reports Management', 'reports'),
('reports.edit', 'Edit Reports Management', 'reports'),
('reports.delete', 'Delete Reports Management', 'reports'),
('users.view', 'View User Permissions', 'users'),
('users.add', 'Add User Permissions', 'users'),
('users.edit', 'Edit User Permissions', 'users'),
('users.delete', 'Delete User Permissions', 'users')
ON DUPLICATE KEY UPDATE name = VALUES(name), module = VALUES(module);

/* Grant all CRUD permissions to system ADMIN roles */
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
CROSS JOIN permissions p
WHERE r.code = 'ADMIN'
  AND r.is_system = 1
  AND (
      p.code LIKE '%.view'
      OR p.code LIKE '%.add'
      OR p.code LIKE '%.edit'
      OR p.code LIKE '%.delete'
  );

/* 4) Prefix Item Type examples (Item Master dropdowns) */
UPDATE prefix_masters SET examples = 'Denim, Twill, Poplin' WHERE name = 'Fabric' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'Single Jersey, Double Jersey, Rib, Interlock, Pique, Fleece' WHERE name = 'Knit Fabric' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'TC Sheeting, Sheeting, TC, Twill, TC Twill, Satin' WHERE name = 'Pocketing' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'Dot Fused, Micro Dot, Non Woven, Woven' WHERE name = 'Interlining' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'Metal, Plastic, Nylon, Coil, Invisible' WHERE name = 'Zipper' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'Button, Rivet, Buckle, Eyelet' WHERE name = 'Metal Item' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'Leather, Fabric, PVC, Embroidery, Metal, Rubber' WHERE name = 'Badge-Patch' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'Print, Embroidery, Test, Inspection, Wash' WHERE name = 'Service' AND deleted_at IS NULL;
UPDATE prefix_masters SET examples = 'Lace, Elastic, Drawstring, Cord' WHERE name = 'Lace-Elastic' AND deleted_at IS NULL;
UPDATE prefix_masters SET name = 'Badge-Patch' WHERE name IN ('Badge/Patch', 'Badge Patch') AND deleted_at IS NULL;
UPDATE prefix_masters SET name = 'Lace-Elastic' WHERE name IN ('Lace, Elastic', 'Lace/Elastic') AND deleted_at IS NULL;

/* 5) Unit Master polish */
UPDATE unit_masters
SET unit_name = 'Con'
WHERE item_category = 'Thread'
  AND unit_code = 'CONE'
  AND deleted_at IS NULL;

/* 6) Color auto-code collision fix
   Soft-deleted rows still occupy UNIQUE(code). Free those codes so Add Color works. */
UPDATE colors
SET code = CONCAT(code, '__del_', id)
WHERE deleted_at IS NOT NULL
  AND code REGEXP '^[0-9]+$'
  AND code NOT LIKE '%__del_%';

/* 7) Mark migrations as applied (ledger only; schema handled above) */
INSERT IGNORE INTO migrations (migration) VALUES
('024_role_crud_permissions.sql'),
('025_item_code.sql'),
('026_item_code_fabric_form.sql'),
('027_item_code_module_permissions.sql'),
('028_prefix_item_names.sql'),
('029_item_code_zipper_form.sql');

SET FOREIGN_KEY_CHECKS = 1;