-- Basic schema for Distribex Drinks 360
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('ADMIN','CLERK','VAN') NOT NULL DEFAULT 'CLERK',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NOT NULL,
  brand VARCHAR(50),
  pack_size VARCHAR(50),
  container_type ENUM('PET','RGB','CAN') NOT NULL,
  has_deposit TINYINT(1) NOT NULL DEFAULT 0,
  price_per_case DECIMAL(12,2) NOT NULL DEFAULT 0,
  deposit_per_case DECIMAL(12,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE stock_locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  type ENUM('WAREHOUSE','VAN') NOT NULL
);

CREATE TABLE stock_balances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  location_id INT NOT NULL,
  qty_cases DECIMAL(12,3) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_prod_loc (product_id, location_id)
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50),
  route_id INT,
  town VARCHAR(100),
  phone VARCHAR(30),
  credit_limit DECIMAL(12,2) DEFAULT 0,
  deposit_balance DECIMAL(12,2) DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE routes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255)
);

CREATE TABLE load_sheets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  load_no VARCHAR(50) NOT NULL,
  load_date DATE NOT NULL,
  van_location_id INT NOT NULL,
  route_id INT,
  created_by INT NOT NULL,
  status ENUM('OPEN','CLOSED') DEFAULT 'OPEN'
);

CREATE TABLE load_sheet_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  load_id INT NOT NULL,
  product_id INT NOT NULL,
  qty_cases DECIMAL(12,3) NOT NULL DEFAULT 0
);

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(50) NOT NULL,
  invoice_date DATETIME NOT NULL,
  customer_id INT NOT NULL,
  load_id INT,
  van_location_id INT,
  created_by INT NOT NULL,
  payment_term ENUM('CASH','CREDIT') DEFAULT 'CASH',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_deposit DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  product_id INT NOT NULL,
  qty_cases DECIMAL(12,3) NOT NULL,
  price_per_case DECIMAL(12,2) NOT NULL,
  deposit_per_case DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12,2) NOT NULL,
  deposit_total DECIMAL(12,2) NOT NULL
);
