-- NEXTLIFE.JP Database Schema
-- MySQL 5.0 Fully Compatible (NO utf8mb4, Single TIMESTAMP)
-- Created: 2024-12-17

-- ============================================================
-- Create database
-- ============================================================
CREATE DATABASE IF NOT EXISTS cs141040133 CHARACTER SET utf8 COLLATE utf8_general_ci;
USE cs141040133;

-- ============================================================
-- ==================== BUSINESS ENTITIES =====================
-- ============================================================

-- Tours
CREATE TABLE IF NOT EXISTS tours (
    tour_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT,
    price DECIMAL(10, 2) NOT NULL,
    capacity INT DEFAULT 0,
    max_bookings INT DEFAULT 0,
    image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_title (title),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Tour Dates
CREATE TABLE IF NOT EXISTS tour_dates (
    date_id INT PRIMARY KEY AUTO_INCREMENT,
    tour_id INT NOT NULL,
    date DATE NOT NULL,
    available_slots INT DEFAULT 0,
    booked_slots INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tour_id) REFERENCES tours(tour_id) ON DELETE CASCADE,
    INDEX idx_tour_id (tour_id),
    INDEX idx_date (date),
    UNIQUE KEY unique_tour_date (tour_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Vehicles
CREATE TABLE IF NOT EXISTS vehicles (
    vehicle_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT,
    year INT,
    make VARCHAR(100),
    model VARCHAR(100),
    fob_price DECIMAL(12, 2) NOT NULL,
    shipping_cost DECIMAL(10, 2) DEFAULT 0.00,
    status VARCHAR(50) DEFAULT 'available',
    image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_year (year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- ============================================================
-- ==================== BOOKING & PAYMENT =====================
-- ============================================================

-- Bookings
CREATE TABLE IF NOT EXISTS bookings (
    booking_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    type VARCHAR(50) NOT NULL COMMENT 'tour or vehicle',
    tour_id INT,
    vehicle_id INT,
    booking_date DATE,
    price DECIMAL(12, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    stripe_payment_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (tour_id) REFERENCES tours(tour_id) ON DELETE SET NULL,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_type (type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Cancellations
CREATE TABLE IF NOT EXISTS cancellations (
    cancellation_id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    reason VARCHAR(500),
    status VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, approved, rejected',
    refund_amount DECIMAL(12, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(booking_id) ON DELETE CASCADE,
    INDEX idx_booking_id (booking_id),
    INDEX idx_status (status),
    UNIQUE KEY unique_booking_cancellation (booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- ============================================================
-- ==================== CONTENT MANAGEMENT =====================
-- ============================================================

-- Blog Posts
CREATE TABLE IF NOT EXISTS blog_posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content LONGTEXT,
    excerpt VARCHAR(500),
    category VARCHAR(100),
    status VARCHAR(50) DEFAULT 'draft' COMMENT 'draft or published',
    author_id INT,
    image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES admin_users(admin_id) ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Contact Messages
CREATE TABLE IF NOT EXISTS contact_messages (
    message_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    subject VARCHAR(255),
    message LONGTEXT,
    status VARCHAR(50) DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Demo Tours
INSERT INTO tours (title, description, price, capacity, max_bookings, image) VALUES
('Drift Experience - Beginner', 'Learn the basics of drift driving in a safe and controlled environment', 299.99, 20, 20, 'drift-beginner.jpg'),
('Drift Experience - Advanced', 'Advanced drift techniques with professional instructors', 499.99, 15, 15, 'drift-advanced.jpg'),
('Tokyo Night Tour', 'Experience the Tokyo night scene with our professional drivers', 199.99, 25, 25, 'tokyo-night.jpg');

-- Demo Tour Dates
INSERT INTO tour_dates (tour_id, date, available_slots, booked_slots) VALUES
(1, '2024-12-25', 20, 0),
(1, '2024-12-26', 20, 0),
(2, '2024-12-28', 15, 0),
(3, '2024-12-24', 25, 0);

-- Demo Vehicles
INSERT INTO vehicles (title, description, year, make, model, fob_price, shipping_cost, status, image) VALUES
('Nissan Silvia S15', 'Legendary drift car, excellent condition', 2002, 'Nissan', 'Silvia S15', 15000.00, 2500.00, 'available', 'silvia-s15.jpg'),
('Toyota Supra JZA80', 'Twin turbo, 1000hp, immaculate', 1997, 'Toyota', 'Supra JZA80', 25000.00, 2500.00, 'available', 'supra.jpg'),
('Mazda RX-7 FD', 'Rotary engine, sports classic', 1992, 'Mazda', 'RX-7', 12000.00, 2000.00, 'available', 'rx7.jpg');

-- Demo Blog Post
INSERT INTO blog_posts (title, slug, content, excerpt, category, status, author_id, image) VALUES
('Welcome to NEXTLIFE', 'welcome-to-nextlife', '<h2>Welcome to NEXTLIFE</h2><p>We are your premier destination for JDM vehicles and drift experiences.</p>', 'Welcome to our blog', 'news', 'published', 1, 'blog-header.jpg');

-- ============================================================
-- ==================== VERIFY SCHEMA ===========================
-- ============================================================

-- Display all tables
SELECT 'Tables created:' AS status;
SHOW TABLES;

-- Display table structures
SELECT '=== users ===' AS '';
DESCRIBE users;

SELECT '=== admin_users ===' AS '';
DESCRIBE admin_users;

SELECT '=== tours ===' AS '';
DESCRIBE tours;

SELECT '=== vehicles ===' AS '';
DESCRIBE vehicles;

SELECT '=== bookings ===' AS '';
DESCRIBE bookings;
