<?php
require_once 'config/database.php';

try {
    $database = new Database();
    $db = $database->getConnection();
    echo "Connected to database.\n";

    // 0. Handle potential migration from owner_id to user_id in guesthouses
    try {
        $stmt = $db->query("SHOW COLUMNS FROM guesthouses LIKE 'owner_id'");
        if ($stmt->rowCount() > 0) {
            echo "Found owner_id column. Checking if user_id exists...\n";
            $stmtUser = $db->query("SHOW COLUMNS FROM guesthouses LIKE 'user_id'");
            if ($stmtUser->rowCount() == 0) {
                echo "Renaming owner_id to user_id...\n";
                $db->exec("ALTER TABLE guesthouses CHANGE owner_id user_id INT NOT NULL");
                echo "Renamed successfully.\n";
            } else {
                echo "Both owner_id and user_id exist. Making owner_id optional to avoid errors...\n";
                $db->exec("ALTER TABLE guesthouses MODIFY owner_id INT NULL DEFAULT NULL");
            }
        }
    } catch (PDOException $e) {
        echo "Error checking/renaming owner_id: " . $e->getMessage() . "\n";
    }

    // 1. Fix guesthouses table
    $guesthouse_columns = [
        'user_id' => "INT NOT NULL DEFAULT 1",
        'accommodation_type' => "VARCHAR(50) DEFAULT 'Guest House'",
        'responsible_name' => "VARCHAR(255) DEFAULT NULL",
        'city' => "VARCHAR(100) DEFAULT NULL",
        'neighborhood' => "VARCHAR(100) DEFAULT NULL",
        'gps_coordinates' => "VARCHAR(255) DEFAULT NULL",
        'total_rooms' => "INT DEFAULT NULL",
        'capacity' => "INT DEFAULT NULL",
        'price_per_hour' => "DECIMAL(10,2) DEFAULT NULL",
        'price_per_2_hours' => "DECIMAL(10,2) DEFAULT NULL",
        'price_per_night' => "DECIMAL(10,2) DEFAULT NULL",
        'accepts_mpesa' => "TINYINT(1) DEFAULT 0",
        'accepts_emola' => "TINYINT(1) DEFAULT 0",
        'accepts_visa' => "TINYINT(1) DEFAULT 0",
        'preferred_contact_method' => "VARCHAR(50) DEFAULT 'Telefone'",
        'accepts_online_reserves' => "TINYINT(1) DEFAULT 0",
        'accepts_commission' => "TINYINT(1) DEFAULT 0",
        'has_wifi' => "TINYINT(1) DEFAULT 0",
        'has_parking' => "TINYINT(1) DEFAULT 0",
        'has_ac' => "TINYINT(1) DEFAULT 0",
        'has_security' => "TINYINT(1) DEFAULT 0",
        'has_cleaning' => "TINYINT(1) DEFAULT 0",
        'name' => "VARCHAR(255) NOT NULL",
        'phone' => "VARCHAR(50) NOT NULL",
        'email' => "VARCHAR(100) DEFAULT NULL",
        'address' => "TEXT DEFAULT NULL",
        'price' => "DECIMAL(10,2) DEFAULT 0.00",
        'description' => "TEXT DEFAULT NULL",
        'created_at' => "TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
    ];

    foreach ($guesthouse_columns as $column => $definition) {
        try {
            $stmt = $db->query("SHOW COLUMNS FROM guesthouses LIKE '$column'");
            if ($stmt->rowCount() == 0) {
                echo "Adding $column to guesthouses...\n";
                $db->exec("ALTER TABLE guesthouses ADD COLUMN $column $definition");
                echo "Added $column.\n";
            } else {
                echo "Updating/Standardizing $column in guesthouses...\n";
                $db->exec("ALTER TABLE guesthouses MODIFY COLUMN $column $definition");
                echo "Updated $column.\n";
            }
        } catch (PDOException $e) {
            echo "Error fixing $column in guesthouses: " . $e->getMessage() . "\n";
        }
    }

    // 2. Fix users table (Missing fcm_token/push_enabled causes 'Invalid response' in Registration)
    try {
        $stmt = $db->query("SHOW COLUMNS FROM users LIKE 'fcm_token'");
        if ($stmt->rowCount() == 0) {
            echo "Adding fcm_token to users...\n";
            $db->exec("ALTER TABLE users ADD COLUMN fcm_token VARCHAR(255) DEFAULT NULL");
            echo "Added fcm_token.\n";
        } else {
            echo "Column fcm_token already exists in users.\n";
        }
    } catch (PDOException $e) {
        echo "Error updating users fcm_token: " . $e->getMessage() . "\n";
    }

    try {
        $stmt = $db->query("SHOW COLUMNS FROM users LIKE 'push_enabled'");
        if ($stmt->rowCount() == 0) {
            echo "Adding push_enabled to users...\n";
            $db->exec("ALTER TABLE users ADD COLUMN push_enabled TINYINT(1) DEFAULT 1");
            echo "Added push_enabled.\n";
        } else {
            echo "Column push_enabled already exists in users.\n";
        }
    } catch (PDOException $e) {
        echo "Error updating users push_enabled: " . $e->getMessage() . "\n";
    }

    // 3. Ensure reviews table exists (often missing in initial setup)
    try {
        $db->exec("CREATE TABLE IF NOT EXISTS reviews (
            id INT AUTO_INCREMENT PRIMARY KEY,
            guesthouse_id INT NOT NULL,
            user_id INT NOT NULL,
            booking_id INT DEFAULT NULL,
            rating INT NOT NULL,
            comment TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (guesthouse_id) REFERENCES guesthouses(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        echo "Ensured reviews table exists.\n";
    } catch (PDOException $e) {
        echo "Error creating reviews table: " . $e->getMessage() . "\n";
    }

    // 4. Ensure user role column exists and defaults to guest
    try {
        $stmt = $db->query("SHOW COLUMNS FROM users LIKE 'role'");
        if ($stmt->rowCount() == 0) {
            echo "Adding role to users...\n";
            $db->exec("ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'guest'");
            echo "Added role.\n";
        }
    } catch (PDOException $e) {
        echo "Error adding role to users: " . $e->getMessage() . "\n";
    }

    // 5. Ensure guesthouse_photos table exists
    try {
        $db->exec("CREATE TABLE IF NOT EXISTS guesthouse_photos (
            id INT AUTO_INCREMENT PRIMARY KEY,
            guesthouse_id INT NOT NULL,
            photo_url VARCHAR(255) NOT NULL,
            is_main TINYINT(1) DEFAULT 0,
            uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (guesthouse_id) REFERENCES guesthouses(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        echo "Ensured guesthouse_photos table exists.\n";
    } catch (PDOException $e) {
        echo "Error creating guesthouse_photos table: " . $e->getMessage() . "\n";
    }

    // 6. Ensure profile_photo column exists in users
    try {
        $stmt = $db->query("SHOW COLUMNS FROM users LIKE 'profile_photo'");
        if ($stmt->rowCount() == 0) {
            echo "Adding profile_photo to users...\n";
            $db->exec("ALTER TABLE users ADD COLUMN profile_photo VARCHAR(255) DEFAULT NULL");
            echo "Added profile_photo.\n";
        }
    } catch (PDOException $e) {
        echo "Error adding profile_photo to users: " . $e->getMessage() . "\n";
    }

    // 7. Ensure favorites table exists
    try {
        $db->exec("CREATE TABLE IF NOT EXISTS favorites (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            guesthouse_id INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (guesthouse_id) REFERENCES guesthouses(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        echo "Ensured favorites table exists.\n";
    } catch (PDOException $e) {
        echo "Error creating favorites table: " . $e->getMessage() . "\n";
    }

    // 8. Ensure notifications table exists
    try {
        $db->exec("CREATE TABLE IF NOT EXISTS notifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            title VARCHAR(255) NOT NULL,
            message TEXT NOT NULL,
            type VARCHAR(50) DEFAULT 'general',
            related_id INT DEFAULT NULL,
            is_read TINYINT(1) DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
        echo "Ensured notifications table exists.\n";
    } catch (PDOException $e) {
        echo "Error creating notifications table: " . $e->getMessage() . "\n";
    }

    echo "Schema fix completed successfully.\n";

} catch (Exception $e) {
    echo "Critical Error: " . $e->getMessage() . "\n";
}
?>