CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(120) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'staff',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  species VARCHAR(20) NOT NULL DEFAULT 'AYAM',
  part_name VARCHAR(80) NOT NULL DEFAULT '',
  condition_name VARCHAR(20) NOT NULL DEFAULT 'FRESH',
  raw_unit VARCHAR(20) NOT NULL DEFAULT 'KARUNG',
  ready_unit VARCHAR(20) NOT NULL DEFAULT 'DUS',
  normal_shrinkage_percent DECIMAL(8,3) NOT NULL DEFAULT 2.000,
  target_margin_percent DECIMAL(8,3) NOT NULL DEFAULT 10.000,
  shelf_life_days INT NOT NULL DEFAULT 3,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS partners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  partner_type VARCHAR(20) NOT NULL,
  market_type VARCHAR(80) NOT NULL DEFAULT '',
  phone VARCHAR(60) NOT NULL DEFAULT '',
  address TEXT NULL,
  payment_terms_days INT NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS warehouses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  warehouse_id INT NOT NULL,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(120) NOT NULL,
  function_type VARCHAR(30) NOT NULL DEFAULT 'STORAGE',
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_location (warehouse_id, code),
  CONSTRAINT fk_location_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cost_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  basis VARCHAR(30) NOT NULL DEFAULT 'FIXED',
  default_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(80) NOT NULL UNIQUE,
  order_type VARCHAR(30) NOT NULL,
  partner_id INT NOT NULL,
  external_po_no VARCHAR(100) NOT NULL DEFAULT '',
  order_date DATE NOT NULL,
  delivery_date DATE NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'OPEN',
  notes TEXT NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_orders_type_status (order_type, status),
  INDEX idx_orders_delivery_date (delivery_date),
  CONSTRAINT fk_orders_partner FOREIGN KEY (partner_id) REFERENCES partners(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  line_no INT NOT NULL,
  product_id INT NOT NULL,
  ordered_weight_kg DECIMAL(14,3) NOT NULL,
  price_per_kg DECIMAL(18,2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NOT NULL DEFAULT '',
  UNIQUE KEY uq_order_line (order_id, line_no),
  INDEX idx_order_items_product (product_id),
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_allocations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_order_item_id BIGINT NOT NULL,
  supplier_order_item_id BIGINT NOT NULL,
  allocated_weight_kg DECIMAL(14,3) NOT NULL,
  notes VARCHAR(255) NOT NULL DEFAULT '',
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_allocation_pair (customer_order_item_id, supplier_order_item_id),
  CONSTRAINT fk_alloc_customer_item FOREIGN KEY (customer_order_item_id) REFERENCES order_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_alloc_supplier_item FOREIGN KEY (supplier_order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS batches (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  batch_no VARCHAR(90) NOT NULL UNIQUE,
  product_id INT NOT NULL,
  supplier_id INT NOT NULL,
  received_at DATETIME NOT NULL,
  production_date DATE NULL,
  expiry_date DATE NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_batches_product (product_id),
  INDEX idx_batches_expiry (expiry_date),
  CONSTRAINT fk_batches_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_batches_supplier FOREIGN KEY (supplier_id) REFERENCES partners(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS receipts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  reference_no VARCHAR(80) NOT NULL UNIQUE,
  supplier_id INT NOT NULL,
  warehouse_id INT NOT NULL,
  location_id INT NOT NULL,
  received_at DATETIME NOT NULL,
  delivery_note VARCHAR(100) NOT NULL DEFAULT '',
  notes TEXT NULL,
  user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_receipts_supplier FOREIGN KEY (supplier_id) REFERENCES partners(id),
  CONSTRAINT fk_receipts_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  CONSTRAINT fk_receipts_location FOREIGN KEY (location_id) REFERENCES locations(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS receipt_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  receipt_id BIGINT NOT NULL,
  supplier_order_item_id BIGINT NULL,
  product_id INT NOT NULL,
  batch_id BIGINT NOT NULL,
  raw_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  gross_weight DECIMAL(14,3) NOT NULL,
  tare_weight DECIMAL(14,3) NOT NULL,
  net_weight DECIMAL(14,3) NOT NULL,
  supplier_weight DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_receipt_items_receipt FOREIGN KEY (receipt_id) REFERENCES receipts(id) ON DELETE CASCADE,
  CONSTRAINT fk_receipt_items_supplier_order FOREIGN KEY (supplier_order_item_id) REFERENCES order_items(id),
  CONSTRAINT fk_receipt_items_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_receipt_items_batch FOREIGN KEY (batch_id) REFERENCES batches(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_balances (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  batch_id BIGINT NOT NULL,
  warehouse_id INT NOT NULL,
  location_id INT NOT NULL,
  stock_stage VARCHAR(20) NOT NULL,
  qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  weight_kg DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_inventory_balance (product_id, batch_id, warehouse_id, location_id, stock_stage),
  INDEX idx_balance_stage (stock_stage),
  CONSTRAINT fk_balance_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_balance_batch FOREIGN KEY (batch_id) REFERENCES batches(id),
  CONSTRAINT fk_balance_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  CONSTRAINT fk_balance_location FOREIGN KEY (location_id) REFERENCES locations(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS packing_runs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  reference_no VARCHAR(80) NOT NULL UNIQUE,
  product_id INT NOT NULL,
  batch_id BIGINT NOT NULL,
  from_warehouse_id INT NOT NULL,
  from_location_id INT NOT NULL,
  to_warehouse_id INT NOT NULL,
  to_location_id INT NOT NULL,
  input_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  input_weight DECIMAL(14,3) NOT NULL,
  output_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  output_weight DECIMAL(14,3) NOT NULL,
  shrinkage_weight DECIMAL(14,3) NOT NULL,
  shrinkage_percent DECIMAL(8,3) NOT NULL,
  raw_unit_cost DECIMAL(18,2) NOT NULL,
  raw_cost DECIMAL(18,2) NOT NULL,
  extra_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
  total_cost DECIMAL(18,2) NOT NULL,
  output_unit_cost DECIMAL(18,2) NOT NULL,
  notes TEXT NULL,
  user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_packing_date (created_at),
  CONSTRAINT fk_packing_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_packing_batch FOREIGN KEY (batch_id) REFERENCES batches(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS packing_cost_lines (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  packing_run_id BIGINT NOT NULL,
  cost_type_id INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  notes VARCHAR(255) NOT NULL DEFAULT '',
  CONSTRAINT fk_pack_cost_run FOREIGN KEY (packing_run_id) REFERENCES packing_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_pack_cost_type FOREIGN KEY (cost_type_id) REFERENCES cost_types(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS deliveries (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  reference_no VARCHAR(80) NOT NULL UNIQUE,
  customer_order_item_id BIGINT NOT NULL,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  batch_id BIGINT NOT NULL,
  warehouse_id INT NOT NULL,
  location_id INT NOT NULL,
  qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  dispatch_weight DECIMAL(14,3) NOT NULL,
  accepted_weight DECIMAL(14,3) NOT NULL,
  sale_price_per_kg DECIMAL(18,2) NOT NULL,
  cogs_unit_cost DECIMAL(18,2) NOT NULL,
  extra_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
  revenue DECIMAL(18,2) NOT NULL,
  cogs DECIMAL(18,2) NOT NULL,
  margin_amount DECIMAL(18,2) NOT NULL,
  margin_percent DECIMAL(8,3) NOT NULL,
  notes TEXT NULL,
  user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_delivery_date (created_at),
  CONSTRAINT fk_delivery_order_item FOREIGN KEY (customer_order_item_id) REFERENCES order_items(id),
  CONSTRAINT fk_delivery_customer FOREIGN KEY (customer_id) REFERENCES partners(id),
  CONSTRAINT fk_delivery_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_delivery_batch FOREIGN KEY (batch_id) REFERENCES batches(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stock_opnames (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  reference_no VARCHAR(80) NOT NULL UNIQUE,
  product_id INT NOT NULL,
  batch_id BIGINT NOT NULL,
  warehouse_id INT NOT NULL,
  location_id INT NOT NULL,
  stock_stage VARCHAR(20) NOT NULL,
  system_qty DECIMAL(14,3) NOT NULL,
  actual_qty DECIMAL(14,3) NOT NULL,
  system_weight DECIMAL(14,3) NOT NULL,
  actual_weight DECIMAL(14,3) NOT NULL,
  diff_qty DECIMAL(14,3) NOT NULL,
  diff_weight DECIMAL(14,3) NOT NULL,
  normal_shrinkage DECIMAL(14,3) NOT NULL DEFAULT 0,
  abnormal_loss DECIMAL(14,3) NOT NULL DEFAULT 0,
  tolerance_percent DECIMAL(8,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
  loss_value DECIMAL(18,2) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_ledger (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  transaction_date DATETIME NOT NULL,
  transaction_type VARCHAR(40) NOT NULL,
  reference_no VARCHAR(80) NOT NULL,
  product_id INT NOT NULL,
  batch_id BIGINT NOT NULL,
  warehouse_id INT NOT NULL,
  location_id INT NOT NULL,
  stock_stage VARCHAR(20) NOT NULL,
  qty_in DECIMAL(14,3) NOT NULL DEFAULT 0,
  qty_out DECIMAL(14,3) NOT NULL DEFAULT 0,
  weight_in DECIMAL(14,3) NOT NULL DEFAULT 0,
  weight_out DECIMAL(14,3) NOT NULL DEFAULT 0,
  balance_qty DECIMAL(14,3) NOT NULL DEFAULT 0,
  balance_weight DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
  movement_value DECIMAL(18,2) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  user_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ledger_date (transaction_date),
  INDEX idx_ledger_product_batch (product_id, batch_id),
  INDEX idx_ledger_ref (reference_no)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  module VARCHAR(60) NOT NULL,
  action VARCHAR(60) NOT NULL,
  reference_no VARCHAR(80) NOT NULL DEFAULT '',
  old_value TEXT NULL,
  new_value TEXT NULL,
  ip_address VARCHAR(64) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_date (created_at)
) ENGINE=InnoDB;