-- Canonical schema for the Vicolas Group POS (supermarket).
-- Run this once on a fresh MySQL instance.

CREATE DATABASE IF NOT EXISTS food_dashboard;
USE food_dashboard;

-- Pending registrations awaiting email-OTP confirmation.
-- Row is created when a buyer requests an OTP and removed when they confirm it.
CREATE TABLE IF NOT EXISTS register_otps (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(150) UNIQUE NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    owner_otp_hash VARCHAR(255),
    payload JSON NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    owner_expires_at TIMESTAMP NULL,
    user_verified_at TIMESTAMP NULL,
    attempts INT NOT NULL DEFAULT 0,
    owner_attempts INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Business profile (multi-tenant). One row per registered business. `code` is
-- the short identifier (e.g. HASHTG) that cashiers type in front of their PIN.
CREATE TABLE IF NOT EXISTS business (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(12) UNIQUE,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(50),
    email VARCHAR(150),
    address TEXT,
    logo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Users (auth + role + cashier PIN)
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    password VARCHAR(255) NOT NULL,
    pin_hash VARCHAR(255),
    role ENUM('admin', 'cashier') NOT NULL,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Staff profile (1:1 with a user)
-- Staff. Cashiers live ONLY here (they sign in with a PIN that lives on this row).
-- The optional user_id link is used for admins, who also need a row in `users` so
-- they can sign in by email + password.
CREATE TABLE IF NOT EXISTS staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(50),
    age INT,
    title VARCHAR(50),
    email VARCHAR(100),
    image VARCHAR(255),
    pin_hash VARCHAR(255),
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Products on the shelves
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE,
    barcode VARCHAR(50) UNIQUE,
    name VARCHAR(150) NOT NULL,
    brand VARCHAR(100),
    category VARCHAR(50) DEFAULT 'General',
    image VARCHAR(255),
    unit_of_measure VARCHAR(10) DEFAULT 'piece',  -- piece | pack | kg | g | l | ml | dozen
    current_price DECIMAL(10, 2) NOT NULL,
    cost_price DECIMAL(10, 2),
    supplied_quantity DECIMAL(12, 3) NOT NULL,
    available_quantity DECIMAL(12, 3) NOT NULL,
    low_stock_threshold DECIMAL(12, 3),
    expiry_date DATE,
    status ENUM('active','pending','rejected') NOT NULL DEFAULT 'active',
    created_by INT,
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Price change audit
CREATE TABLE IF NOT EXISTS price_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    price DECIMAL(10, 2) NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Transaction line items. order_id groups lines that were rung up together.
CREATE TABLE IF NOT EXISTS transactions (
    id VARCHAR(20) PRIMARY KEY,
    order_id VARCHAR(20) NOT NULL,
    staff_id INT,
    shift_id INT,
    product_id INT,
    quantity DECIMAL(12, 3) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    is_return TINYINT(1) DEFAULT 0,
    is_void TINYINT(1) DEFAULT 0,
    original_transaction_id VARCHAR(20),
    customer_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Cashier shifts: opening float + variance against actual cash sales
CREATE TABLE IF NOT EXISTS shifts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    staff_id INT NOT NULL,
    opening_float DECIMAL(10, 2) NOT NULL DEFAULT 0,
    expected_cash DECIMAL(10, 2),
    counted_cash DECIMAL(10, 2),
    variance DECIMAL(10, 2),
    notes TEXT,
    opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- Payments: one row per tender. An order may have multiple (split payments).
CREATE TABLE IF NOT EXISTS payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id VARCHAR(20) NOT NULL,
    method ENUM('cash','card','transfer','credit') NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    reference VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Daily rollup of sales per product
CREATE TABLE IF NOT EXISTS sales_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity_sold DECIMAL(12, 3) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    date DATE NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id),
    UNIQUE KEY uniq_item_day (product_id, date)
);

-- Stock replenishment audit
CREATE TABLE IF NOT EXISTS supply_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity_added DECIMAL(12, 3) NOT NULL,
    previous_quantity DECIMAL(12, 3) NOT NULL,
    new_quantity DECIMAL(12, 3) NOT NULL,
    updated_by INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- Wastage / spoilage
CREATE TABLE IF NOT EXISTS wastage (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    quantity DECIMAL(12, 3) NOT NULL,
    reason ENUM('spoiled','expired','damaged','theft','other') NOT NULL DEFAULT 'other',
    notes TEXT,
    recorded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (recorded_by) REFERENCES users(id)
);

-- Suppliers + Purchase orders
CREATE TABLE IF NOT EXISTS suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(50),
    email VARCHAR(100),
    address TEXT,
    notes TEXT,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS purchase_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id INT NOT NULL,
    reference VARCHAR(50),
    status ENUM('draft','ordered','received','cancelled') NOT NULL DEFAULT 'draft',
    total_cost DECIMAL(10, 2) NOT NULL DEFAULT 0,
    notes TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    received_at TIMESTAMP NULL,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS purchase_order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    po_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(12, 3) NOT NULL,
    unit_cost DECIMAL(10, 2) NOT NULL,
    line_total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Customers (phone-keyed loyalty + store credit)
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(150),
    email VARCHAR(100),
    loyalty_points INT NOT NULL DEFAULT 0,
    store_credit DECIMAL(10, 2) NOT NULL DEFAULT 0,
    total_spent DECIMAL(10, 2) NOT NULL DEFAULT 0,
    visit_count INT NOT NULL DEFAULT 0,
    last_visit_at TIMESTAMP NULL,
    notes TEXT,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Key/value settings (tax %, discount cap, etc.)
CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(50) PRIMARY KEY,
    `value` VARCHAR(255) NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT IGNORE INTO settings (`key`, `value`) VALUES
  ('tax_percent', '0'),
  ('max_discount_percent', '10');

-- Audit log
CREATE TABLE IF NOT EXISTS audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    actor_user_id INT,
    actor_username VARCHAR(50),
    action VARCHAR(60) NOT NULL,
    entity_type VARCHAR(40),
    entity_id VARCHAR(50),
    summary TEXT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (actor_user_id) REFERENCES users(id)
);

-- Hot-path indexes
CREATE INDEX idx_transactions_date ON transactions(created_at);
CREATE INDEX idx_sales_history_date ON sales_history(date);
CREATE INDEX idx_price_history_date ON price_history(changed_at);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_barcode ON products(barcode);
CREATE INDEX idx_transactions_order ON transactions(order_id);
CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_shifts_open ON shifts(staff_id, closed_at);
CREATE INDEX idx_audit_created ON audit_log(created_at);
CREATE INDEX idx_audit_entity ON audit_log(entity_type, entity_id);