-- ============================================================
--  High Rd Store · Cashing Up · Database Setup
--  Run this ONCE in your OVH phpMyAdmin
-- ============================================================

CREATE TABLE IF NOT EXISTS cashing_entries (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    submitted_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    entry_date      DATE NOT NULL,
    cashier         VARCHAR(50) NOT NULL,

    -- Money Out (notes)
    note_50         DECIMAL(10,2) DEFAULT 0,
    note_20         DECIMAL(10,2) DEFAULT 0,
    note_10         DECIMAL(10,2) DEFAULT 0,
    note_5          DECIMAL(10,2) DEFAULT 0,
    excess_coins    DECIMAL(10,2) DEFAULT 0,
    cash_total      DECIMAL(10,2) DEFAULT 0,
    coin_total      DECIMAL(10,2) DEFAULT 0,

    -- Coin bags
    bag_510         DECIMAL(10,2) DEFAULT 0,
    bag_21          DECIMAL(10,2) DEFAULT 0,
    bag_50p         DECIMAL(10,2) DEFAULT 0,
    bag_20p         DECIMAL(10,2) DEFAULT 0,
    bag_10p         DECIMAL(10,2) DEFAULT 0,
    bag_small       DECIMAL(10,2) DEFAULT 0,
    coin_bags_total DECIMAL(10,2) DEFAULT 0,

    payout_who      VARCHAR(100) DEFAULT '',

    -- Payout & till
    payout          DECIMAL(10,2) DEFAULT 0,
    total_till      DECIMAL(10,2) DEFAULT 0,
    payzone         DECIMAL(10,2) DEFAULT 0,
    card_report     DECIMAL(10,2) DEFAULT 0,

    -- Z Report
    z_report        DECIMAL(10,2) DEFAULT 0,
    foodhub         DECIMAL(10,2) DEFAULT 0,
    zeus            DECIMAL(10,2) DEFAULT 0,
    ubereats        DECIMAL(10,2) DEFAULT 0,
    sales_total     DECIMAL(10,2) DEFAULT 0,

    -- Scratch cards (tonight closing numbers + sold + value + deliveries per pack)
    sc1_tonight     INT DEFAULT NULL,
    sc1_sold        INT DEFAULT 0,
    sc1_value       DECIMAL(10,2) DEFAULT 0,
    sc1_deliveries  INT DEFAULT 0,

    sc2_tonight     INT DEFAULT NULL,
    sc2_sold        INT DEFAULT 0,
    sc2_value       DECIMAL(10,2) DEFAULT 0,
    sc2_deliveries  INT DEFAULT 0,

    sc3_tonight     INT DEFAULT NULL,
    sc3_sold        INT DEFAULT 0,
    sc3_value       DECIMAL(10,2) DEFAULT 0,
    sc3_deliveries  INT DEFAULT 0,

    sc4_tonight     INT DEFAULT NULL,
    sc4_sold        INT DEFAULT 0,
    sc4_value       DECIMAL(10,2) DEFAULT 0,
    sc4_deliveries  INT DEFAULT 0,

    sc5_tonight     INT DEFAULT NULL,
    sc5_sold        INT DEFAULT 0,
    sc5_value       DECIMAL(10,2) DEFAULT 0,
    sc5_deliveries  INT DEFAULT 0,

    sc6_tonight     INT DEFAULT NULL,
    sc6_sold        INT DEFAULT 0,
    sc6_value       DECIMAL(10,2) DEFAULT 0,
    sc6_deliveries  INT DEFAULT 0,

    sc7_tonight     INT DEFAULT NULL,
    sc7_sold        INT DEFAULT 0,
    sc7_value       DECIMAL(10,2) DEFAULT 0,
    sc7_deliveries  INT DEFAULT 0,

    sc8_tonight     INT DEFAULT NULL,
    sc8_sold        INT DEFAULT 0,
    sc8_value       DECIMAL(10,2) DEFAULT 0,
    sc8_deliveries  INT DEFAULT 0,

    sc9_tonight     INT DEFAULT NULL,
    sc9_sold        INT DEFAULT 0,
    sc9_value       DECIMAL(10,2) DEFAULT 0,
    sc9_deliveries  INT DEFAULT 0,

    sc10_tonight    INT DEFAULT NULL,
    sc10_sold       INT DEFAULT 0,
    sc10_value      DECIMAL(10,2) DEFAULT 0,
    sc10_deliveries INT DEFAULT 0,

    scratch_total   DECIMAL(10,2) DEFAULT 0,
    delivery_total  INT DEFAULT 0,

    -- Grand total (excludes money out)
    grand_total     DECIMAL(10,2) DEFAULT 0,

    -- Scratch card state saved for next shift
    scratch_state   JSON,

    INDEX idx_date    (entry_date),
    INDEX idx_cashier (cashier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
--  Admin credentials table (owner only)
-- ============================================================
CREATE TABLE IF NOT EXISTS admin_users (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    username     VARCHAR(50) NOT NULL UNIQUE,
    password     VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin: username = admin, password = HighRd2024!
-- CHANGE THIS PASSWORD after first login
INSERT INTO admin_users (username, password)
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uZutLnXK2')
ON DUPLICATE KEY UPDATE username=username;
