-- TSHEN SQLite Schema and Sample Data
-- Usage: sqlite3 tshen.sqlite < tshen_sqlite.sql

-- Drop existing tables
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS credit_purchases;
DROP TABLE IF EXISTS credit_codes;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS discount_codes;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS messages;

-- Create tables
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user',
    credits INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE classes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    location TEXT NOT NULL,
    date TEXT NOT NULL,
    time TEXT NOT NULL,
    capacity INTEGER DEFAULT 10,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE bookings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    class_id INTEGER NOT NULL,
    status TEXT DEFAULT 'confirmed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (class_id) REFERENCES classes (id)
);

CREATE TABLE credit_purchases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    credits INTEGER NOT NULL,
    expires_at DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE TABLE credit_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT UNIQUE NOT NULL,
    credits INTEGER NOT NULL,
    used_by TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE discount_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT UNIQUE NOT NULL,
    percent INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    qty INTEGER NOT NULL,
    total_price REAL NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
-- Default password for all users: 'password' (hashed)
INSERT INTO users (email, password, role, credits) VALUES 
('admin@tshen.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 50),
('studio@tshen.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'studio', 0),
('user@tshen.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'user', 5);

INSERT INTO classes (title, location, date, time, capacity) VALUES 
('HIIT Training', 'Central', '2025-10-15', '09:00', 12),
('Yoga Flow', 'East', '2025-10-16', '18:00', 15),
('Spin Class', 'West', '2025-10-17', '07:30', 10),
('Pilates', 'North', '2025-10-18', '10:00', 8),
('Boxing', 'South', '2025-10-19', '19:00', 6);

INSERT INTO products (name, price) VALUES 
('TSHEN T-Shirt', 20.00),
('TSHEN Water Bottle', 12.00),
('TSHEN Towel', 15.00),
('TSHEN Gym Bag', 35.00);

INSERT INTO discount_codes (code, percent) VALUES 
('WELCOME10', 10),
('STUDENT15', 15),
('MEMBER20', 20);

INSERT INTO credit_codes (code, credits) VALUES 
('FREE5CREDITS', 5),
('WELCOME3', 3);