<?php
// db/setup.php
require_once __DIR__ . '/config.php';

echo "Starting database setup...\n";

try {
    $pdo = db();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 1. Users Table
    $sql_users = "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL,
        role ENUM('client', 'companion') NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $pdo->exec($sql_users);
    echo "- Table 'users' is ready.\n";

    // 2. User Profiles Table
    $sql_profiles = "CREATE TABLE IF NOT EXISTS user_profiles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL UNIQUE,
        name VARCHAR(100) NOT NULL,
        age INT,
        city VARCHAR(100),
        bio TEXT,
        profile_photo_path VARCHAR(255) DEFAULT 'assets/images/avatar_placeholder.svg',
        hourly_rate DECIMAL(10, 2),
        is_verified TINYINT(1) DEFAULT 0,
        rating DECIMAL(3, 2) DEFAULT 5.00,
        activities TEXT, -- Can store comma-separated values or JSON
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )";
    $pdo->exec($sql_profiles);
    echo "- Table 'user_profiles' is ready.\n";

    // 3. Swipes Table
    $sql_swipes = "CREATE TABLE IF NOT EXISTS swipes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        swiper_user_id INT NOT NULL,
        swiped_user_id INT NOT NULL,
        swipe_type ENUM('like', 'dislike') NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (swiper_user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (swiped_user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY (swiper_user_id, swiped_user_id)
    )";
    $pdo->exec($sql_swipes);
    echo "- Table 'swipes' is ready.\n";

    // 4. Matches Table
    $sql_matches = "CREATE TABLE IF NOT EXISTS matches (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user1_id INT NOT NULL,
        user2_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user1_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (user2_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY (user1_id, user2_id)
    )";
    $pdo->exec($sql_matches);
    echo "- Table 'matches' is ready.\n";


    // -- SEEDING DATA (if tables are empty) --
    $stmt = $pdo->query("SELECT COUNT(*) FROM users");
    if ($stmt->fetchColumn() == 0) {
        echo "Seeding initial data...\n";
        
        // Hash for a default password, e.g., 'password123'
        $default_password_hash = password_hash('password123', PASSWORD_DEFAULT);

        // Companions
        $companions_to_seed = [
            [1, 'sofia@email.com', 'Sofia', 28, 'San José', 45.00, 'Art lover and coffee enthusiast. I know the best hidden cafes in Amón.', 1, 4.9, 'assets/images/avatar_placeholder.svg'],
            [2, 'mateo@email.com', 'Mateo', 32, 'San José', 50.00, 'Local history buff. Let\'s walk through the city center.', 1, 4.8, 'assets/images/avatar_placeholder.svg'],
            [3, 'elena@email.com', 'Elena', 25, 'Escazú', 60.00, 'Foodie and wine taster. Available for elegant dinners.', 1, 5.0, 'assets/images/avatar_placeholder.svg'],
            [4, 'alejandro@email.com', 'Alejandro', 29, 'San Pedro', 40.00, 'Musician and movie buff. Great for concerts.', 0, 4.7, 'assets/images/avatar_placeholder.svg']
        ];

        $user_sql = "INSERT INTO users (id, email, password_hash, role) VALUES (?, ?, ?, 'companion')";
        $profile_sql = "INSERT INTO user_profiles (user_id, name, age, city, hourly_rate, bio, is_verified, rating, profile_photo_path) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $user_stmt = $pdo->prepare($user_sql);
        $profile_stmt = $pdo->prepare($profile_sql);

        foreach ($companions_to_seed as $c) {
            $user_stmt->execute([$c[0], $c[1], $default_password_hash]);
            $profile_stmt->execute([$c[0], $c[2], $c[3], $c[4], $c[5], $c[6], $c[7], $c[8], $c[9]]);
        }

        // A client user for testing
        $client_user_sql = "INSERT INTO users (email, password_hash, role) VALUES ('client@email.com', ?, 'client')";
        $pdo->prepare($client_user_sql)->execute([$default_password_hash]);
        $client_user_id = $pdo->lastInsertId();

        $client_profile_sql = "INSERT INTO user_profiles (user_id, name, age, city, bio) VALUES (?, 'Test Client', 30, 'San José', 'Looking for great company.')";
        $pdo->prepare($client_profile_sql)->execute([$client_user_id]);

        echo "- Seeded companion and client users.\n";

    } else {
        echo "- Users table already has data. Skipping seed.\n";
    }

    echo "Database setup completed successfully!\n";

} catch (PDOException $e) {
    die("DB Setup Error: " . $e->getMessage() . "\n");
}