<?php
mysqli_report(MYSQLI_REPORT_OFF);

$is_localhost = (
    (isset($_SERVER['SERVER_NAME']) && $_SERVER['SERVER_NAME'] == 'localhost') ||
    (isset($_SERVER['SERVER_ADDR']) && $_SERVER['SERVER_ADDR'] == '127.0.0.1') ||
    (isset($_SERVER['HTTP_HOST']) && strpos($_SERVER['HTTP_HOST'], '127.0.0.1') !== false)
);

if ($is_localhost) {
    $host = "localhost";
    $username = "root";
    $password = "";
    $dbname = "school_db";
} else {
    // Online Server Credentials
    $host = "localhost"; 
    $username = "flightbo_school"; 
    $password = "flightbo_school"; 
    $dbname = "flightbo_school";
}

try {
    $conn = @new mysqli($host, $username, $password, $dbname);
    if ($conn->connect_error) {
        $db_connected = false;
    } else {
        $db_connected = true;
    }
} catch (Exception $e) {
    $db_connected = false;
}

if ($db_connected) {
    // Create admins table
    $conn->query("CREATE TABLE IF NOT EXISTS admins (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL
    )");

    // Create sliders table
    $conn->query("CREATE TABLE IF NOT EXISTS sliders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        image VARCHAR(255) NOT NULL,
        subtitle VARCHAR(100),
        title VARCHAR(255),
        description TEXT,
        link VARCHAR(255),
        status INT DEFAULT 1
    )");

    // Create gallery table
    $conn->query("CREATE TABLE IF NOT EXISTS gallery (
        id INT AUTO_INCREMENT PRIMARY KEY,
        image VARCHAR(255) NOT NULL,
        heading VARCHAR(255),
        status INT DEFAULT 1
    )");

    // Create board_members table
    $conn->query("CREATE TABLE IF NOT EXISTS board_members (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        designation VARCHAR(255) NOT NULL,
        image VARCHAR(255) NOT NULL,
        order_num INT DEFAULT 0,
        status INT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    $bm_check = $conn->query("SELECT id FROM board_members LIMIT 1");
    if ($bm_check && $bm_check->num_rows == 0) {
        $default_members = [
            ['Dr. Furkan Saifi', 'Secretary', 'secretary_furkan.webp', 1],
            ['Smt. Sashi Sagar', 'Registrar', 'president_sashi.webp', 2],
            ['Dr. Vinod Kothari', 'Exam Controller', 'exam_controller_vinod.webp', 3],
            ['Charan Singh', 'President', 'president_charan.webp', 4],
            ['Sanjay Kumar Verma', 'Joint Secretary', 'joint_secretary_sanjay.webp', 5],
            ['Arshi Saifi', 'Board Member', 'board_member_arshi.webp', 6],
            ['Adv. Abhishek Giri', 'Legal Advisor', 'adv_abhishek.jpg', 7],
        ];
        $bm_stmt = $conn->prepare("INSERT INTO board_members (name, designation, image, order_num) VALUES (?, ?, ?, ?)");
        foreach ($default_members as $m) {
            $bm_stmt->bind_param("sssi", $m[0], $m[1], $m[2], $m[3]);
            $bm_stmt->execute();
        }
    }

    // Create event_gallery table
    $conn->query("CREATE TABLE IF NOT EXISTS event_gallery (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        image VARCHAR(255) NOT NULL,
        order_num INT DEFAULT 0,
        status INT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    $eg_check = $conn->query("SELECT id FROM event_gallery LIMIT 1");
    if ($eg_check && $eg_check->num_rows == 0) {
        $default_events = [
            ['IBVTE Event 1', 'gov_gallery_1.webp', 1],
            ['IBVTE Event 2', 'gov_gallery_2.webp', 2],
            ['IBVTE Event 3', 'gov_gallery_3.webp', 3],
            ['IBVTE Event 4', 'gov_gallery_4.webp', 4],
            ['IBVTE Event 5', 'gov_gallery_5.webp', 5],
        ];
        $eg_stmt = $conn->prepare("INSERT INTO event_gallery (title, image, order_num) VALUES (?, ?, ?)");
        foreach ($default_events as $ev) {
            $eg_stmt->bind_param("ssi", $ev[0], $ev[1], $ev[2]);
            $eg_stmt->execute();
        }
    }

    // Create director_desk table
    $conn->query("CREATE TABLE IF NOT EXISTS director_desk (
        id INT AUTO_INCREMENT PRIMARY KEY,
        badge VARCHAR(100) DEFAULT 'DIRECTOR\'S DESK',
        title VARCHAR(255) DEFAULT 'From Director\'s Desk',
        subtitle TEXT,
        director_name VARCHAR(255) DEFAULT 'Dr. Furkan Saifi',
        designation VARCHAR(255) DEFAULT 'Principle',
        image VARCHAR(255) DEFAULT 'secretary_furkan.webp',
        quote TEXT,
        stamp_image VARCHAR(255) DEFAULT 'images/logo_bsps.png',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )");

    $dd_check = $conn->query("SELECT id FROM director_desk LIMIT 1");
    if ($dd_check && $dd_check->num_rows == 0) {
        $quote = "BS Public School & AH International School is committed to transforming education through modern curriculum, moral discipline, and holistic development to build real-world careers and empower students for future success.";
        $dd_stmt = $conn->prepare("INSERT INTO director_desk (badge, title, subtitle, director_name, designation, image, quote, stamp_image) VALUES ('DIRECTOR\'S DESK', 'From Director\'s Desk', 'Leadership vision and message for holistic education.', 'Dr. Furkan Saifi', 'Principle', 'secretary_furkan.webp', ?, 'images/logo_bsps.png')");
        $dd_stmt->bind_param("s", $quote);
        $dd_stmt->execute();
    }

    // Create our_schools table
    $conn->query("CREATE TABLE IF NOT EXISTS our_schools (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        tagline VARCHAR(255),
        description TEXT,
        badge VARCHAR(100),
        image VARCHAR(255) NOT NULL,
        explore_link VARCHAR(255) DEFAULT 'the-school.php',
        admission_link VARCHAR(255) DEFAULT 'admission-enquiry.php',
        order_num INT DEFAULT 0,
        status INT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    $os_check = $conn->query("SELECT id FROM our_schools LIMIT 1");
    if ($os_check && $os_check->num_rows == 0) {
        $default_schools = [
            [
                'B.S. Public School',
                'CBSE Pattern • Foundational Excellence (Nursery to 10th)',
                'Empowering young minds through comprehensive curriculum, disciplined environment, science & IT labs, and value-based education.',
                'Main Campus • Estd. 2005',
                'bs_public_school.webp',
                'the-school.php',
                'admission-enquiry.php',
                1
            ],
            [
                'A.H. International School',
                'World-Class Pedagogy • Smart Interactive Digital Campus',
                'State-of-the-art educational infrastructure cultivating global competence, critical thinking, sports training, and creative innovation.',
                'International Standard • Advanced Campus',
                'ah_international_school.webp',
                'facilities.php',
                'admission-enquiry.php',
                2
            ]
        ];
        $os_stmt = $conn->prepare("INSERT INTO our_schools (name, tagline, description, badge, image, explore_link, admission_link, order_num) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        foreach ($default_schools as $s) {
            $os_stmt->bind_param("sssssssi", $s[0], $s[1], $s[2], $s[3], $s[4], $s[5], $s[6], $s[7]);
            $os_stmt->execute();
        }
    }

    // Create leave_applications table
    $conn->query("CREATE TABLE IF NOT EXISTS leave_applications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        app_no VARCHAR(50) NOT NULL UNIQUE,
        school_name VARCHAR(100) NOT NULL DEFAULT 'B.S. Public School',
        student_name VARCHAR(255) NOT NULL,
        father_name VARCHAR(255) NOT NULL,
        class_name VARCHAR(50) NOT NULL,
        section VARCHAR(20) DEFAULT '',
        roll_no VARCHAR(50) DEFAULT '',
        from_date DATE NOT NULL,
        to_date DATE NOT NULL,
        total_days INT DEFAULT 1,
        reason_type VARCHAR(100) NOT NULL,
        reason_details TEXT,
        parent_mobile VARCHAR(20) NOT NULL,
        status VARCHAR(30) DEFAULT 'Pending',
        admin_remarks TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // Create tc_applications table
    $conn->query("CREATE TABLE IF NOT EXISTS tc_applications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        app_no VARCHAR(50) NOT NULL UNIQUE,
        school_name VARCHAR(100) NOT NULL DEFAULT 'B.S. Public School',
        student_name VARCHAR(255) NOT NULL,
        father_name VARCHAR(255) NOT NULL,
        class_name VARCHAR(50) NOT NULL,
        scholar_no VARCHAR(50) DEFAULT '',
        reason_for_leaving TEXT NOT NULL,
        parent_mobile VARCHAR(20) NOT NULL,
        status VARCHAR(30) DEFAULT 'Under Process',
        admin_remarks TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // Create admission_enquiries table
    $conn->query("CREATE TABLE IF NOT EXISTS admission_enquiries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_name VARCHAR(255) NOT NULL,
        parent_name VARCHAR(255) NOT NULL,
        grade VARCHAR(100) NOT NULL,
        phone VARCHAR(30) NOT NULL,
        email VARCHAR(100) DEFAULT '',
        address TEXT,
        status VARCHAR(30) DEFAULT 'New',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // Create important_sites table
    $conn->query("CREATE TABLE IF NOT EXISTS important_sites (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        url VARCHAR(255) NOT NULL DEFAULT '#',
        image VARCHAR(255) NOT NULL,
        order_num INT DEFAULT 0,
        status INT DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    $is_check = $conn->query("SELECT id FROM important_sites LIMIT 1");
    if ($is_check && $is_check->num_rows == 0) {
        $default_sites = [
            ['MAKE IN INDIA', 'https://www.makeinindia.com/', 'site_makeinindia.png', 1],
            ['RTI ONLINE', 'https://rtionline.gov.in/', 'site_rti.png', 2],
            ['FIT INDIA', 'https://fitindia.gov.in/', 'site_fitindia.jpg', 3],
            ['INDIA SERVICES', 'https://services.india.gov.in/', 'site_indiaservices.png', 4],
            ['IGOD', 'https://igod.gov.in/', 'site_igod.png', 5],
            ['PM INDIA', 'https://www.pmindia.gov.in/en/', 'site_pmindia.png', 6],
            ['INDIA GOV', 'https://www.india.gov.in/', 'site_indiagov.png', 7],
            ['EBSB', 'https://dsel.education.gov.in/hi/ebsb', 'site_ebsb.jpg', 8]
        ];
        $is_stmt = $conn->prepare("INSERT INTO important_sites (title, url, image, order_num) VALUES (?, ?, ?, ?)");
        foreach ($default_sites as $s) {
            $is_stmt->bind_param("sssi", $s[0], $s[1], $s[2], $s[3]);
            $is_stmt->execute();
        }
    }

    // Insert default admin if not exists (password: admin123)
    $admin_check = $conn->query("SELECT * FROM admins WHERE username = 'admin'");
    if ($admin_check && $admin_check->num_rows == 0) {
        $pass = password_hash("admin123", PASSWORD_DEFAULT);
        $conn->query("INSERT INTO admins (username, password) VALUES ('admin', '$pass')");
    }
}
?>