-- ============================================================
-- Fuel Station Management System — MySQL Schema
-- Phase 1: Manager/Owner Back-Office
-- ============================================================

CREATE DATABASE IF NOT EXISTS fuel_station CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fuel_station;

-- ============================================================
-- USERS (Owner / Manager only)
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    username     VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role         ENUM('OWNER','MANAGER') NOT NULL DEFAULT 'MANAGER',
    full_name    VARCHAR(100) NOT NULL,
    contact      VARCHAR(20),
    is_active    TINYINT(1) NOT NULL DEFAULT 1,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- STAFF (Pump Operators — managed by Manager, no system login)
-- ============================================================
CREATE TABLE IF NOT EXISTS staff (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    employee_id  VARCHAR(20) UNIQUE,
    full_name    VARCHAR(100) NOT NULL,
    contact      VARCHAR(20),
    basic_salary DECIMAL(10,2),
    join_date    DATE,
    status       ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    notes        TEXT,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- PUMPS
-- Pump 1 → LP1  (LP92)
-- Pump 2 → LP2  (LP92) + EURO3
-- Pump 3 → LAD1 (LAD)
-- Pump 4 → LAD2 (LAD) + LADXM
-- ============================================================
CREATE TABLE IF NOT EXISTS pumps (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    pump_code    VARCHAR(10) UNIQUE NOT NULL,
    pump_number  TINYINT NOT NULL,
    fuel_type    ENUM('LP92','EURO3','LAD','LADXM') NOT NULL,
    description  VARCHAR(100),
    is_active    TINYINT(1) NOT NULL DEFAULT 1
);

-- ============================================================
-- FUEL RATES (historical log of price changes)
-- ============================================================
CREATE TABLE IF NOT EXISTS fuel_rates (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    fuel_type      ENUM('LP92','EURO3','LAD','LADXM') NOT NULL,
    rate           DECIMAL(8,2) NOT NULL,
    effective_from DATE NOT NULL,
    effective_to   DATE,
    set_by         INT REFERENCES users(id),
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_fuel_rates_type_date (fuel_type, effective_from)
);

-- ============================================================
-- DAILY SHIFT SESSIONS
-- One row per shift block per day (EM / DAY / MS / MG / ES)
-- Captures the full cash reconciliation for a staff block.
-- ============================================================
CREATE TABLE IF NOT EXISTS daily_shifts (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    record_date     DATE NOT NULL,
    -- EM=Early Morning(10PM-5:30AM), DAY=Day Shift(LP1),
    -- MS=Morning Diesel, MG=Morning Petrol, ES=Evening
    shift_type      ENUM('EM','DAY','MS','MG','ES') NOT NULL,
    staff_id        INT REFERENCES staff(id),
    -- Cash reconciliation (from the shift block totals)
    cash_collected  DECIMAL(12,2) NOT NULL DEFAULT 0,
    card_visa       DECIMAL(12,2) NOT NULL DEFAULT 0,
    card_amex       DECIMAL(12,2) NOT NULL DEFAULT 0,
    card_touch      DECIMAL(12,2) NOT NULL DEFAULT 0,
    credit_total    DECIMAL(12,2) NOT NULL DEFAULT 0,
    other_income    DECIMAL(12,2) NOT NULL DEFAULT 0,
    shortage        DECIMAL(12,2) NOT NULL DEFAULT 0,
    advance         DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_sale_calc DECIMAL(12,2) NOT NULL DEFAULT 0,  -- sum of pump sale amounts
    total_collected DECIMAL(12,2) NOT NULL DEFAULT 0,  -- cash+card+credit+other
    difference      DECIMAL(12,2) NOT NULL DEFAULT 0,  -- sale_calc - collected
    is_locked       TINYINT(1) NOT NULL DEFAULT 0,
    notes           TEXT,
    created_by      INT REFERENCES users(id),
    updated_by      INT REFERENCES users(id),
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_shift (record_date, shift_type),
    INDEX idx_daily_shifts_date (record_date),
    INDEX idx_daily_shifts_staff (staff_id)
);

-- ============================================================
-- PUMP READINGS (one row per pump per shift)
-- ============================================================
CREATE TABLE IF NOT EXISTS pump_readings (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    shift_id        INT NOT NULL,
    pump_id         INT NOT NULL,
    starting_meter  DECIMAL(12,3) NOT NULL DEFAULT 0,
    ending_meter    DECIMAL(12,3) NOT NULL DEFAULT 0,
    meter_out       DECIMAL(12,3) NOT NULL DEFAULT 0,   -- ending - starting
    testing_ltr     DECIMAL(8,3)  NOT NULL DEFAULT 0,
    sale_ltr        DECIMAL(10,3) NOT NULL DEFAULT 0,   -- meter_out - testing
    fuel_rate       DECIMAL(8,2)  NOT NULL DEFAULT 0,
    sale_amount     DECIMAL(12,2) NOT NULL DEFAULT 0,   -- sale_ltr × rate
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shift_id) REFERENCES daily_shifts(id) ON DELETE CASCADE,
    FOREIGN KEY (pump_id)  REFERENCES pumps(id),
    UNIQUE KEY uq_pump_reading (shift_id, pump_id),
    INDEX idx_pump_readings_shift (shift_id)
);

-- ============================================================
-- CASH DENOMINATIONS (per shift, up to 4 bags)
-- ============================================================
CREATE TABLE IF NOT EXISTS cash_denominations (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    shift_id        INT NOT NULL,
    bag_number      TINYINT NOT NULL,   -- 1, 2, 3, 4
    note_5000       INT NOT NULL DEFAULT 0,
    note_1000       INT NOT NULL DEFAULT 0,
    note_500        INT NOT NULL DEFAULT 0,
    note_100        INT NOT NULL DEFAULT 0,
    note_50         INT NOT NULL DEFAULT 0,
    note_20         INT NOT NULL DEFAULT 0,
    note_10         INT NOT NULL DEFAULT 0,
    calculated_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    FOREIGN KEY (shift_id) REFERENCES daily_shifts(id) ON DELETE CASCADE,
    UNIQUE KEY uq_denom_bag (shift_id, bag_number)
);

-- ============================================================
-- ROSTER (shift scheduling)
-- ============================================================
CREATE TABLE IF NOT EXISTS roster (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    roster_date  DATE NOT NULL,
    shift_type   ENUM('EM','DAY','MS','MG','ES') NOT NULL,
    staff_id     INT NOT NULL REFERENCES staff(id),
    pump_id      INT REFERENCES pumps(id),
    status       ENUM('SCHEDULED','PRESENT','ABSENT','SWAPPED') NOT NULL DEFAULT 'SCHEDULED',
    swap_notes   TEXT,
    created_by   INT REFERENCES users(id),
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_roster (roster_date, shift_type, pump_id)
);

-- ============================================================
-- CREDIT CUSTOMERS
-- ============================================================
CREATE TABLE IF NOT EXISTS credit_customers (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    company_name     VARCHAR(100) NOT NULL,
    contact_person   VARCHAR(100),
    contact_phone    VARCHAR(20),
    address          TEXT,
    credit_limit     DECIMAL(12,2) NOT NULL DEFAULT 0,
    current_balance  DECIMAL(12,2) NOT NULL DEFAULT 0,  -- total outstanding
    status           ENUM('ACTIVE','SUSPENDED','CLOSED') NOT NULL DEFAULT 'ACTIVE',
    approved_by      INT REFERENCES users(id),
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_credit_customers_name (company_name)
);

-- ============================================================
-- CREDIT CUSTOMER VEHICLES
-- ============================================================
CREATE TABLE IF NOT EXISTS customer_vehicles (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL,
    vehicle_no   VARCHAR(20) NOT NULL,
    vehicle_type VARCHAR(50),
    notes        TEXT,
    FOREIGN KEY (customer_id) REFERENCES credit_customers(id) ON DELETE CASCADE
);

-- ============================================================
-- CREDIT SALES (individual bills per day)
-- shift_id may be NULL (bill logged at end of day, shift unknown)
-- ============================================================
CREATE TABLE IF NOT EXISTS credit_sales (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    sale_date    DATE NOT NULL,
    shift_id     INT REFERENCES daily_shifts(id),
    customer_id  INT NOT NULL,
    bill_no      VARCHAR(20) NOT NULL,
    amount       DECIMAL(12,2) NOT NULL,
    vehicle_no   VARCHAR(20),
    fuel_type    ENUM('LP92','EURO3','LAD','LADXM'),
    liters       DECIMAL(10,3),
    notes        TEXT,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES credit_customers(id),
    INDEX idx_credit_sales_date (sale_date),
    INDEX idx_credit_sales_customer (customer_id)
);

-- ============================================================
-- CREDIT PAYMENTS (customer payments against outstanding balance)
-- ============================================================
CREATE TABLE IF NOT EXISTS credit_payments (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    customer_id    INT NOT NULL,
    payment_date   DATE NOT NULL,
    amount         DECIMAL(12,2) NOT NULL,
    reference_no   VARCHAR(50),
    payment_method ENUM('CASH','BANK_TRANSFER','CHEQUE','OTHER') NOT NULL DEFAULT 'CASH',
    notes          TEXT,
    received_by    INT REFERENCES users(id),
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES credit_customers(id),
    INDEX idx_credit_payments_customer (customer_id)
);

-- ============================================================
-- TANK / DIP READINGS (one reading per tank per day)
-- ============================================================
CREATE TABLE IF NOT EXISTS tank_readings (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    reading_date  DATE NOT NULL,
    reading_time  ENUM('START','END') NOT NULL,
    fuel_type     ENUM('LP92','EURO3','LAD','LADXM') NOT NULL,
    depth_cm      DECIMAL(8,2),
    liters        DECIMAL(10,2),
    recorded_by   INT REFERENCES users(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tank_reading (reading_date, reading_time, fuel_type)
);

-- ============================================================
-- STOCK DELIVERIES (Bowser / tanker in)
-- ============================================================
CREATE TABLE IF NOT EXISTS stock_deliveries (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    delivery_date DATE NOT NULL,
    fuel_type     ENUM('LP92','EURO3','LAD','LADXM') NOT NULL,
    liters        DECIMAL(10,2) NOT NULL,
    supplier      VARCHAR(100),
    invoice_no    VARCHAR(50),
    rate_per_ltr  DECIMAL(8,2),
    total_cost    DECIMAL(12,2),
    notes         TEXT,
    received_by   INT REFERENCES users(id),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stock_deliveries_date (delivery_date)
);

-- ============================================================
-- CARD SETTLEMENTS (bank settlement records)
-- ============================================================
CREATE TABLE IF NOT EXISTS card_settlements (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    settlement_date  DATE NOT NULL,
    card_type        ENUM('VISA','AMEX','TOUCH','OTHER') NOT NULL,
    amount           DECIMAL(12,2) NOT NULL,
    bank_reference   VARCHAR(50),
    notes            TEXT,
    recorded_by      INT REFERENCES users(id),
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- AUDIT LOG
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT REFERENCES users(id),
    action      ENUM('CREATE','UPDATE','DELETE','LOGIN','LOGOUT','EXPORT','IMPORT') NOT NULL,
    table_name  VARCHAR(50),
    record_id   INT,
    old_values  JSON,
    new_values  JSON,
    ip_address  VARCHAR(45),
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_log_user (user_id),
    INDEX idx_audit_log_date (created_at)
);

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default pumps
INSERT IGNORE INTO pumps (pump_code, pump_number, fuel_type, description) VALUES
    ('LP1',   1, 'LP92',  'Pump 1 - Lanka Petrol 92'),
    ('LP2',   2, 'LP92',  'Pump 2 - Lanka Petrol 92'),
    ('EURO3', 2, 'EURO3', 'Pump 2 - Euro 3 Petrol'),
    ('LAD1',  3, 'LAD',   'Pump 3 - Lanka Auto Diesel'),
    ('LAD2',  4, 'LAD',   'Pump 4 - Lanka Auto Diesel'),
    ('LADXM', 4, 'LADXM', 'Pump 4 - Xtra Mile Diesel');

-- Historical fuel rates (from Excel data)
INSERT IGNORE INTO fuel_rates (fuel_type, rate, effective_from, effective_to) VALUES
    ('LP92',  293.00, '2026-01-01', '2026-04-20'),
    ('EURO3', 324.00, '2026-01-01', '2026-04-20'),
    ('LAD',   274.00, '2026-01-01', '2026-04-20'),
    ('LADXM', 298.00, '2026-01-01', '2026-04-20'),
    -- Current rates (active)
    ('LP92',  410.00, '2026-04-21', NULL),
    ('EURO3', 465.00, '2026-04-21', NULL),
    ('LAD',   392.00, '2026-04-21', NULL),
    ('LADXM', 590.00, '2026-04-21', NULL);

-- Default admin user (password: admin123 — CHANGE AFTER FIRST LOGIN)
-- Hash generated with bcrypt rounds=12
INSERT IGNORE INTO users (username, password_hash, role, full_name) VALUES
    ('admin', '$2b$12$placeholder_change_on_first_run', 'OWNER', 'System Administrator');