<?php
// Database Setup Script for Consultations Table
// Run this file once to create the consultations table

// Database Configuration
$db_host = 'localhost';
$db_user = 'zemaraim';
$db_password = '8wwxk]-X3Kx]CJ]5';
$db_name = 'zemaraim_interior';

// Connect without database first
$connection = new mysqli($db_host, $db_user, $db_password);

if ($connection->connect_error) {
    die(json_encode([
        'status' => 'error',
        'message' => 'Connection failed: ' . $connection->connect_error
    ]));
}

// Try to create database if it doesn't exist
$connection->query("CREATE DATABASE IF NOT EXISTS `$db_name`");

// Select the database
$connection->select_db($db_name);

if ($connection->connect_error) {
    die(json_encode([
        'status' => 'error',
        'message' => 'Could not select database: ' . $connection->connect_error
    ]));
}

try {
    // Check if consultations table exists
    $result = $connection->query("SHOW TABLES LIKE 'consultations'");
    
    if ($result->num_rows > 0) {
        echo json_encode([
            'status' => 'info',
            'message' => 'Consultations table already exists'
        ]);
        exit;
    }
    
    // Create consultations table
    $sql = "
    CREATE TABLE consultations (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT,
        first_name VARCHAR(100) NOT NULL,
        last_name VARCHAR(100) NOT NULL,
        email VARCHAR(255) NOT NULL,
        phone VARCHAR(20) NOT NULL,
        address TEXT NOT NULL,
        consultation_type VARCHAR(50) NOT NULL,
        preferred_date DATE NOT NULL,
        preferred_time TIME NOT NULL,
        project_description TEXT NOT NULL,
        budget_range VARCHAR(50) NOT NULL,
        source VARCHAR(50),
        verification_token VARCHAR(255),
        token_expiry DATETIME,
        status ENUM('pending', 'verified', 'scheduled', 'completed', 'cancelled') DEFAULT 'pending',
        verified_at DATETIME,
        scheduled_at DATETIME,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_status (status),
        INDEX idx_email (email),
        INDEX idx_verification_token (verification_token)
    )
    ";
    
    if ($connection->query($sql) === TRUE) {
        echo json_encode([
            'status' => 'success',
            'message' => 'Consultations table created successfully'
        ]);
    } else {
        throw new Exception("Error creating table: " . $connection->error);
    }
    
} catch (Exception $e) {
    echo json_encode([
        'status' => 'error',
        'message' => $e->getMessage()
    ]);
} finally {
    $connection->close();
}
?>
