CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    type ENUM('warehouse','store','van_hub') NOT NULL DEFAULT 'store',
    location VARCHAR(190) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('owner','cashier','sales_rep','storekeeper','accounts','supervisor') NOT NULL,
    branch_id INT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
);
CREATE TABLE routes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    route_code VARCHAR(40) NOT NULL UNIQUE,
    route_name VARCHAR(120) NOT NULL,
    sales_rep_id INT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_routes_sales_rep FOREIGN KEY (sales_rep_id) REFERENCES users(id)
);
CREATE TABLE vans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    van_code VARCHAR(40) NOT NULL UNIQUE,
    van_name VARCHAR(120) NOT NULL,
    reg_no VARCHAR(50) NULL,
    assigned_rep_id INT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_vans_assigned_rep FOREIGN KEY (assigned_rep_id) REFERENCES users(id)
);
CREATE TABLE price_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(40) NOT NULL UNIQUE,
    customer_type ENUM('walkin','business') NOT NULL DEFAULT 'business',
    name VARCHAR(190) NOT NULL,
    contact_person VARCHAR(120) NULL,
    phone VARCHAR(30) NULL,
    route_id INT NULL,
    location VARCHAR(190) NULL,
    credit_limit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    price_group_id INT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_customers_route FOREIGN KEY (route_id) REFERENCES routes(id),
    CONSTRAINT fk_customers_price_group FOREIGN KEY (price_group_id) REFERENCES price_groups(id)
);
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL UNIQUE,
    active TINYINT(1) NOT NULL DEFAULT 1
);
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    barcode VARCHAR(80) NULL,
    item_name VARCHAR(190) NOT NULL,
    category_id INT NULL,
    brand VARCHAR(120) NULL,
    description TEXT NULL,
    image_path VARCHAR(255) NULL,
    base_unit VARCHAR(20) NOT NULL DEFAULT 'pcs',
    tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_items_category FOREIGN KEY (category_id) REFERENCES categories(id)
);
CREATE TABLE item_units (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    unit_name VARCHAR(50) NOT NULL,
    unit_code VARCHAR(20) NOT NULL,
    conversion_to_base DECIMAL(12,3) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_item_unit (item_id, unit_code),
    CONSTRAINT fk_item_units_item FOREIGN KEY (item_id) REFERENCES items(id)
);
CREATE TABLE item_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    unit_code VARCHAR(20) NOT NULL,
    price_group_id INT NULL,
    selling_price DECIMAL(14,2) NOT NULL,
    min_price DECIMAL(14,2) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_item_price (item_id, unit_code, price_group_id),
    CONSTRAINT fk_item_prices_item FOREIGN KEY (item_id) REFERENCES items(id),
    CONSTRAINT fk_item_prices_group FOREIGN KEY (price_group_id) REFERENCES price_groups(id)
);
CREATE TABLE stock_balances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    location_type ENUM('warehouse','store','van') NOT NULL,
    location_id INT NOT NULL,
    qty_base DECIMAL(14,3) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_stock_balance (item_id, location_type, location_id),
    CONSTRAINT fk_stock_balances_item FOREIGN KEY (item_id) REFERENCES items(id)
);
CREATE TABLE stock_ledger (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    location_type ENUM('warehouse','store','van') NOT NULL,
    location_id INT NOT NULL,
    txn_type VARCHAR(50) NOT NULL,
    reference_type VARCHAR(50) NULL,
    reference_id INT NULL,
    qty_in_base DECIMAL(14,3) NOT NULL DEFAULT 0,
    qty_out_base DECIMAL(14,3) NOT NULL DEFAULT 0,
    unit_cost DECIMAL(14,2) NULL,
    created_by INT NULL,
    reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stock_ledger_item_date (item_id, created_at),
    CONSTRAINT fk_stock_ledger_item FOREIGN KEY (item_id) REFERENCES items(id),
    CONSTRAINT fk_stock_ledger_user FOREIGN KEY (created_by) REFERENCES users(id)
);
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_no VARCHAR(40) NOT NULL UNIQUE,
    sale_type ENUM('walkin','route_sale','order_invoice') NOT NULL,
    customer_id INT NULL,
    route_id INT NULL,
    rep_id INT NULL,
    location_id INT NULL,
    subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
    discount_total DECIMAL(14,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(14,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
    payment_status ENUM('unpaid','part_paid','paid') NOT NULL DEFAULT 'unpaid',
    status ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'posted',
    sold_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sales_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_sales_route FOREIGN KEY (route_id) REFERENCES routes(id),
    CONSTRAINT fk_sales_rep FOREIGN KEY (rep_id) REFERENCES users(id)
);
CREATE TABLE sale_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    item_id INT NOT NULL,
    unit_code VARCHAR(20) NOT NULL,
    qty DECIMAL(14,3) NOT NULL,
    conversion_to_base DECIMAL(12,3) NOT NULL,
    qty_base DECIMAL(14,3) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL,
    discount DECIMAL(14,2) NOT NULL DEFAULT 0,
    line_total DECIMAL(14,2) NOT NULL,
    CONSTRAINT fk_sale_lines_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
    CONSTRAINT fk_sale_lines_item FOREIGN KEY (item_id) REFERENCES items(id)
);
CREATE TABLE sales_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(40) NOT NULL UNIQUE,
    customer_id INT NULL,
    route_id INT NULL,
    rep_id INT NULL,
    status ENUM('draft','confirmed','packed','dispatched','delivered','cancelled') NOT NULL DEFAULT 'draft',
    order_date DATE NOT NULL,
    delivery_date DATE NULL,
    subtotal DECIMAL(14,2) NOT NULL DEFAULT 0,
    discount_total DECIMAL(14,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(14,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(14,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_orders_route FOREIGN KEY (route_id) REFERENCES routes(id),
    CONSTRAINT fk_orders_rep FOREIGN KEY (rep_id) REFERENCES users(id)
);
CREATE TABLE sales_order_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    unit_code VARCHAR(20) NOT NULL,
    qty DECIMAL(14,3) NOT NULL,
    conversion_to_base DECIMAL(12,3) NOT NULL,
    qty_base DECIMAL(14,3) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL,
    line_total DECIMAL(14,2) NOT NULL,
    CONSTRAINT fk_order_lines_order FOREIGN KEY (order_id) REFERENCES sales_orders(id),
    CONSTRAINT fk_order_lines_item FOREIGN KEY (item_id) REFERENCES items(id)
);
CREATE TABLE dispatches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dispatch_no VARCHAR(40) NOT NULL UNIQUE,
    order_id INT NOT NULL,
    from_location_id INT NULL,
    route_id INT NULL,
    vehicle_name VARCHAR(120) NULL,
    status ENUM('pending','picked','dispatched','delivered','returned') NOT NULL DEFAULT 'pending',
    dispatched_at DATETIME NULL,
    delivered_at DATETIME NULL,
    CONSTRAINT fk_dispatches_order FOREIGN KEY (order_id) REFERENCES sales_orders(id),
    CONSTRAINT fk_dispatches_route FOREIGN KEY (route_id) REFERENCES routes(id)
);
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_no VARCHAR(40) NOT NULL UNIQUE,
    customer_id INT NULL,
    sale_id INT NULL,
    order_id INT NULL,
    method ENUM('cash','mpesa','bank','cheque','mixed') NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    reference_no VARCHAR(120) NULL,
    received_by INT NULL,
    received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payments_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_payments_sale FOREIGN KEY (sale_id) REFERENCES sales(id),
    CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES sales_orders(id),
    CONSTRAINT fk_payments_user FOREIGN KEY (received_by) REFERENCES users(id)
);
CREATE TABLE van_loads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    load_no VARCHAR(40) NOT NULL UNIQUE,
    van_id INT NOT NULL,
    route_id INT NULL,
    loaded_by INT NULL,
    load_date DATE NOT NULL,
    status ENUM('draft','posted','closed') NOT NULL DEFAULT 'draft',
    CONSTRAINT fk_van_loads_van FOREIGN KEY (van_id) REFERENCES vans(id),
    CONSTRAINT fk_van_loads_route FOREIGN KEY (route_id) REFERENCES routes(id),
    CONSTRAINT fk_van_loads_user FOREIGN KEY (loaded_by) REFERENCES users(id)
);
CREATE TABLE van_load_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    load_id INT NOT NULL,
    item_id INT NOT NULL,
    qty_base DECIMAL(14,3) NOT NULL,
    CONSTRAINT fk_van_load_lines_load FOREIGN KEY (load_id) REFERENCES van_loads(id),
    CONSTRAINT fk_van_load_lines_item FOREIGN KEY (item_id) REFERENCES items(id)
);