<?php
$host = "localhost";
$dbname = "nextgens_clients_website"; 
$username = "nextgens_clients_websites"; 
$password = "RAHMATdewan..11";

// Create connection
$conn = new mysqli($host, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("<h3 style='color:red;'>Database Connection failed: " . $conn->connect_error . "</h3>");
}

echo "<h3>Database Connected Successfully!</h3>";

// Array of Table Creation Queries
$tables = [
    "agh_users" => "CREATE TABLE IF NOT EXISTS agh_users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        role ENUM('admin', 'officer') NOT NULL,
        full_name VARCHAR(100) NOT NULL
    )",
    
    "agh_patients" => "CREATE TABLE IF NOT EXISTS agh_patients (
        id INT AUTO_INCREMENT PRIMARY KEY,
        card_number VARCHAR(50) NOT NULL UNIQUE,
        mobile VARCHAR(15) NOT NULL,
        address TEXT,
        marketing_officer_id INT,
        reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (marketing_officer_id) REFERENCES agh_users(id) ON DELETE SET NULL
    )",
    
    "agh_family_members" => "CREATE TABLE IF NOT EXISTS agh_family_members (
        id INT AUTO_INCREMENT PRIMARY KEY,
        patient_id INT,
        member_name VARCHAR(100) NOT NULL,
        age INT NOT NULL,
        initial_problem TEXT,
        FOREIGN KEY (patient_id) REFERENCES agh_patients(id) ON DELETE CASCADE
    )",
    
    "agh_visits" => "CREATE TABLE IF NOT EXISTS agh_visits (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id INT,
        visit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        current_problem TEXT,
        FOREIGN KEY (member_id) REFERENCES agh_family_members(id) ON DELETE CASCADE
    )"
];

// Execute Table Queries
$all_success = true;
foreach ($tables as $name => $query) {
    if ($conn->query($query) === TRUE) {
        echo "<p style='color:green;'>Table <b>$name</b> created successfully (or already exists).</p>";
    } else {
        echo "<p style='color:red;'>Error creating table $name: " . $conn->error . "</p>";
        $all_success = false;
    }
}

// Insert Default Admin & Officer if tables created successfully
// Insert বা Update করার লজিক
if ($all_success) {
    // এখানে আপনার নতুন পাসওয়ার্ডটি দিন
    $new_admin_pass = password_hash('admin@321', PASSWORD_DEFAULT); 
    
    // পাসওয়ার্ড আপডেট করার কুয়েরি
    $update_admin = "UPDATE agh_users SET password = '$new_admin_pass' WHERE username = 'admin'";
    
    if ($conn->query($update_admin) === TRUE) {
        echo "<p style='color:blue; font-weight:bold;'>Admin password updated successfully!</p>";
        echo "<p><b>New Admin Login:</b> Username: admin | Password: admin@321</p>";
    } else {
        echo "<p style='color:red;'>Error updating password: " . $conn->error . "</p>";
    }
}

echo "<hr><p><b>Note:</b> Please delete this <b>setup.php</b> file after successful execution for security reasons.</p>";

$conn->close();
?>