-- PolyOps MVP (Films + Printing + Bags) — MySQL/MariaDB
-- Tested on MySQL 5.7+/MariaDB 10.3+

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','stores','production','accounts') NOT NULL DEFAULT 'admin',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  phone VARCHAR(50) DEFAULT NULL,
  email VARCHAR(190) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS materials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(190) NOT NULL,
  uom VARCHAR(20) NOT NULL DEFAULT 'kg',
  category ENUM('resin','masterbatch','ink','solvent','additive','other') NOT NULL DEFAULT 'other',
  avg_cost DECIMAL(12,4) NOT NULL DEFAULT 0.0000, -- weighted average cost
  qty_on_hand DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stock_ledger (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  material_id INT NOT NULL,
  ref_type ENUM('GRN','ISSUE','ADJ') NOT NULL,
  ref_id BIGINT NOT NULL,
  txn_date DATE NOT NULL,
  qty_in DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  qty_out DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  unit_cost DECIMAL(12,4) NOT NULL DEFAULT 0.0000, -- cost per unit for this movement
  note VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (material_id),
  INDEX (ref_type, ref_id),
  CONSTRAINT fk_ledger_material FOREIGN KEY (material_id) REFERENCES materials(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS grn (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  grn_no VARCHAR(50) NOT NULL UNIQUE,
  supplier_id INT DEFAULT NULL,
  received_date DATE NOT NULL,
  notes TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (supplier_id),
  CONSTRAINT fk_grn_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_grn_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS grn_lines (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  grn_id BIGINT NOT NULL,
  material_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  unit_cost DECIMAL(12,4) NOT NULL,
  line_total DECIMAL(14,4) NOT NULL,
  INDEX (grn_id),
  INDEX (material_id),
  CONSTRAINT fk_grn_lines_grn FOREIGN KEY (grn_id) REFERENCES grn(id) ON DELETE CASCADE,
  CONSTRAINT fk_grn_lines_material FOREIGN KEY (material_id) REFERENCES materials(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS issues (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  issue_no VARCHAR(50) NOT NULL UNIQUE,
  issue_date DATE NOT NULL,
  stage ENUM('film','printing','bag') NOT NULL,
  batch_code VARCHAR(50) DEFAULT NULL, -- optional link to batch
  notes TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_issues_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS issue_lines (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  issue_id BIGINT NOT NULL,
  material_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  unit_cost DECIMAL(12,4) NOT NULL, -- captured avg_cost at time of issue
  line_total DECIMAL(14,4) NOT NULL,
  CONSTRAINT fk_issue_lines_issue FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE,
  CONSTRAINT fk_issue_lines_material FOREIGN KEY (material_id) REFERENCES materials(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS batches (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  batch_code VARCHAR(50) NOT NULL UNIQUE,
  stage ENUM('film','printing','bag') NOT NULL,
  batch_date DATE NOT NULL,
  machine VARCHAR(80) DEFAULT NULL,
  shift ENUM('day','night','other') DEFAULT 'day',
  -- inputs/outputs (simple MVP)
  input_qty DECIMAL(14,4) NOT NULL DEFAULT 0.0000,  -- kg of rolls/resin issued or roll input
  output_qty DECIMAL(14,4) NOT NULL DEFAULT 0.0000, -- kg rolls out OR number of bags out (for bag stage we store bags as output_qty)
  scrap_qty DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  notes TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_batches_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS batch_costs_cache (
  batch_id BIGINT PRIMARY KEY,
  material_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  total_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  cost_per_unit DECIMAL(14,6) NOT NULL DEFAULT 0.000000, -- per kg for film/printing, per bag for bag stage (based on output_qty)
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_batch_costs_batch FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE CASCADE
) ENGINE=InnoDB;
