schema.sql

-- TuckShop POC - Minimal Schema

CREATE TABLE IF NOT EXISTS products (
    id              TEXT PRIMARY KEY,
    sku             TEXT UNIQUE,
    name            TEXT NOT NULL,
    cost_price      REAL DEFAULT 0,
    selling_price   REAL NOT NULL,
    quantity_on_hand REAL DEFAULT 0,
    is_active       INTEGER DEFAULT 1
);

CREATE TABLE IF NOT EXISTS sales (
    id              TEXT PRIMARY KEY,
    receipt_number  TEXT UNIQUE NOT NULL,
    total_amount    REAL NOT NULL,
    total_cost      REAL DEFAULT 0,
    sale_date       TEXT,
    sale_time       TEXT,
    status          TEXT DEFAULT 'COMPLETED'
);

CREATE TABLE IF NOT EXISTS sale_items (
    id              TEXT PRIMARY KEY,
    sale_id         TEXT NOT NULL,
    product_id      TEXT NOT NULL,
    product_name    TEXT NOT NULL,
    quantity        REAL NOT NULL,
    unit_price      REAL NOT NULL,
    unit_cost       REAL DEFAULT 0,
    line_total      REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS sequences (
    name            TEXT PRIMARY KEY,
    current_value   INTEGER DEFAULT 0
);

INSERT OR IGNORE INTO sequences (name, current_value) VALUES ('receipt', 0);

-- Seed products
INSERT OR IGNORE INTO products (id, sku, name, cost_price, selling_price, quantity_on_hand) VALUES
    ('p1', 'COKE500', 'Coca-Cola 500ml', 0.80, 1.00, 50),
    ('p2', 'BREAD', 'Bread White', 0.90, 1.20, 30),
    ('p3', 'EGGS6', 'Eggs (6 pack)', 1.50, 2.00, 20),
    ('p4', 'CHIPS', 'Potato Chips', 0.40, 0.75, 100),
    ('p5', 'WATER', 'Water 500ml', 0.30, 0.50, 80);