-- Kenyan Bakery MVP Schema (MySQL/MariaDB)
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,
  role VARCHAR(40) NOT NULL DEFAULT 'Admin',
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  phone VARCHAR(40) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('Raw','Packaging','Other') NOT NULL DEFAULT 'Raw',
  sku VARCHAR(50) NULL,
  name VARCHAR(190) NOT NULL,
  uom VARCHAR(20) NOT NULL DEFAULT 'kg',
  reorder_level DECIMAL(12,4) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_items_sku (sku)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stock_moves (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  move_time DATETIME NOT NULL,
  ref_type VARCHAR(30) NOT NULL,
  ref_id BIGINT NOT NULL,
  item_id INT NOT NULL,
  qty_in DECIMAL(14,4) NOT NULL DEFAULT 0,
  qty_out DECIMAL(14,4) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  KEY idx_stock_item (item_id),
  KEY idx_stock_ref (ref_type, ref_id),
  CONSTRAINT fk_stock_item FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchases (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  supplier_id INT NULL,
  invoice_no VARCHAR(80) NULL,
  purchase_date DATE NOT NULL,
  notes VARCHAR(255) NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_purchases_supplier (supplier_id),
  CONSTRAINT fk_purchases_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_purchases_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchase_lines (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  purchase_id BIGINT NOT NULL,
  item_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
  KEY idx_pl_purchase (purchase_id),
  CONSTRAINT fk_pl_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
  CONSTRAINT fk_pl_item FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category ENUM('Bread','Cake','Pastry','Other') NOT NULL DEFAULT 'Bread',
  sku VARCHAR(50) NULL,
  name VARCHAR(190) NOT NULL,
  uom VARCHAR(20) NOT NULL DEFAULT 'pcs',
  sell_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_products_sku (sku)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS recipes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  item_id INT NOT NULL,
  qty_per_unit DECIMAL(14,6) NOT NULL, -- qty of raw item per 1 finished unit
  UNIQUE KEY uq_recipe (product_id, item_id),
  CONSTRAINT fk_recipe_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_recipe_item FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS finished_moves (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  move_time DATETIME NOT NULL,
  ref_type VARCHAR(30) NOT NULL,
  ref_id BIGINT NOT NULL,
  product_id INT NOT NULL,
  qty_in DECIMAL(14,4) NOT NULL DEFAULT 0,
  qty_out DECIMAL(14,4) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  KEY idx_fin_product (product_id),
  KEY idx_fin_ref (ref_type, ref_id),
  CONSTRAINT fk_fin_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS production_batches (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  batch_date DATE NOT NULL,
  product_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
  total_cost DECIMAL(14,4) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_pb_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_pb_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  phone VARCHAR(40) NULL,
  credit_limit DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pos_sales (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  sale_time DATETIME NOT NULL,
  receipt_no VARCHAR(40) NOT NULL,
  customer_id INT NULL,
  payment_method ENUM('Cash','M-Pesa','Bank','Credit') NOT NULL DEFAULT 'Cash',
  mpesa_ref VARCHAR(40) NULL,
  subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
  vat_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  total DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_by INT NULL,
  KEY idx_pos_receipt (receipt_no),
  KEY idx_pos_customer (customer_id),
  CONSTRAINT fk_pos_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_pos_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pos_sale_lines (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  pos_sale_id BIGINT NOT NULL,
  product_id INT NOT NULL,
  qty DECIMAL(14,4) NOT NULL,
  unit_price DECIMAL(14,2) NOT NULL,
  line_total DECIMAL(14,2) NOT NULL,
  CONSTRAINT fk_psl_sale FOREIGN KEY (pos_sale_id) REFERENCES pos_sales(id) ON DELETE CASCADE,
  CONSTRAINT fk_psl_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS routes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trips (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  trip_date DATE NOT NULL,
  route_id INT NULL,
  vehicle VARCHAR(60) NULL,
  sales_rep VARCHAR(120) NULL,
  status ENUM('Open','Closed') NOT NULL DEFAULT 'Open',
  loaded_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  sold_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  returned_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  collected_total DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_trip_route FOREIGN KEY (route_id) REFERENCES routes(id),
  CONSTRAINT fk_trip_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trip_lines (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  trip_id BIGINT NOT NULL,
  product_id INT NOT NULL,
  loaded_qty DECIMAL(14,4) NOT NULL,
  sold_qty DECIMAL(14,4) NOT NULL DEFAULT 0,
  returned_qty DECIMAL(14,4) NOT NULL DEFAULT 0,
  unit_price DECIMAL(14,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_tl_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE CASCADE,
  CONSTRAINT fk_tl_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS collections (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  collected_at DATETIME NOT NULL,
  ref_type VARCHAR(20) NOT NULL,
  ref_id BIGINT NOT NULL,
  payment_method ENUM('Cash','M-Pesa','Bank') NOT NULL DEFAULT 'Cash',
  mpesa_ref VARCHAR(40) NULL,
  amount DECIMAL(14,2) NOT NULL,
  received_by INT NULL,
  notes VARCHAR(255) NULL,
  KEY idx_col_ref (ref_type, ref_id),
  CONSTRAINT fk_col_user FOREIGN KEY (received_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS expenses (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  expense_date DATE NOT NULL,
  category VARCHAR(60) NOT NULL,
  description VARCHAR(255) NULL,
  amount DECIMAL(14,2) NOT NULL,
  payment_method ENUM('Cash','M-Pesa','Bank') NOT NULL DEFAULT 'Cash',
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_exp_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- Seed default admin
