
-- WSC Logistics schema (USD invoicing per HBL, W/M costing)
CREATE DATABASE IF NOT EXISTS wsc_logistics CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wsc_logistics;

CREATE TABLE IF NOT EXISTS sequences (
  name VARCHAR(50) PRIMARY KEY,
  current_value INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(120) UNIQUE,
  phone VARCHAR(30),
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS shipping_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS ports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  country VARCHAR(80),
  unlocode VARCHAR(10),
  UNIQUE KEY uniq_port (name, country)
);

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  contact_person VARCHAR(120),
  email VARCHAR(120),
  phone VARCHAR(30),
  address VARCHAR(255),
  kra_pin VARCHAR(30),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS master_bls (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  mbl_no VARCHAR(60) NOT NULL UNIQUE,
  shipping_line_id INT NOT NULL,
  pol_port_id INT NOT NULL,
  pod_port_id INT NOT NULL,
  vessel_name VARCHAR(120),
  voyage_no VARCHAR(60),
  eta_date DATE,
  status ENUM('RECEIVED','BREAKDOWN_IN_PROGRESS','BREAKDOWN_DONE','CLEARING','RELEASED','CLOSED')
    NOT NULL DEFAULT 'RECEIVED',
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shipping_line_id) REFERENCES shipping_lines(id),
  FOREIGN KEY (pol_port_id) REFERENCES ports(id),
  FOREIGN KEY (pod_port_id) REFERENCES ports(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS house_bls (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  master_bl_id BIGINT NOT NULL,
  hbl_no VARCHAR(60) NOT NULL UNIQUE,

  consignee_customer_id INT NOT NULL,
  billing_party_customer_id INT NOT NULL,

  notify_party_customer_id INT NULL,
  goods_description TEXT,
  packages INT DEFAULT 0,

  gross_weight_kg DECIMAL(12,3) NOT NULL DEFAULT 0,
  cbm DECIMAL(12,3) NOT NULL DEFAULT 0,

  chargeable_cbm INT NOT NULL DEFAULT 0,                 -- CEIL(cbm)
  chargeable_weight_ton DECIMAL(12,3) NOT NULL DEFAULT 0, -- kg/1000
  chargeable_units DECIMAL(12,3) NOT NULL DEFAULT 0,      -- max(weight_ton, chargeable_cbm)

  clearing_status ENUM('PENDING','DOCS_SUBMITTED','ASSESSED','DUTY_PAID','RELEASED','DELIVERED')
    NOT NULL DEFAULT 'PENDING',

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (master_bl_id) REFERENCES master_bls(id),
  FOREIGN KEY (consignee_customer_id) REFERENCES customers(id),
  FOREIGN KEY (billing_party_customer_id) REFERENCES customers(id),
  FOREIGN KEY (notify_party_customer_id) REFERENCES customers(id)
);

CREATE TABLE IF NOT EXISTS charge_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  basis ENUM('FLAT','PER_UNIT_WM') NOT NULL DEFAULT 'FLAT',
  default_rate DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS invoices (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(40) NOT NULL UNIQUE,
  house_bl_id BIGINT NOT NULL,
  consignee_customer_id INT NOT NULL,
  billing_party_customer_id INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  status ENUM('DRAFT','ISSUED','PART_PAID','PAID','CANCELLED') NOT NULL DEFAULT 'ISSUED',
  issue_date DATE NOT NULL,
  due_date DATE NULL,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  total DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (house_bl_id) REFERENCES house_bls(id),
  FOREIGN KEY (consignee_customer_id) REFERENCES customers(id),
  FOREIGN KEY (billing_party_customer_id) REFERENCES customers(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS invoice_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT NOT NULL,
  charge_type_id INT NOT NULL,
  description VARCHAR(255),
  basis ENUM('FLAT','PER_UNIT_WM') NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (charge_type_id) REFERENCES charge_types(id)
);

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT NOT NULL,
  pay_date DATE NOT NULL,
  method ENUM('CASH','BANK','MPESA','CHEQUE','OTHER') NOT NULL,
  ref_no VARCHAR(80),
  amount DECIMAL(14,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);

CREATE TABLE IF NOT EXISTS delivery_orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  do_no VARCHAR(40) NOT NULL UNIQUE,
  house_bl_id BIGINT NOT NULL,
  status ENUM('DRAFT','ISSUED','RELEASED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
  issued_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (house_bl_id) REFERENCES house_bls(id)
);

-- Seed: one admin user (password: ChangeMe123!)
INSERT INTO users(full_name, email, phone, password_hash, is_active)
VALUES ('WSC Admin', 'admin@wsc.local', '0700000000', '$2b$12$ZkQ.XT8.ioeg17b4j7jPROajZtPZPqvHxhZ2yNqJv554SylPc2r5m', 1)
ON DUPLICATE KEY UPDATE email=email;

-- Seed: common charge lines (basis and rates per your spec)
INSERT INTO charge_types(name, basis, default_rate) VALUES
('LCL FEES', 'PER_UNIT_WM', 26.00),
('DESTUFFING CHARGES', 'PER_UNIT_WM', 25.00),
('WAR SURCHARGE', 'PER_UNIT_WM', 35.00),
('BAF', 'PER_UNIT_WM', 35.00),
('LSS', 'PER_UNIT_WM', 5.00),
('PSS', 'PER_UNIT_WM', 30.00),
('WRS', 'PER_UNIT_WM', 30.00),
('ADMINISTRATION FEES', 'FLAT', 30.00),
('DOCUMENTATION FEES', 'FLAT', 70.00),
('DOCUMENT HANDOVER FEES', 'FLAT', 80.00),
('HANDLING CHARGES', 'FLAT', 85.00),
('DEGROUP FEES', 'FLAT', 30.00),
('MANIFEST FILING FEES', 'FLAT', 30.00),
('TERMINAL TRANSFER FEES', 'FLAT', 30.00)
ON DUPLICATE KEY UPDATE name=name;
