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

$config = getDatabaseConfig();
$conn = createDatabaseConnection(false);
$dbname = $config['database'];

if ($conn->query("CREATE DATABASE IF NOT EXISTS {$dbname}") === TRUE) {
    echo 'Database created successfully<br>';
} else {
    echo 'Error creating database: ' . $conn->error . '<br>';
}

$conn->select_db($dbname);

$tables = [
    "CREATE TABLE IF NOT EXISTS contacts (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,email VARCHAR(100) NOT NULL,phone VARCHAR(20),subject VARCHAR(100),message TEXT NOT NULL,inquiry_status ENUM('new','reviewing','resolved','closed') DEFAULT 'new',admin_notes TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)" => 'Contacts table ready',
    "CREATE TABLE IF NOT EXISTS users (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,email VARCHAR(100) NOT NULL UNIQUE,phone VARCHAR(20),password VARCHAR(255) NOT NULL,user_type ENUM('resident','family','staff','admin') DEFAULT 'family',account_status ENUM('active','inactive') DEFAULT 'active',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)" => 'Users table ready',
    "CREATE TABLE IF NOT EXISTS services (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,description TEXT,category VARCHAR(50),price DECIMAL(10,2) NOT NULL,duration VARCHAR(50),image VARCHAR(255),active BOOLEAN DEFAULT TRUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)" => 'Services table ready',
    "CREATE TABLE IF NOT EXISTS bookings (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT(6) UNSIGNED,service_id INT(6) UNSIGNED,booking_date DATE NOT NULL,booking_time TIME,status ENUM('pending','confirmed','completed','cancelled') DEFAULT 'pending',notes TEXT,total_amount DECIMAL(10,2),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id),FOREIGN KEY (service_id) REFERENCES services(id))" => 'Bookings table ready',
    "CREATE TABLE IF NOT EXISTS payments (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,booking_id INT(6) UNSIGNED,user_id INT(6) UNSIGNED,amount DECIMAL(10,2) NOT NULL,payment_method VARCHAR(50),transaction_id VARCHAR(255),status ENUM('pending','completed','failed','refunded') DEFAULT 'pending',payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (booking_id) REFERENCES bookings(id),FOREIGN KEY (user_id) REFERENCES users(id))" => 'Payments table ready',
    "CREATE TABLE IF NOT EXISTS testimonials (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,relationship VARCHAR(50),testimonial TEXT NOT NULL,rating INT(1) DEFAULT 5,approved TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)" => 'Testimonials table ready',
    "CREATE TABLE IF NOT EXISTS admissions (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,resident_name VARCHAR(100) NOT NULL,resident_dob DATE NOT NULL,contact_name VARCHAR(100) NOT NULL,contact_relationship VARCHAR(100) NOT NULL,contact_phone VARCHAR(20) NOT NULL,contact_email VARCHAR(100) NOT NULL,preferred_move_date DATE NULL,care_level VARCHAR(100),special_needs TEXT,inquiry_status ENUM('new','reviewing','resolved','closed') DEFAULT 'new',admin_notes TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)" => 'Admissions table ready'
];

foreach ($tables as $sql => $message) {
    if ($conn->query($sql) === TRUE) {
        echo $message . '<br>';
    } else {
        echo 'Error: ' . $conn->error . '<br>';
    }
}

foreach ([
    "ALTER TABLE users MODIFY COLUMN user_type ENUM('resident','family','staff','admin') DEFAULT 'family'",
    "ALTER TABLE users ADD COLUMN account_status ENUM('active','inactive') DEFAULT 'active'",
    "ALTER TABLE contacts ADD COLUMN inquiry_status ENUM('new','reviewing','resolved','closed') DEFAULT 'new'",
    "ALTER TABLE contacts ADD COLUMN admin_notes TEXT NULL",
    "ALTER TABLE admissions ADD COLUMN inquiry_status ENUM('new','reviewing','resolved','closed') DEFAULT 'new'",
    "ALTER TABLE admissions ADD COLUMN admin_notes TEXT NULL",
    "ALTER TABLE testimonials ADD COLUMN approved TINYINT(1) DEFAULT 1"
] as $alter) {
    $conn->query($alter);
}

echo 'Schema sync completed<br>';

$sampleServices = [
    ["Personal Care Assistance", "Daily personal care including bathing, dressing, and grooming", "Personal Care", 75.00, "1 hour", "personal-care.jpg"],
    ["Medical Check-up", "Comprehensive health assessment by qualified medical professionals", "Medical", 150.00, "30 minutes", "medical-checkup.jpg"],
    ["Physical Therapy", "Individualized physical therapy sessions for mobility and strength", "Therapy", 100.00, "45 minutes", "physical-therapy.jpg"],
    ["Nutrition Consultation", "Personalized nutrition planning and dietary guidance", "Nutrition", 80.00, "1 hour", "nutrition.jpg"],
    ["Recreational Activity", "Group recreational activities and social engagement programs", "Activities", 25.00, "2 hours", "activities.jpg"],
    ["Housekeeping Services", "Professional cleaning and maintenance of living spaces", "Support", 50.00, "1 hour", "housekeeping.jpg"],
    ["Transportation Services", "Safe transportation to medical appointments and outings", "Transportation", 40.00, "per trip", "transportation.jpg"],
    ["Companionship Services", "One-on-one companionship and emotional support", "Support", 35.00, "1 hour", "companionship.jpg"]
];

$serviceCount = (int) $conn->query('SELECT COUNT(*) AS total FROM services')->fetch_assoc()['total'];
if ($serviceCount === 0) {
    $stmt = $conn->prepare('INSERT INTO services (name,description,category,price,duration,image) VALUES (?,?,?,?,?,?)');
    foreach ($sampleServices as $service) {
        $stmt->bind_param('sssdss', $service[0], $service[1], $service[2], $service[3], $service[4], $service[5]);
        $stmt->execute();
    }
    $stmt->close();
    echo 'Sample services inserted<br>';
} else {
    echo 'Sample services skipped because services already exist<br>';
}

$adminEmail = 'admin@goldenyears.com';
$adminPassword = 'Admin@123';
$adminName = 'Site Administrator';
$adminPhone = '(555) 123-4567';
$stmt = $conn->prepare('SELECT id FROM users WHERE email = ?');
$stmt->bind_param('s', $adminEmail);
$stmt->execute();
$exists = $stmt->get_result()->num_rows > 0;
$stmt->close();

if (!$exists) {
    $hashed = password_hash($adminPassword, PASSWORD_DEFAULT);
    $stmt = $conn->prepare("INSERT INTO users (name,email,phone,password,user_type,account_status) VALUES (?,?,?,?, 'admin','active')");
    $stmt->bind_param('ssss', $adminName, $adminEmail, $adminPhone, $hashed);
    if ($stmt->execute()) {
        echo 'Default admin account created<br>';
        echo 'Admin email: ' . htmlspecialchars($adminEmail) . '<br>';
        echo 'Admin password: ' . htmlspecialchars($adminPassword) . '<br>';
    } else {
        echo 'Error creating default admin account: ' . $stmt->error . '<br>';
    }
    $stmt->close();
} else {
    $escapedEmail = $conn->real_escape_string($adminEmail);
    $conn->query("UPDATE users SET user_type='admin', account_status='active' WHERE email='{$escapedEmail}'");
    echo 'Default admin account already exists and is active<br>';
}

$testimonialCount = (int) $conn->query('SELECT COUNT(*) AS total FROM testimonials')->fetch_assoc()['total'];
if ($testimonialCount === 0) {
    $approved = 1;
    $stmt = $conn->prepare('INSERT INTO testimonials (name,relationship,testimonial,rating,approved) VALUES (?,?,?,?,?)');
    $samples = [
        ['Mary Thompson', 'Daughter of Resident', 'The staff treats my mother like family. We finally feel at peace knowing she is in caring hands.', 5],
        ['Robert Hayes', 'Resident', 'I enjoy the activities, the meals, and the friendly atmosphere here every day.', 5]
    ];
    foreach ($samples as $row) {
        $stmt->bind_param('sssii', $row[0], $row[1], $row[2], $row[3], $approved);
        $stmt->execute();
    }
    $stmt->close();
    echo 'Sample testimonials inserted<br>';
}

$conn->close();
echo '<br>Database setup completed successfully! You can now use the website.';
?>