-- TSHEN Complete Database Schema and Sample Data
-- This file contains all tables and data needed for the TSHEN application
-- Usage: import via phpMyAdmin or mysql CLI



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

-- Create users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('user','studio','admin') NOT NULL DEFAULT 'user',
    credits INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Create user_profiles table
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    gender VARCHAR(20),
    bio TEXT,
    profile_picture VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;




-- Create classes table
CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    location ENUM('Central','North','East','West','South') NOT NULL,
    date DATE NOT NULL,
    time TIME NOT NULL,
    capacity INT DEFAULT 10,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Create bookings table
CREATE TABLE bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    class_id INT NOT NULL,
    status ENUM('confirmed','cancelled') DEFAULT 'confirmed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES classes (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Create credit_purchases table
CREATE TABLE credit_purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    credits INT NOT NULL,
    expires_at DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Create credit_codes table
CREATE TABLE credit_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) UNIQUE NOT NULL,
    credits INT NOT NULL,
    used_by VARCHAR(64)
) ENGINE=InnoDB;

-- Create products table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Create discount_codes table
CREATE TABLE discount_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) UNIQUE NOT NULL,
    percent INT NOT NULL
) ENGINE=InnoDB;

-- Create orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    qty INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Create messages table
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert sample data for users
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 sample data for user_profiles
INSERT INTO user_profiles (user_id, first_name, last_name, phone, address, gender, bio, profile_picture) VALUES
(1, 'Admin', 'User', '123-456-7890', '123 Admin St, Singapore', 'male', 'System administrator', '/public/icon.png'),
(2, 'Studio', 'Manager', '234-567-8901', '456 Studio Ave, Singapore', 'female', 'Studio manager', '/public/icon.png'),
(3, 'Regular', 'User', '345-678-9012', '789 User Blvd, Singapore', 'male', 'Fitness enthusiast', '/public/icon.png');

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

-- Insert sample data for bookings
INSERT INTO bookings (user_id, class_id, status) VALUES
(3, 1, 'confirmed'),
(3, 2, 'confirmed'),
(2, 3, 'confirmed'),
(1, 4, 'confirmed');

-- Insert sample data for products
INSERT INTO products (name, price) VALUES
('Yoga Mat', 29.99),
('Resistance Bands', 15.99),
('Water Bottle', 9.99),
('Fitness Tracker', 99.99);

-- Insert sample data for discount_codes
INSERT INTO discount_codes (code, percent) VALUES
('WELCOME10', 10),
('SUMMER20', 20);

-- Insert sample data for credit_codes
INSERT INTO credit_codes (code, credits) VALUES
('FREE5CREDITS', 5),
('BONUS10', 10);

-- Insert sample data for messages
INSERT INTO messages (name, email, message) VALUES
('John Doe', 'john@example.com', 'I love your fitness classes!'),
('Jane Smith', 'jane@example.com', 'When will you add more yoga classes?');


-- Create bookings, credit_bundles and purchases tables
CREATE TABLE IF NOT EXISTS bookings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  class_id INT NULL,
  status ENUM('booked','cancelled') NOT NULL DEFAULT 'booked',
  class_time DATETIME NOT NULL,
  consumed_credits TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  cancelled_at DATETIME NULL,
  INDEX (user_id),
  INDEX (class_time)
);

CREATE TABLE IF NOT EXISTS credit_bundles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  credits INT NOT NULL,
  credits_remaining INT NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id),
  INDEX (expires_at)
);

CREATE TABLE IF NOT EXISTS purchases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  bundle_id INT NULL,
  credits INT NOT NULL,
  amount_cents INT DEFAULT 0,
  payment_ref VARCHAR(255) DEFAULT NULL,
  status ENUM('pending','completed','failed') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id)
);