schema.sql

-- Database Schema for POS Application

-- Products Table
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    barcode TEXT UNIQUE,
    price REAL NOT NULL,
    cost REAL DEFAULT 0,
    category TEXT,
    category_id INTEGER REFERENCES categories(id),
    image TEXT,
    deleted_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Stock Table
CREATE TABLE IF NOT EXISTS stock (
    product_id INTEGER PRIMARY KEY REFERENCES products(id),
    count INTEGER NOT NULL DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Suppliers Table
CREATE TABLE IF NOT EXISTS suppliers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    contact_person TEXT,
    phone TEXT,
    email TEXT,
    address TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Sales Table
CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    items TEXT, -- Kept for backward compatibility or denormalized data
    total REAL NOT NULL,
    payment_method TEXT,
    sale_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Sale Items Table
CREATE TABLE IF NOT EXISTS sale_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sale_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    price REAL NOT NULL,
    payment_status TEXT DEFAULT 'unverified',
    verified_by INTEGER REFERENCES users(id),
    verified_at DATETIME,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Expenses Table
CREATE TABLE IF NOT EXISTS expenses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category TEXT NOT NULL,
    amount REAL NOT NULL,
    description TEXT,
    incurred_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Purchase Orders Table
CREATE TABLE IF NOT EXISTS purchase_orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    supplier_id INTEGER,
    total REAL NOT NULL,
    status TEXT DEFAULT 'pending',
    notes TEXT,
    received_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

-- Categories Table
CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Purchase Order Items Table
CREATE TABLE IF NOT EXISTS purchase_order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    purchase_order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    cost REAL NOT NULL,
    FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Users Table (Authentication & RBAC)
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'cashier',
    last_seen_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_sales_created_at ON sales(created_at);
CREATE INDEX IF NOT EXISTS idx_expenses_created_at ON expenses(created_at);
CREATE INDEX IF NOT EXISTS idx_products_barcode ON products(barcode);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_received_at ON purchase_orders(received_at);
CREATE INDEX IF NOT EXISTS idx_purchase_order_items_po_id ON purchase_order_items(purchase_order_id);
CREATE INDEX IF NOT EXISTS idx_sale_items_sale_id ON sale_items(sale_id);

-- Settings Table
CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    business_name TEXT NOT NULL DEFAULT 'Set Up Your Business Name',
    primary_color TEXT NOT NULL DEFAULT '#667eea',
    secondary_color TEXT NOT NULL DEFAULT '#764ba2',
    currency_symbol TEXT NOT NULL DEFAULT 'Ksh',
    currency_code TEXT NOT NULL DEFAULT 'KES',
    tax_rate REAL DEFAULT 0,
    logo_url TEXT,
    address TEXT,
    phone TEXT,
    email TEXT,
    timezone TEXT NOT NULL DEFAULT 'Africa/Nairobi',
    setup_complete BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Seed Default Admin User
-- Username: admin
-- Password: admin123 (bcrypt hashed with 10 salt rounds)
INSERT OR IGNORE INTO users (username, password, role) VALUES 
('admin', '$2b$10$3oQIQ6MduZBgkfqO3pLsbO4twZbSmWlH09ZJCqzQv0ScK8Zc0Z2Qu', 'admin');

-- Migrations Tracking Table
CREATE TABLE IF NOT EXISTS _migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    executed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Borrowed Items Table
CREATE TABLE IF NOT EXISTS borrowed_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    borrowed_from TEXT NOT NULL,
    reason TEXT,
    status TEXT DEFAULT 'pending', -- pending, returned, etc.
    returned_quantity INTEGER DEFAULT 0,
    paid_quantity INTEGER DEFAULT 0,
    paid_amount REAL DEFAULT 0,
    borrowed_at DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Loans Table
CREATE TABLE IF NOT EXISTS loans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    borrower_name TEXT NOT NULL,
    borrower_contact TEXT,
    collateral TEXT,
    collateral_description TEXT,
    status TEXT DEFAULT 'active', -- active, returned, partially_returned
    loaned_at DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Loan Items Table (One-to-Many with loans)
CREATE TABLE IF NOT EXISTS loan_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    loan_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    returned_quantity INTEGER DEFAULT 0,
    FOREIGN KEY (loan_id) REFERENCES loans(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Loan Item Returns Table (for tracking partial returns and substitutions)
CREATE TABLE IF NOT EXISTS loan_item_returns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    loan_item_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    replacement_product_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (loan_item_id) REFERENCES loan_items(id),
    FOREIGN KEY (replacement_product_id) REFERENCES products(id)
);

-- Stock Counts Table (for verifying physical stock vs system stock)
CREATE TABLE IF NOT EXISTS stock_counts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    status TEXT DEFAULT 'draft',
    counted_by INTEGER,
    reconciled_by INTEGER,
    reconciled_at DATETIME,
    count_date DATE,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (counted_by) REFERENCES users(id),
    FOREIGN KEY (reconciled_by) REFERENCES users(id)
);

-- Stock Count Items Table
CREATE TABLE IF NOT EXISTS stock_count_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stock_count_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    system_count INTEGER NOT NULL,
    actual_count INTEGER,
    variance INTEGER,
    reason TEXT,
    FOREIGN KEY (stock_count_id) REFERENCES stock_counts(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);