-- v1.13 Warehouse Module (MySQL)
-- Run after sql/schema.sql is applied.

SET NAMES utf8mb4;

-- Extend warehouses
ALTER TABLE warehouses
  ADD COLUMN code VARCHAR(20) NULL UNIQUE,
  ADD COLUMN is_main TINYINT(1) NOT NULL DEFAULT 0;

UPDATE warehouses SET code = COALESCE(code, CONCAT('WH', id)) WHERE code IS NULL;
UPDATE warehouses SET is_main = 1 WHERE id = 1;

-- Categories
CREATE TABLE IF NOT EXISTS categories (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Units
CREATE TABLE IF NOT EXISTS units (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  symbol VARCHAR(20) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Extend items
ALTER TABLE items
  ADD COLUMN category_id BIGINT UNSIGNED NULL,
  ADD COLUMN default_unit_id BIGINT UNSIGNED NULL,
  ADD COLUMN reorder_point DECIMAL(14,3) NOT NULL DEFAULT 0,
  ADD COLUMN low_stock_level DECIMAL(14,3) NOT NULL DEFAULT 0;

ALTER TABLE items
  ADD CONSTRAINT fk_items_category FOREIGN KEY (category_id) REFERENCES categories(id),
  ADD CONSTRAINT fk_items_unit FOREIGN KEY (default_unit_id) REFERENCES units(id);

-- Item Units (optional conversions)
CREATE TABLE IF NOT EXISTS item_units (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  item_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  factor_to_base DECIMAL(18,6) NOT NULL DEFAULT 1,
  is_base TINYINT(1) NOT NULL DEFAULT 0,
  barcode VARCHAR(64) NULL,
  UNIQUE KEY uq_item_unit (item_id, unit_id),
  CONSTRAINT fk_item_units_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
  CONSTRAINT fk_item_units_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Warehouse documents (Header)
CREATE TABLE IF NOT EXISTS wh_docs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  doc_no VARCHAR(30) NOT NULL UNIQUE,
  doc_date DATETIME NOT NULL,
  doc_type VARCHAR(20) NOT NULL, -- RECEIPT / ISSUE / TRANSFER / SCRAP / ADJUST
  status VARCHAR(20) NOT NULL DEFAULT 'POSTED', -- keep simple now
  warehouse_id BIGINT UNSIGNED NOT NULL,      -- from warehouse
  to_warehouse_id BIGINT UNSIGNED NULL,       -- to warehouse (transfer)
  notes VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_wh_docs_date (doc_date),
  CONSTRAINT fk_wh_docs_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  CONSTRAINT fk_wh_docs_to_wh FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Warehouse document lines
CREATE TABLE IF NOT EXISTS wh_doc_lines (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  doc_id BIGINT UNSIGNED NOT NULL,
  line_no INT NOT NULL DEFAULT 1,
  item_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  qty DECIMAL(14,3) NOT NULL,
  unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
  reason_code VARCHAR(30) NULL,
  notes VARCHAR(255) NULL,
  INDEX idx_wh_doc_lines_doc (doc_id),
  CONSTRAINT fk_wh_doc_lines_doc FOREIGN KEY (doc_id) REFERENCES wh_docs(id) ON DELETE CASCADE,
  CONSTRAINT fk_wh_doc_lines_item FOREIGN KEY (item_id) REFERENCES items(id),
  CONSTRAINT fk_wh_doc_lines_unit FOREIGN KEY (unit_id) REFERENCES units(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed common unit
INSERT INTO units(name, symbol)
SELECT 'قطعة', 'EA'
WHERE NOT EXISTS (SELECT 1 FROM units WHERE name='قطعة');

-- Ensure MAIN warehouse exists/is_main
UPDATE warehouses SET is_main=1 WHERE id=1;