

CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    instructor VARCHAR(100),
    date DATE,
    start_time TIME,
    duration INT, -- duration in minutes
    image VARCHAR(255), -- optional, for image filename or URL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE `packages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) COLLATE utf8mb4_general_ci NOT NULL,
  `description` VARCHAR(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `session_count` INT(11) NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL,
  `price_per_session` DECIMAL(10,2) NOT NULL,
  `validity_weeks` INT(11) NOT NULL,
  `is_private` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE user_packages (
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    package_id INT(11) NOT NULL,
    sessions_remaining INT(11) NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiry_date DATE DEFAULT NULL,
    status ENUM('active', 'expired', 'used') COLLATE utf8mb4_general_ci DEFAULT 'active',
    payment_method VARCHAR(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
    payment_status ENUM('pending', 'completed', 'failed') COLLATE utf8mb4_general_ci DEFAULT 'pending',
    transaction_id VARCHAR(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE bookings (
    id INT(11) NOT NULL AUTO_INCREMENT,
    class_id INT(11) NOT NULL,
    user_id INT(11) NULL,
    package_id INT(11) NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    booking_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL
);

