-- =========================================================
-- Restaurant Management System - Anuradhapura Rice & Curry
-- Full Foundation Schema (Phase 1)
-- PHP 8.2 / MySQL / PDO
-- =========================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------
-- 1. SETTINGS (key-value, single row per key, company info)
-- ---------------------------------------------------------
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 2. ROLES & PERMISSIONS
-- ---------------------------------------------------------
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,   -- e.g. OWNER, MANAGER, CASHIER, KITCHEN, RIDER
    description VARCHAR(255) NULL,
    is_system TINYINT(1) DEFAULT 0,          -- 1 = cannot be deleted (Owner)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    module_key VARCHAR(50) NOT NULL UNIQUE,  -- e.g. orders, stock, payroll, finance, delivery, reports, admin
    module_label VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    can_view TINYINT(1) DEFAULT 0,
    can_add TINYINT(1) DEFAULT 0,
    can_edit TINYINT(1) DEFAULT 0,
    can_delete TINYINT(1) DEFAULT 0,
    UNIQUE KEY uniq_role_perm (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 3. USERS (staff accounts)
-- ---------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NULL,
    phone VARCHAR(20) NULL,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    nic_number VARCHAR(20) NULL,
    basic_salary DECIMAL(12,2) DEFAULT 0,     -- for payroll (salary staff)
    wage_type ENUM('MONTHLY','DAILY','PIECE_RATE') DEFAULT 'MONTHLY',
    daily_wage DECIMAL(12,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    is_protected TINYINT(1) DEFAULT 0,        -- self-protection guard (cannot be deleted/deactivated by others)
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 4. AUDIT LOG
-- ---------------------------------------------------------
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,             -- e.g. LOGIN, ORDER_CREATE, STOCK_ADJUST
    module_key VARCHAR(50) NULL,
    description TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 5. CUSTOMERS
-- ---------------------------------------------------------
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100) NULL,
    address TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 6. DELIVERY ZONES (Free Delivery coverage areas)
-- ---------------------------------------------------------
CREATE TABLE delivery_zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_name VARCHAR(100) NOT NULL,          -- e.g. "Anuradhapura Town", "New Town"
    center_latitude DECIMAL(10,7) NULL,
    center_longitude DECIMAL(10,7) NULL,
    radius_km DECIMAL(5,2) DEFAULT 3.0,
    is_free_delivery TINYINT(1) DEFAULT 1,
    delivery_fee DECIMAL(10,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 7. MENU: CATEGORIES / ITEMS / VARIANTS
-- ---------------------------------------------------------
CREATE TABLE menu_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,      -- e.g. Rice & Curry, Short Eats, Beverages
    display_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    item_name VARCHAR(150) NOT NULL,          -- e.g. "Chicken Rice & Curry"
    description TEXT NULL,
    image_path VARCHAR(255) NULL,
    base_price DECIMAL(10,2) NOT NULL DEFAULT 0,
    is_available TINYINT(1) DEFAULT 1,
    track_stock TINYINT(1) DEFAULT 1,         -- deduct raw materials via recipe on sale
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES menu_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE menu_item_variants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    menu_item_id INT NOT NULL,
    variant_name VARCHAR(100) NOT NULL,       -- e.g. "Regular", "Large", "1 Curry", "2 Curries"
    price_adjustment DECIMAL(10,2) DEFAULT 0, -- added to base_price
    is_default TINYINT(1) DEFAULT 0,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 8. RAW MATERIALS / STOCK
-- ---------------------------------------------------------
CREATE TABLE raw_materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_name VARCHAR(150) NOT NULL,      -- e.g. Rice, Chicken, Coconut Oil
    unit VARCHAR(20) NOT NULL,                -- kg, g, l, ml, pcs
    current_stock DECIMAL(12,3) DEFAULT 0,
    reorder_level DECIMAL(12,3) DEFAULT 0,
    unit_cost DECIMAL(10,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE stock_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    raw_material_id INT NOT NULL,
    transaction_type ENUM('PURCHASE_IN','SALE_DEDUCT','ADJUSTMENT','WASTAGE') NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,          -- positive or negative depending on type
    reference_type VARCHAR(50) NULL,          -- e.g. 'ORDER', 'MANUAL'
    reference_id INT NULL,
    notes VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (raw_material_id) REFERENCES raw_materials(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Recipe: how much raw material 1 unit of a menu item consumes
CREATE TABLE recipes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    menu_item_id INT NOT NULL,
    raw_material_id INT NOT NULL,
    quantity_required DECIMAL(12,3) NOT NULL, -- per 1 unit sold
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
    FOREIGN KEY (raw_material_id) REFERENCES raw_materials(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 9. ORDERS
-- ---------------------------------------------------------
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(30) NOT NULL UNIQUE, -- e.g. ORD-20260719-0001
    order_type ENUM('DINE_IN','TAKEAWAY','ONLINE_DELIVERY') NOT NULL,
    customer_id INT NULL,
    delivery_zone_id INT NULL,
    status ENUM('PENDING','CONFIRMED','PREPARING','READY','OUT_FOR_DELIVERY','COMPLETED','CANCELLED') DEFAULT 'PENDING',
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
    discount DECIMAL(10,2) NOT NULL DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    delivery_address TEXT NULL,
    delivery_latitude DECIMAL(10,7) NULL,
    delivery_longitude DECIMAL(10,7) NULL,
    notes VARCHAR(255) NULL,
    created_by INT NULL,                      -- staff who created (cashier) or NULL if customer online
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (delivery_zone_id) REFERENCES delivery_zones(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    menu_item_id INT NOT NULL,
    variant_id INT NULL,
    item_name_snapshot VARCHAR(150) NOT NULL, -- snapshot in case item edited later
    unit_price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    line_total DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id),
    FOREIGN KEY (variant_id) REFERENCES menu_item_variants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    status VARCHAR(30) NOT NULL,
    changed_by INT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 10. DELIVERY (Riders + tracking)
-- ---------------------------------------------------------
CREATE TABLE deliveries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL UNIQUE,
    rider_id INT NULL,                        -- FK to users (role = RIDER)
    status ENUM('UNASSIGNED','ASSIGNED','PICKED_UP','DELIVERED','FAILED') DEFAULT 'UNASSIGNED',
    assigned_at TIMESTAMP NULL,
    picked_up_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    notes VARCHAR(255) NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (rider_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 11. PAYMENTS
-- ---------------------------------------------------------
CREATE TABLE payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    method_name VARCHAR(50) NOT NULL UNIQUE,  -- CASH, CARD, ONLINE_TRANSFER, KOKO, etc
    finance_account_id INT NULL,              -- linked ledger account for auto-crediting
    fee_percentage DECIMAL(5,2) DEFAULT 0,    -- e.g. card processing fee
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_method_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    fee_amount DECIMAL(10,2) DEFAULT 0,
    status ENUM('PENDING','PAID','REFUNDED') DEFAULT 'PAID',
    paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    received_by INT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id),
    FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 12. FINANCE (ledger accounts + transactions + expenses)
-- ---------------------------------------------------------
CREATE TABLE finance_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_name VARCHAR(100) NOT NULL,       -- e.g. Cash in Hand, Bank Account, Card Settlement
    account_type ENUM('CASH','BANK','OTHER') DEFAULT 'CASH',
    current_balance DECIMAL(14,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE finance_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    finance_account_id INT NOT NULL,
    transaction_type ENUM('CREDIT','DEBIT') NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    category VARCHAR(50) NOT NULL,            -- SALES, EXPENSE, PAYROLL, REFUND, MANUAL
    reference_type VARCHAR(50) NULL,          -- ORDER, EXPENSE, PAYROLL_RUN, MANUAL
    reference_id INT NULL,
    description VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (finance_account_id) REFERENCES finance_accounts(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    expense_category VARCHAR(100) NOT NULL,   -- e.g. Gas, Electricity, Rent, Transport
    amount DECIMAL(12,2) NOT NULL,
    finance_account_id INT NOT NULL,          -- account debited from
    expense_date DATE NOT NULL,
    notes VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (finance_account_id) REFERENCES finance_accounts(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 13. ATTENDANCE / PAYROLL
-- ---------------------------------------------------------
CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    check_in TIME NULL,
    check_out TIME NULL,
    status ENUM('PRESENT','ABSENT','HALF_DAY','LEAVE') DEFAULT 'PRESENT',
    notes VARCHAR(255) NULL,
    UNIQUE KEY uniq_user_date (user_id, attendance_date),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payroll_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    period_month TINYINT NOT NULL,
    period_year SMALLINT NOT NULL,
    status ENUM('DRAFT','FINALIZED','PAID') DEFAULT 'DRAFT',
    generated_by INT NULL,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_period (period_month, period_year),
    FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payroll_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payroll_run_id INT NOT NULL,
    user_id INT NOT NULL,
    days_present INT DEFAULT 0,
    days_absent INT DEFAULT 0,
    basic_amount DECIMAL(12,2) DEFAULT 0,
    additions DECIMAL(12,2) DEFAULT 0,        -- bonus, OT
    deductions DECIMAL(12,2) DEFAULT 0,       -- loans, EPF, etc
    net_pay DECIMAL(12,2) DEFAULT 0,
    is_paid TINYINT(1) DEFAULT 0,
    paid_at TIMESTAMP NULL,
    FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------
-- 14. BACKUPS LOG (actual files handled by pure-PHP backup script)
-- ---------------------------------------------------------
CREATE TABLE backup_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    file_size_kb INT DEFAULT 0,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;