-- MySQL Schema for Profit Tracker

-- Table for users (for login functionality)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for purchase records
CREATE TABLE IF NOT EXISTS purchase_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_quantity DECIMAL(10, 2) DEFAULT 0,
    price_per_quantity DECIMAL(10, 2) DEFAULT 0,
    purchase_total_price DECIMAL(10, 2) DEFAULT 0,
    purchase_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for sales records
CREATE TABLE IF NOT EXISTS sales_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sales_quantity DECIMAL(10, 2),
    price_per_quantity DECIMAL(10, 2),
    sales_total_price DECIMAL(10, 2),
    total_cashback DECIMAL(10, 2) DEFAULT 0.0,
    cashback_type VARCHAR(50),
    cashback_value DECIMAL(10, 2) DEFAULT 0.0,
    created_at TIMESTAMP 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);
