PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE orders (
    id TEXT PRIMARY KEY,
    order_date TEXT NOT NULL,
    order_time TEXT NOT NULL,
    material_code TEXT,
    material_desc TEXT NOT NULL,
    units INTEGER DEFAULT 0,
    is_sent INTEGER DEFAULT 0,
    is_received INTEGER DEFAULT 0,
    is_paid INTEGER DEFAULT 0,
    payment_note TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE TABLE sales (
    id TEXT PRIMARY KEY,
    sale_date TEXT NOT NULL,
    sale_time TEXT NOT NULL,
    material_desc TEXT NOT NULL,
    units INTEGER DEFAULT 1,
    total_amount REAL NOT NULL,
    payment_method TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE TABLE settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(is_received, is_sent, is_paid);
CREATE INDEX idx_sales_date ON sales(sale_date);
COMMIT;
