<?php
$servername = "localhost";
$username = "root";
$password = ""; // Default XAMPP password is empty
$dbname = "bm_business_db";

// 1. Connect without selecting database to CREATE it safely if missing
$temp_conn = new mysqli($servername, $username, $password);

if (!$temp_conn->connect_error) {
    // Ensure DB exists
    $temp_conn->query("CREATE DATABASE IF NOT EXISTS `$dbname`");
    $temp_conn->close();
}

// 2. Connect properly to the freshly created database
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// 3. Ensure pages table and other metadata tables exist before proceeding
$conn->query("CREATE TABLE IF NOT EXISTS pages (
    id INT(11) NOT NULL AUTO_INCREMENT,
    page_name VARCHAR(50) NOT NULL,
    title VARCHAR(255),
    content TEXT NOT NULL,
    PRIMARY KEY (id)
)");

$conn->query("CREATE TABLE IF NOT EXISTS services (id INT(11) NOT NULL AUTO_INCREMENT, title VARCHAR(100) NOT NULL, description TEXT NOT NULL, image_url VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id))");
$conn->query("CREATE TABLE IF NOT EXISTS admin_users (id INT(11) NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, password VARCHAR(255) NOT NULL, email VARCHAR(100) DEFAULT NULL, reset_token VARCHAR(128) DEFAULT NULL, reset_token_expiry DATETIME DEFAULT NULL, PRIMARY KEY (id))");
// Add columns to existing admin_users if they don't exist (safe on every run)
$res_cols = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'admin_users'");
$existing_cols = [];
if ($res_cols) { while ($col = $res_cols->fetch_assoc()) { $existing_cols[] = $col['COLUMN_NAME']; } }
if (!in_array('email',              $existing_cols)) { $conn->query("ALTER TABLE admin_users ADD COLUMN email VARCHAR(100) DEFAULT NULL"); }
if (!in_array('reset_token',        $existing_cols)) { $conn->query("ALTER TABLE admin_users ADD COLUMN reset_token VARCHAR(128) DEFAULT NULL"); }
if (!in_array('reset_token_expiry', $existing_cols)) { $conn->query("ALTER TABLE admin_users ADD COLUMN reset_token_expiry DATETIME DEFAULT NULL"); }
$conn->query("CREATE TABLE IF NOT EXISTS blogs (id INT(11) NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, content TEXT NOT NULL, image_url VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id))");
$conn->query("CREATE TABLE IF NOT EXISTS contact_messages (id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, message TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id))");
$conn->query("CREATE TABLE IF NOT EXISTS job_applications (
    id INT(11) NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL,
    address TEXT,
    district VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(10),
    resume_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)");

$conn->query("CREATE TABLE IF NOT EXISTS customers (
    id INT(11) NOT NULL AUTO_INCREMENT,
    company_name VARCHAR(255) NOT NULL,
    description TEXT,
    industry VARCHAR(100),
    website_url VARCHAR(255),
    logo_url VARCHAR(255),
    status ENUM('active', 'inactive') DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)");

$conn->query("CREATE TABLE IF NOT EXISTS companies (
    id INT(11) NOT NULL AUTO_INCREMENT,
    company_name VARCHAR(255) NOT NULL,
    legal_name VARCHAR(255) DEFAULT NULL,
    short_name VARCHAR(100) DEFAULT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    banner VARCHAR(255) DEFAULT NULL,
    description TEXT,
    about_company TEXT,
    industry VARCHAR(100) DEFAULT NULL,
    company_type VARCHAR(100) DEFAULT NULL,
    year_established VARCHAR(50) DEFAULT NULL,
    registration_no VARCHAR(100) DEFAULT NULL,
    cin VARCHAR(100) DEFAULT NULL,
    gst_no VARCHAR(100) DEFAULT NULL,
    pan VARCHAR(100) DEFAULT NULL,
    tan VARCHAR(100) DEFAULT NULL,
    website_url VARCHAR(255) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    address TEXT,
    city VARCHAR(100) DEFAULT NULL,
    state VARCHAR(100) DEFAULT NULL,
    country VARCHAR(100) DEFAULT NULL,
    pincode VARCHAR(20) DEFAULT NULL,
    business_details TEXT,
    services TEXT,
    products TEXT,
    experience VARCHAR(100) DEFAULT NULL,
    employees VARCHAR(100) DEFAULT NULL,
    projects_completed VARCHAR(100) DEFAULT NULL,
    turnover VARCHAR(100) DEFAULT NULL,
    certifications TEXT,
    awards TEXT,
    display_order INT DEFAULT 0,
    status ENUM('Published', 'Draft', 'Inactive') DEFAULT 'Published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)");

$conn->query("CREATE TABLE IF NOT EXISTS founders (
    id INT(11) NOT NULL AUTO_INCREMENT,
    company_id INT(11) NOT NULL,
    name VARCHAR(255) NOT NULL,
    photo VARCHAR(255) DEFAULT NULL,
    designation VARCHAR(100) DEFAULT NULL,
    short_bio TEXT,
    biography TEXT,
    experience VARCHAR(100) DEFAULT NULL,
    education VARCHAR(255) DEFAULT NULL,
    linkedin_url VARCHAR(255) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    display_order INT DEFAULT 0,
    status ENUM('Published', 'Draft', 'Inactive') DEFAULT 'Published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY (company_id)
)");

$conn->query("CREATE TABLE IF NOT EXISTS directors (
    id INT(11) NOT NULL AUTO_INCREMENT,
    company_id INT(11) NOT NULL,
    name VARCHAR(255) NOT NULL,
    photo VARCHAR(255) DEFAULT NULL,
    designation VARCHAR(100) DEFAULT NULL,
    director_type VARCHAR(100) DEFAULT NULL,
    short_bio TEXT,
    biography TEXT,
    education VARCHAR(255) DEFAULT NULL,
    experience VARCHAR(100) DEFAULT NULL,
    linkedin_url VARCHAR(255) DEFAULT NULL,
    display_order INT DEFAULT 0,
    status ENUM('Published', 'Draft', 'Inactive') DEFAULT 'Published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY (company_id)
)");

// 4. Insert default home_intro row if empty
$check_home = $conn->query("SELECT id FROM pages WHERE page_name='home_intro'");
if ($check_home && $check_home->num_rows == 0) {
    $conn->query("INSERT INTO pages (page_name, title, content) VALUES ('home_intro', 'Welcome to BALIMEEN', '“Driven by our mission to build responsibly and serve with integrity, Balimeen Private Limited provides efficient, high-quality construction services...”')");
}
$check_about = $conn->query("SELECT id FROM pages WHERE page_name='about_us'");
if ($check_about && $check_about->num_rows == 0) {
    $conn->query("INSERT INTO pages (page_name, title, content) VALUES ('about_us', 'About Us', 'Write your About Us content here.')");
}
$check_services_page = $conn->query("SELECT id FROM pages WHERE page_name='our_services'");
if ($check_services_page && $check_services_page->num_rows == 0) {
    $conn->query("INSERT INTO pages (page_name, title, content) VALUES ('our_services', 'Our Services', 'Write about your services here.')");
}
$check_clients = $conn->query("SELECT id FROM pages WHERE page_name='our_clients'");
if ($check_clients && $check_clients->num_rows == 0) {
    $conn->query("INSERT INTO pages (page_name, title, content) VALUES ('our_clients', 'Our Clients', 'Write about your clients here.')");
}
?>
