-- Align an existing StockFlow database with the verified 21-table baseline.
-- Take a backup before running this migration; existing rows are preserved.
-- Run this file after selecting the target database.  Do not hard-code the
-- cPanel-prefixed database name here.

ALTER TABLE units
  ADD COLUMN IF NOT EXISTS unit_code VARCHAR(20) NULL AFTER unit_id,
  ADD COLUMN IF NOT EXISTS symbol VARCHAR(10) NULL AFTER unit_name,
  ADD COLUMN IF NOT EXISTS status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  ADD COLUMN IF NOT EXISTS created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
UPDATE units SET unit_code = CONCAT('UNIT-', LPAD(unit_id, 4, '0')) WHERE unit_code IS NULL;
UPDATE units SET symbol = LEFT(unit_name, 10) WHERE symbol IS NULL;
ALTER TABLE units MODIFY unit_code VARCHAR(20) NOT NULL, MODIFY symbol VARCHAR(10) NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_units_code ON units(unit_code);

ALTER TABLE products
  ADD COLUMN IF NOT EXISTS import_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER minimum_stock,
  ADD COLUMN IF NOT EXISTS export_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER import_price;
ALTER TABLE suppliers ADD COLUMN IF NOT EXISTS tax_code VARCHAR(30) NULL AFTER supplier_name;

CREATE TABLE IF NOT EXISTS customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_code VARCHAR(40) NOT NULL UNIQUE,
  customer_name VARCHAR(160) NOT NULL, tax_code VARCHAR(30), contact_name VARCHAR(120),
  phone VARCHAR(30), email VARCHAR(120), address VARCHAR(255),
  status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ALTER TABLE import_receipts
  ADD COLUMN IF NOT EXISTS import_type ENUM('PURCHASE','FINISHED_GOODS','CUSTOMER_RETURN','OTHER') NOT NULL DEFAULT 'PURCHASE' AFTER import_code,
  ADD COLUMN IF NOT EXISTS customer_id INT NULL AFTER supplier_id,
  ADD COLUMN IF NOT EXISTS source_export_id INT NULL AFTER customer_id,
  ADD COLUMN IF NOT EXISTS source_name VARCHAR(160) NULL AFTER delivery_person,
  ADD COLUMN IF NOT EXISTS reason VARCHAR(255) NULL AFTER import_date;
ALTER TABLE import_details
  ADD COLUMN IF NOT EXISTS unit_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER actual_quantity,
  ADD COLUMN IF NOT EXISTS import_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER unit_price,
  ADD COLUMN IF NOT EXISTS note VARCHAR(255) NULL;
UPDATE import_details SET unit_price = import_price WHERE unit_price = 0;

ALTER TABLE export_receipts
  ADD COLUMN IF NOT EXISTS export_type ENUM('SALE','SUPPLIER_RETURN','INTERNAL_USE','DISPOSAL','OTHER') NOT NULL DEFAULT 'SALE' AFTER export_code,
  ADD COLUMN IF NOT EXISTS customer_id INT NULL AFTER warehouse_id,
  ADD COLUMN IF NOT EXISTS supplier_id INT NULL AFTER customer_id,
  ADD COLUMN IF NOT EXISTS source_import_id INT NULL AFTER supplier_id,
  ADD COLUMN IF NOT EXISTS reason VARCHAR(255) NULL AFTER export_date;
ALTER TABLE export_details
  ADD COLUMN IF NOT EXISTS unit_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER actual_quantity,
  ADD COLUMN IF NOT EXISTS export_price DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER unit_price,
  ADD COLUMN IF NOT EXISTS note VARCHAR(255) NULL;
UPDATE export_details SET unit_price = export_price WHERE unit_price = 0;

CREATE TABLE IF NOT EXISTS transfer_receipts (
  transfer_id INT AUTO_INCREMENT PRIMARY KEY, transfer_code VARCHAR(40) NOT NULL UNIQUE,
  from_warehouse_id INT NOT NULL, to_warehouse_id INT NOT NULL, created_by INT NOT NULL,
  carrier_name VARCHAR(120), transfer_date DATE NOT NULL, reason VARCHAR(255), note VARCHAR(255),
  status ENUM('DRAFT','CONFIRMED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_transfer_from FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(warehouse_id),
  CONSTRAINT fk_transfer_to FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(warehouse_id),
  CONSTRAINT fk_transfer_user FOREIGN KEY (created_by) REFERENCES users(user_id),
  CONSTRAINT chk_transfer_warehouses CHECK (from_warehouse_id <> to_warehouse_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS transfer_details (
  transfer_detail_id INT AUTO_INCREMENT PRIMARY KEY, transfer_id INT NOT NULL, product_id INT NOT NULL,
  requested_quantity DECIMAL(14,2) NOT NULL, actual_quantity DECIMAL(14,2) NOT NULL, note VARCHAR(255),
  UNIQUE KEY uq_transfer_product (transfer_id, product_id),
  FOREIGN KEY (transfer_id) REFERENCES transfer_receipts(transfer_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id),
  CONSTRAINT chk_transfer_qty CHECK (requested_quantity > 0 AND actual_quantity > 0)
) ENGINE=InnoDB;

ALTER TABLE stock_checks ADD COLUMN IF NOT EXISTS approved_by INT NULL AFTER created_by;
ALTER TABLE stock_checks MODIFY COLUMN status ENUM('DRAFT','APPROVED','ADJUSTED','CONFIRMED','CANCELLED') NOT NULL DEFAULT 'DRAFT';
UPDATE stock_checks SET status='CONFIRMED' WHERE status IN ('APPROVED','ADJUSTED');
ALTER TABLE stock_checks MODIFY COLUMN status ENUM('DRAFT','CONFIRMED','CANCELLED') NOT NULL DEFAULT 'DRAFT';
CREATE TABLE IF NOT EXISTS adjustment_receipts (
  adjustment_id INT AUTO_INCREMENT PRIMARY KEY, adjustment_code VARCHAR(40) NOT NULL UNIQUE,
  warehouse_id INT NOT NULL, check_id INT NULL, created_by INT NOT NULL, approved_by INT NULL,
  adjustment_date DATE NOT NULL, reason VARCHAR(255) NOT NULL, note VARCHAR(255),
  status ENUM('DRAFT','CONFIRMED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),
  FOREIGN KEY (check_id) REFERENCES stock_checks(check_id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(user_id), FOREIGN KEY (approved_by) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS adjustment_details (
  adjustment_detail_id INT AUTO_INCREMENT PRIMARY KEY, adjustment_id INT NOT NULL, product_id INT NOT NULL,
  system_quantity DECIMAL(14,2) NOT NULL, actual_quantity DECIMAL(14,2) NOT NULL, quantity_change DECIMAL(14,2) NOT NULL,
  damaged_change DECIMAL(14,2) NOT NULL DEFAULT 0, obsolete_change DECIMAL(14,2) NOT NULL DEFAULT 0, note VARCHAR(255),
  UNIQUE KEY uq_adjustment_product (adjustment_id, product_id),
  FOREIGN KEY (adjustment_id) REFERENCES adjustment_receipts(adjustment_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id),
  CONSTRAINT chk_adjustment_values CHECK (system_quantity >= 0 AND actual_quantity >= 0 AND quantity_change = actual_quantity - system_quantity)
) ENGINE=InnoDB;

ALTER TABLE inventory_transactions
  ADD COLUMN IF NOT EXISTS transaction_date DATETIME NULL AFTER quantity_after;
ALTER TABLE inventory_transactions ADD COLUMN IF NOT EXISTS created_at DATETIME NULL AFTER transaction_date;
UPDATE inventory_transactions SET transaction_date = created_at WHERE transaction_date IS NULL;
UPDATE inventory_transactions SET created_at = transaction_date WHERE created_at IS NULL;
ALTER TABLE inventory_transactions MODIFY transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  MODIFY transaction_type ENUM('IMPORT','EXPORT','TRANSFER_OUT','TRANSFER_IN','ADJUSTMENT','DAMAGE_ADJUSTMENT','OBSOLETE_ADJUSTMENT') NOT NULL;

CREATE TABLE IF NOT EXISTS vat_invoices (
  invoice_id INT AUTO_INCREMENT PRIMARY KEY, invoice_number VARCHAR(60) NOT NULL,
  invoice_type ENUM('INPUT','OUTPUT') NOT NULL, invoice_date DATE NOT NULL,
  supplier_id INT NULL, customer_id INT NULL, import_id INT NULL, export_id INT NULL,
  seller_name VARCHAR(160), seller_tax_code VARCHAR(30), buyer_name VARCHAR(160), buyer_tax_code VARCHAR(30),
  subtotal DECIMAL(16,2) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  vat_amount DECIMAL(16,2) NOT NULL DEFAULT 0, total_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  status ENUM('DRAFT','ISSUED','CANCELLED') NOT NULL DEFAULT 'DRAFT', created_by INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_vat_invoice_type_number (invoice_type, invoice_number),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id) ON DELETE SET NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL,
  FOREIGN KEY (import_id) REFERENCES import_receipts(import_id), FOREIGN KEY (export_id) REFERENCES export_receipts(export_id),
  FOREIGN KEY (created_by) REFERENCES users(user_id),
  CONSTRAINT chk_vat_amounts CHECK (subtotal >= 0 AND vat_rate BETWEEN 0 AND 100 AND vat_amount >= 0 AND total_amount >= 0)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS vat_invoice_details (
  invoice_detail_id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, product_id INT NOT NULL,
  quantity DECIMAL(14,2) NOT NULL, unit_price DECIMAL(14,2) NOT NULL, subtotal DECIMAL(16,2) NOT NULL,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_amount DECIMAL(16,2) NOT NULL DEFAULT 0, total_amount DECIMAL(16,2) NOT NULL,
  FOREIGN KEY (invoice_id) REFERENCES vat_invoices(invoice_id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(product_id),
  CONSTRAINT chk_vat_detail_values CHECK (quantity > 0 AND unit_price >= 0 AND subtotal >= 0 AND vat_rate BETWEEN 0 AND 100 AND vat_amount >= 0 AND total_amount >= 0)
) ENGINE=InnoDB;

DROP VIEW IF EXISTS vw_inventory_summary;
CREATE VIEW vw_inventory_summary AS
SELECT i.inventory_id, i.warehouse_id, w.warehouse_code, w.warehouse_name, i.product_id, p.product_code, p.product_name,
 c.category_name, u.unit_code, u.unit_name, u.symbol, i.quantity, i.damaged_quantity, i.obsolete_quantity,
 i.quantity-i.damaged_quantity-i.obsolete_quantity AS available_quantity, p.minimum_stock,
 CASE WHEN i.quantity-i.damaged_quantity-i.obsolete_quantity=0 THEN 'OUT_OF_STOCK'
      WHEN i.quantity-i.damaged_quantity-i.obsolete_quantity<=p.minimum_stock THEN 'LOW_STOCK' ELSE 'IN_STOCK' END AS stock_status, i.updated_at
FROM inventory i JOIN warehouses w ON w.warehouse_id=i.warehouse_id JOIN products p ON p.product_id=i.product_id
JOIN categories c ON c.category_id=p.category_id JOIN units u ON u.unit_id=p.unit_id;
DROP VIEW IF EXISTS vw_stock_card;
CREATE VIEW vw_stock_card AS
SELECT t.transaction_id, t.transaction_date, t.warehouse_id, w.warehouse_code, w.warehouse_name, t.product_id,
 p.product_code, p.product_name, t.transaction_type, t.reference_type, t.reference_id, t.quantity_before,
 t.quantity_change, t.quantity_after, t.note, u.full_name AS created_by_name
FROM inventory_transactions t JOIN warehouses w ON w.warehouse_id=t.warehouse_id JOIN products p ON p.product_id=t.product_id
LEFT JOIN users u ON u.user_id=t.created_by;