-- Warehouses
CREATE TABLE warehouses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) UNIQUE,
  name VARCHAR(120),
  type ENUM('CENTRAL','SITE','YARD') DEFAULT 'SITE',
  address VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Materials
CREATE TABLE materials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(60) UNIQUE,
  name VARCHAR(180),
  uom VARCHAR(30),
  std_cost DECIMAL(12,4) DEFAULT 0
);

-- Inventory per warehouse
CREATE TABLE inventory_balances (
  warehouse_id INT,
  material_id INT,
  on_hand DECIMAL(14,4) DEFAULT 0,
  reserved DECIMAL(14,4) DEFAULT 0,
  PRIMARY KEY (warehouse_id, material_id)
);

-- FIFO layers
CREATE TABLE inventory_layers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  warehouse_id INT,
  material_id INT,
  qty_remaining DECIMAL(14,4),
  unit_cost DECIMAL(12,4),
  source_doc VARCHAR(40),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Movements
CREATE TABLE inventory_movements (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('GRN','ISSUE','TR','ADJ','RETURN') NOT NULL,
  project_id INT NULL,
  from_warehouse_id INT NULL,
  to_warehouse_id INT NULL,
  material_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  unit_cost DECIMAL(12,4) DEFAULT 0,
  total_cost DECIMAL(14,4) DEFAULT 0,
  doc_no VARCHAR(40),
  created_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- BOQ planned vs issued
CREATE TABLE boq_lines (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  project_id INT,
  material_id INT,
  planned_qty DECIMAL(14,4) DEFAULT 0,
  issued_qty DECIMAL(14,4) DEFAULT 0
);

-- Budgets
CREATE TABLE project_budgets (
  project_id INT PRIMARY KEY,
  planned_budget DECIMAL(14,2) DEFAULT 0
);

-- Roles
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120),
  role ENUM('ADMIN','STOREKEEPER','ENGINEER')
);