-- WholesaleOps MVP (Single Shop) schema + Phase 2 upgrades
-- Import this file into a NEW database for clean setup.

SET NAMES utf8mb4;
SET time_zone = "+03:00";

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  sku VARCHAR(80) NULL,
  barcode VARCHAR(80) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_item_name (name),
  KEY idx_barcode (barcode)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS item_uoms (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  uom_name VARCHAR(60) NOT NULL,
  conversion_qty DECIMAL(12,3) NOT NULL DEFAULT 1.000,
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_item (item_id),
  CONSTRAINT fk_uom_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
  UNIQUE KEY uq_item_uom (item_id, uom_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  phone VARCHAR(60) NULL,
  credit_limit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  opening_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customer_name (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shifts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  opened_at DATETIME NOT NULL,
  closed_at DATETIME NULL,
  opened_by INT NOT NULL,
  opening_cash DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  closing_cash DECIMAL(12,2) NULL,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_opened (opened_by, opened_at),
  CONSTRAINT fk_shift_user FOREIGN KEY (opened_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  shift_id INT NULL,
  invoice_no VARCHAR(40) NOT NULL,
  invoice_date DATE NOT NULL,
  total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(20) NOT NULL DEFAULT 'Unpaid',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_customer_date (customer_id, invoice_date),
  UNIQUE KEY uq_invoice_no (invoice_no),
  CONSTRAINT fk_invoice_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_invoice_shift FOREIGN KEY (shift_id) REFERENCES shifts(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  item_id INT NOT NULL,
  uom_id INT NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 1.000,
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  cost_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  profit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  KEY idx_invoice (invoice_id),
  CONSTRAINT fk_li_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_li_item FOREIGN KEY (item_id) REFERENCES items(id),
  CONSTRAINT fk_li_uom FOREIGN KEY (uom_id) REFERENCES item_uoms(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  payment_date DATE NOT NULL,
  method VARCHAR(30) NOT NULL DEFAULT 'Cash',
  reference VARCHAR(80) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pay_customer_date (customer_id, payment_date),
  CONSTRAINT fk_pay_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payment_allocations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payment_id INT NOT NULL,
  invoice_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pa_payment (payment_id),
  KEY idx_pa_invoice (invoice_id),
  CONSTRAINT fk_pa_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

/* Stock (single shop) */
CREATE TABLE IF NOT EXISTS stock_onhand (
  item_id INT PRIMARY KEY,
  qty_base DECIMAL(14,3) NOT NULL DEFAULT 0.000,
  avg_cost_base DECIMAL(12,6) NOT NULL DEFAULT 0.000000,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_onhand_item FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stock_moves (
  id INT AUTO_INCREMENT PRIMARY KEY,
  move_date DATETIME NOT NULL,
  move_type VARCHAR(20) NOT NULL,
  ref_type VARCHAR(30) NOT NULL,
  ref_id INT NOT NULL,
  item_id INT NOT NULL,
  qty_base DECIMAL(14,3) NOT NULL,
  cost_base DECIMAL(12,6) NOT NULL DEFAULT 0.000000,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_item_date (item_id, move_date),
  KEY idx_ref (ref_type, ref_id),
  CONSTRAINT fk_move_item FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

/* GRN */
CREATE TABLE IF NOT EXISTS grns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  grn_no VARCHAR(40) NOT NULL,
  grn_date DATE NOT NULL,
  supplier VARCHAR(160) NULL,
  notes VARCHAR(255) NULL,
  created_by INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_grn_no (grn_no),
  CONSTRAINT fk_grn_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS grn_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  grn_id INT NOT NULL,
  item_id INT NOT NULL,
  uom_id INT NOT NULL,
  qty DECIMAL(14,3) NOT NULL DEFAULT 0.000,
  unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_grn (grn_id),
  CONSTRAINT fk_grni_grn FOREIGN KEY (grn_id) REFERENCES grns(id) ON DELETE CASCADE,
  CONSTRAINT fk_grni_item FOREIGN KEY (item_id) REFERENCES items(id),
  CONSTRAINT fk_grni_uom FOREIGN KEY (uom_id) REFERENCES item_uoms(id)
) ENGINE=InnoDB;

/* Returns */
CREATE TABLE IF NOT EXISTS returns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  return_no VARCHAR(40) NOT NULL,
  return_date DATE NOT NULL,
  customer_id INT NOT NULL,
  invoice_id INT NULL,
  method VARCHAR(30) NOT NULL DEFAULT 'Credit',
  total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  notes VARCHAR(255) NULL,
  created_by INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_return_no (return_no),
  KEY idx_return_customer (customer_id, return_date),
  CONSTRAINT fk_ret_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_ret_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id),
  CONSTRAINT fk_ret_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS return_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  return_id INT NOT NULL,
  item_id INT NOT NULL,
  uom_id INT NOT NULL,
  qty DECIMAL(14,3) NOT NULL DEFAULT 0.000,
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  cost_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reti_ret FOREIGN KEY (return_id) REFERENCES returns(id) ON DELETE CASCADE,
  CONSTRAINT fk_reti_item FOREIGN KEY (item_id) REFERENCES items(id),
  CONSTRAINT fk_reti_uom FOREIGN KEY (uom_id) REFERENCES item_uoms(id)
) ENGINE=InnoDB;
