-- SQLite Schema for Profit Tracker

-- Table for users (for login functionality)
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Table for purchase records
CREATE TABLE IF NOT EXISTS purchase_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    purchase_quantity REAL DEFAULT 0,
    price_per_quantity REAL DEFAULT 0,
    purchase_total_price REAL DEFAULT 0,
    purchase_date TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Table for sales records
CREATE TABLE IF NOT EXISTS sales_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sales_quantity REAL,
    price_per_quantity REAL,
    sales_total_price REAL,
    total_cashback REAL DEFAULT 0.0,
    cashback_type TEXT,
    cashback_value REAL DEFAULT 0.0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Optional: Add a default admin user for testing (change password in production!)
-- INSERT INTO users (username, password) VALUES ('admin', '$2y$10$YOUR_HASHED_PASSWORD_HERE');
-- You can generate a hashed password using password_hash('your_password', PASSWORD_DEFAULT);
