<?php
/**
 * Database Configuration
 * Zemaraim Real Estate Platform
 */

// Database credentials
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'Zemaraim_db');

// Create connection WITHOUT selecting database (for database creation)
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS);

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

// Create database if it doesn't exist
$create_db = "CREATE DATABASE IF NOT EXISTS " . DB_NAME;
if ($conn->query($create_db) === TRUE) {
    // Database created or already exists
} else {
    // Log error but don't die - database might already exist
}

// Now select the database
if (!$conn->select_db(DB_NAME)) {
    die("Database selection failed: " . $conn->error);
}

// Set charset to utf8
$conn->set_charset("utf8");

// Create mysqli alias for compatibility
$mysqli = $conn;

// Session configuration - MUST be before session_start()
if (!isset($_SESSION)) {
    ini_set('session.name', 'Zemaraim_session');
    ini_set('session.use_only_cookies', 1);
    if (session_status() === PHP_SESSION_NONE) {
        session_start();
    }
}

// Create tables if they don't exist

// Create users table for authentication
$create_users = "CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  role ENUM('customer', 'agent', 'broker', 'staff', 'admin') NOT NULL DEFAULT 'customer',
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role (role)
)";
$conn->query($create_users);

$create_customers = "CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(100) NOT NULL,
  lastname VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  address VARCHAR(255),
  city VARCHAR(100),
  role VARCHAR(50) DEFAULT 'customer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($create_customers);

$create_brokers = "CREATE TABLE IF NOT EXISTS brokers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  company VARCHAR(100),
  address VARCHAR(255),
  city VARCHAR(100),
  role VARCHAR(50) DEFAULT 'broker',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($create_brokers);

$create_staff = "CREATE TABLE IF NOT EXISTS staff (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  position VARCHAR(100),
  role VARCHAR(50) DEFAULT 'staff',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($create_staff);

$create_properties = "CREATE TABLE IF NOT EXISTS properties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  price INT NOT NULL,
  type VARCHAR(50) NOT NULL,
  status VARCHAR(50) DEFAULT 'active',
  address VARCHAR(255) NOT NULL,
  city VARCHAR(100) NOT NULL,
  state VARCHAR(100),
  zip_code VARCHAR(20),
  bedrooms INT,
  bathrooms INT,
  garage INT DEFAULT 0,
  area INT,
  broker_id INT,
  agent_id INT,
  image_url VARCHAR(500),
  gallery VARCHAR(2000),
  floor_plan VARCHAR(500),
  video_url VARCHAR(500),
  latitude DECIMAL(10, 8),
  longitude DECIMAL(10, 8),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(broker_id) REFERENCES users(id)
)";
$conn->query($create_properties);

// Ensure properties table has agent_id column
$conn->query("ALTER TABLE properties ADD COLUMN IF NOT EXISTS agent_id INT AFTER broker_id");

$create_newsletter = "CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
$conn->query($create_newsletter);

// Create password reset OTP table
$create_otp = "CREATE TABLE IF NOT EXISTS password_resets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  otp VARCHAR(6) NOT NULL,
  is_used BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP NULL,
  verified_at TIMESTAMP NULL,
  INDEX idx_email (email),
  INDEX idx_otp (otp)
)";
$conn->query($create_otp);

$create_appointments = "CREATE TABLE IF NOT EXISTS appointments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  customer_id INT,
  property_id INT,
  appointment_date DATE NOT NULL,
  date DATE,
  time TIME NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(customer_id) REFERENCES customers(id),
  FOREIGN KEY(property_id) REFERENCES properties(id)
)";
$conn->query($create_appointments);

// Add user_id column to appointments table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'user_id'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE appointments ADD COLUMN user_id INT AFTER id");
}

// Add customer_id column to appointments table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'customer_id'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE appointments ADD COLUMN customer_id INT AFTER user_id");
}

// Add property_id column to appointments table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'property_id'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE appointments ADD COLUMN property_id INT AFTER customer_id");
}

// Add appointment_date column to appointments table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'appointment_date'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE appointments ADD COLUMN appointment_date DATE AFTER property_id");
}

// Add appointment_time column to appointments table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'appointment_time'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE appointments ADD COLUMN appointment_time TIME AFTER appointment_date");
}

// Add status column to appointments table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'status'");
if ($result && $result->num_rows === 1) {
    // Check if status column definition is correct
}

// Add message column to appointments table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM appointments LIKE 'message'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE appointments ADD COLUMN message TEXT AFTER status");
}

$create_inquiries = "CREATE TABLE IF NOT EXISTS inquiries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  property_id INT,
  assigned_to INT,
  broker_id INT,
  status VARCHAR(50) DEFAULT 'pending',
  message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY(customer_id) REFERENCES customers(id),
  FOREIGN KEY(property_id) REFERENCES properties(id),
  FOREIGN KEY(broker_id) REFERENCES users(id)
)";
$conn->query($create_inquiries);

// Add missing columns to inquiries table if they don't exist
$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'customer_id'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN customer_id INT AFTER id");
}

$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'property_id'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN property_id INT AFTER customer_id");
}

$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'assigned_to'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN assigned_to INT AFTER property_id");
}

$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'broker_id'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN broker_id INT AFTER assigned_to");
}

$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'status'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN status VARCHAR(50) DEFAULT 'pending' AFTER broker_id");
}

$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'message'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN message TEXT AFTER status");
}

// Add customer contact columns to inquiries table if they don't exist
$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'customer_name'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN customer_name VARCHAR(255) AFTER customer_id");
}

$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'customer_email'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN customer_email VARCHAR(255) AFTER customer_name");
}

$result = $conn->query("SHOW COLUMNS FROM inquiries LIKE 'customer_phone'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE inquiries ADD COLUMN customer_phone VARCHAR(20) AFTER customer_email");
}

$create_reviews = "CREATE TABLE IF NOT EXISTS reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  property_id INT,
  rating INT,
  comment TEXT,
  customer_name VARCHAR(255),
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(customer_id) REFERENCES customers(id),
  FOREIGN KEY(property_id) REFERENCES properties(id)
)";
$conn->query($create_reviews);

// Add status column to reviews table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM reviews LIKE 'status'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE reviews ADD COLUMN status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' AFTER comment");
}

// Add customer_name column to reviews table if it doesn't exist
$result = $conn->query("SHOW COLUMNS FROM reviews LIKE 'customer_name'");
if ($result && $result->num_rows === 0) {
    $conn->query("ALTER TABLE reviews ADD COLUMN customer_name VARCHAR(255) AFTER rating");
}

$create_services = "CREATE TABLE IF NOT EXISTS services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  short_description TEXT NOT NULL,
  full_description TEXT,
  icon VARCHAR(100),
  image_url VARCHAR(500),
  benefits TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($create_services);

$create_hero_sliders = "CREATE TABLE IF NOT EXISTS hero_sliders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tag VARCHAR(100),
  title VARCHAR(255) NOT NULL,
  subtitle TEXT,
  button1_text VARCHAR(100),
  button1_url VARCHAR(255),
  button2_text VARCHAR(100),
  button2_url VARCHAR(255),
  image_url VARCHAR(500),
  stat1_icon VARCHAR(100),
  stat1_text VARCHAR(255),
  stat2_icon VARCHAR(100),
  stat2_text VARCHAR(255),
  display_order INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($create_hero_sliders);

// Add missing columns to hero_sliders table if they don't exist
$columns_hero = [
    'tag' => "ALTER TABLE hero_sliders ADD COLUMN tag VARCHAR(100) AFTER id",
    'title' => "ALTER TABLE hero_sliders ADD COLUMN title VARCHAR(255) AFTER tag",
    'subtitle' => "ALTER TABLE hero_sliders ADD COLUMN subtitle TEXT AFTER title",
    'button1_text' => "ALTER TABLE hero_sliders ADD COLUMN button1_text VARCHAR(100) AFTER subtitle",
    'button1_url' => "ALTER TABLE hero_sliders ADD COLUMN button1_url VARCHAR(255) AFTER button1_text",
    'button2_text' => "ALTER TABLE hero_sliders ADD COLUMN button2_text VARCHAR(100) AFTER button1_url",
    'button2_url' => "ALTER TABLE hero_sliders ADD COLUMN button2_url VARCHAR(255) AFTER button2_text",
    'image_url' => "ALTER TABLE hero_sliders ADD COLUMN image_url VARCHAR(500) AFTER button2_url",
    'stat1_icon' => "ALTER TABLE hero_sliders ADD COLUMN stat1_icon VARCHAR(100) AFTER image_url",
    'stat1_text' => "ALTER TABLE hero_sliders ADD COLUMN stat1_text VARCHAR(255) AFTER stat1_icon",
    'stat2_icon' => "ALTER TABLE hero_sliders ADD COLUMN stat2_icon VARCHAR(100) AFTER stat1_text",
    'stat2_text' => "ALTER TABLE hero_sliders ADD COLUMN stat2_text VARCHAR(255) AFTER stat2_icon",
    'display_order' => "ALTER TABLE hero_sliders ADD COLUMN display_order INT DEFAULT 0 AFTER stat2_text",
    'status' => "ALTER TABLE hero_sliders ADD COLUMN status ENUM('active', 'inactive') DEFAULT 'active' AFTER display_order"
];

foreach ($columns_hero as $col_name => $alter_query) {
    $result = $conn->query("SHOW COLUMNS FROM hero_sliders LIKE '$col_name'");
    if (!$result || $result->num_rows === 0) {
        @$conn->query($alter_query);
    }
}

$create_blog = "CREATE TABLE IF NOT EXISTS blog_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  author_id INT,
  featured_image VARCHAR(500),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($create_blog);

$create_agents = "CREATE TABLE IF NOT EXISTS agents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  position VARCHAR(100) NOT NULL,
  email VARCHAR(100),
  phone VARCHAR(20),
  specialization VARCHAR(255),
  experience INT DEFAULT 0,
  image_url VARCHAR(500),
  bio TEXT,
  properties_sold INT DEFAULT 0,
  rating DECIMAL(3, 2) DEFAULT 0,
  facebook_url VARCHAR(500),
  twitter_url VARCHAR(500),
  linkedin_url VARCHAR(500),
  instagram_url VARCHAR(500),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
$conn->query($create_agents);

$create_contact = "CREATE TABLE IF NOT EXISTS contact_inquiries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  subject VARCHAR(255),
  property_type VARCHAR(100),
  message TEXT NOT NULL,
  status VARCHAR(50) DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
$conn->query($create_contact);

// Insert sample services if table is empty
$check_services = $conn->query("SELECT COUNT(*) as count FROM services");
$row_services = $check_services->fetch_assoc();
if ($row_services['count'] == 0) {
    $sample_services = [
        "('Property Buying Assistance', 'Expert guidance in finding and purchasing the perfect property', 'Our team provides comprehensive property buying assistance to help you find verified and legally approved properties that match your requirements. We handle property searches, market analysis, negotiation, and documentation.', 'fas fa-home', 'assets/img/service/buying.jpg', 'Expert market analysis, Property verification, Legal consultation, Financing guidance, Portfolio building', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Property Selling Support', 'Professional marketing and pricing strategy for fast selling', 'We provide end-to-end property selling support with professional marketing strategies. Our experts handle property valuation, attractive listing creation, buyer matching, and negotiation to ensure you get the best price.', 'fas fa-tag', 'assets/img/service/selling.jpg', 'Property valuation, Professional photography, Targeted marketing, Buyer screening, Price optimization', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Rental Property Management', 'End-to-end rental management and tenant verification', 'Complete rental property management services including tenant screening, rent collection, maintenance coordination, and legal compliance. We ensure your investment generates consistent returns with minimal hassle.', 'fas fa-handshake', 'assets/img/service/rental.jpg', 'Tenant screening, Rent collection, Maintenance management, Legal compliance, Income optimization', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Real Estate Investment Consulting', 'Expert advice for high return investment opportunities', 'Professional investment consulting tailored to your financial goals. We analyze market trends, identify lucrative opportunities, and create strategic investment plans for maximum returns.', 'fas fa-chart-line', 'assets/img/service/investment.jpg', 'Market analysis, ROI projections, Portfolio diversification, Risk assessment, Growth strategy', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Legal Documentation Support', 'Complete legal document verification and registration help', 'We handle all legal aspects of real estate transactions including document verification, title checks, registration assistance, and compliance with local regulations. Your transaction is protected legally.', 'fas fa-file-contract', 'assets/img/service/legal.jpg', 'Title verification, Document preparation, Registration assistance, Compliance checking, Dispute resolution', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Property Loan Assistance', 'Home loan support with top bank partnerships', 'Get expert assistance in securing home loans with our partnerships with leading banks. We help with loan applications, documentation, and ensure competitive interest rates and favorable terms.', 'fas fa-hand-holding-usd', 'assets/img/service/loan.jpg', 'Loan application support, Bank partnerships, Rate comparison, Document preparation, Quick approval', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)"
    ];

    foreach ($sample_services as $service) {
        $insert = "INSERT INTO services (title, short_description, full_description, icon, image_url, benefits, created_at, updated_at) VALUES " . $service;
        $conn->query($insert);
    }
}

// Insert sample hero sliders if table is empty
$check_hero = $conn->query("SELECT COUNT(*) as count FROM hero_sliders");
$row_hero = $check_hero->fetch_assoc();
if ($row_hero['count'] == 0) {
    $sample_hero = [
        "('Premium Properties', 'Find Your Dream Apartment Today', 'Discover luxury apartments in prime locations with verified documents and expert guidance for secure investments.', 'Explore Properties', 'properties.php', 'Contact Us', 'contact.php', 'assets/img/hero/Copilot_20260219_135217.png', 'fas fa-check-circle', '22K+ Properties Listed', 'fas fa-users', '140K+ Happy Customers', 1, 'active')",
        "('Smart Investments', 'Grow Your Wealth With Smart Property Investing', 'Invest in verified properties with proven returns. Our expert team helps you identify lucrative opportunities and build wealth through real estate.', 'Start Investing', 'properties.php', 'Get Consultation', 'contact.php', 'assets/img/hero/hero-2.jpg', 'fas fa-chart-line', '₹5B+ In Transactions', 'fas fa-award', '500+ 5-Star Reviews', 2, 'active')",
        "('Trusted Partners', 'Your Real Estate Success Is Our Mission', 'With years of experience and a proven track record, NextHM is your trusted partner in real estate. Let us guide you to your best investment.', 'View Portfolio', 'properties.php', 'Schedule Call', 'contact.php', 'assets/img/hero/hero-3.jpg', 'fas fa-handshake', '200+ Expert Agents', 'fas fa-building', '50+ Cities Covered', 3, 'active')"
    ];

    foreach ($sample_hero as $hero) {
        $insert = "INSERT INTO hero_sliders (tag, title, subtitle, button1_text, button1_url, button2_text, button2_url, image_url, stat1_icon, stat1_text, stat2_icon, stat2_text, display_order, status) VALUES " . $hero;
        $conn->query($insert);
    }
}

// Insert sample properties if table is empty
$check_properties = $conn->query("SELECT COUNT(*) as count FROM properties");
$row = $check_properties->fetch_assoc();
if ($row['count'] == 0) {
    $sample_properties = [
        "('Luxury Modern Apartment', 'Beautiful 3-bedroom apartment with stunning city views, modern kitchen, and spacious living area. Located in the heart of the city with easy access to public transportation and shopping centers.', 550000, 'apartment', 'active', '123 Main Street', 'New York', 'NY', '10001', 3, 2, 2, 1200, NULL, 'assets/img/properties/properties-list-1.png')",
        "('Elegant Villa with Pool', 'Spacious 5-bedroom villa featuring a private swimming pool, lush gardens, and modern amenities. Perfect family home with gated entrance and ample parking space.', 950000, 'villa', 'active', '456 Oak Avenue', 'California', 'CA', '90001', 5, 4, 3, 3500, NULL, 'assets/img/properties/properties-list-2.png')",
        "('Commercial Office Space', 'Premium office space in downtown business district. Fully furnished with modern facilities, conference rooms, and high-speed internet. Perfect for growing businesses.', 750000, 'commercial', 'active', '789 Commerce Road', 'Chicago', 'IL', '60601', 0, 0, 5, 2000, NULL, 'assets/img/properties/properties-list-3.png')",
        "('Cozy Studio Apartment', 'Compact and efficient studio apartment perfect for young professionals. Modern finishes, in-unit laundry, and close to transit. Move-in ready!', 350000, 'apartment', 'active', '321 Cedar Lane', 'New York', 'NY', '10002', 1, 1, 0, 550, NULL, 'assets/img/properties/properties-list-1.png')",
        "('Charming Townhouse', 'Renovated 3-story townhouse with rooftop access, hardwood floors, and fireplace. Located in trending neighborhood with vibrant nightlife and excellent schools.', 650000, 'house', 'active', '654 Elm Street', 'California', 'CA', '90002', 3, 2, 1, 2000, NULL, 'assets/img/properties/properties-list-2.png')",
        "('Beachfront Resort Property', 'Stunning beachfront villa with direct beach access, panoramic ocean views, and resort-style amenities. Luxury living at its finest with 4 spacious bedrooms.', 1500000, 'villa', 'active', '999 Seaside Boulevard', 'California', 'CA', '90003', 4, 3, 2, 4000, NULL, 'assets/img/properties/properties-list-2.png')",
        "('Downtown Office Complex', 'Large commercial complex with multiple office units, retail spaces, and parking facilities. High foot traffic location with strong rental potential.', 1200000, 'commercial', 'active', '555 Business Plaza', 'Chicago', 'IL', '60602', 0, 0, 20, 5000, NULL, 'assets/img/properties/properties-list-3.png')",
        "('Suburban Family Home', 'Beautiful 4-bedroom house with large backyard and patio. Great for families with excellent school district nearby. Recently upgraded with new roof and HVAC.', 520000, 'house', 'active', '234 Maple Drive', 'New York', 'NY', '10003', 4, 2, 2, 2200, NULL, 'assets/img/properties/properties-list-1.png')",
        "('Modern Loft', 'Industrial-style loft with soaring ceilings, exposed brick, and open floor plan. Located in artsy neighborhood with galleries, cafes, and restaurants nearby.', 450000, 'apartment', 'active', '876 Gallery Lane', 'Chicago', 'IL', '60603', 2, 1, 1, 1800, NULL, 'assets/img/properties/properties-list-3.png')",
        "('Luxury Penthouse', 'Exclusive penthouse with floor-to-ceiling windows, private terrace, and high-end finishes. 3 bedrooms, 2.5 baths with premium building amenities and 24-hour concierge.', 1800000, 'apartment', 'active', '111 Sky Tower', 'New York', 'NY', '10004', 3, 2, 1, 2500, NULL, 'assets/img/properties/properties-list-1.png')"
    ];

    foreach ($sample_properties as $prop) {
        $insert = "INSERT INTO properties (title, description, price, type, status, address, city, state, zip_code, bedrooms, bathrooms, garage, area, broker_id, image_url) VALUES " . $prop;
        $conn->query($insert);
    }
}

// Insert sample blog posts if table is empty
$check_blog = $conn->query("SELECT COUNT(*) as count FROM blog_posts");
$row_blog = $check_blog->fetch_assoc();
if ($row_blog['count'] == 0) {
    $sample_blog = [
        "('Best Property Types For Rental Income', 'Learn which real estate properties generate the highest rental returns and steady cash flow. Our comprehensive analysis covers residential apartments, commercial spaces, and more investment strategies.', 1, 'assets/img/blog/blog-2-3.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Checklist Before Buying Your First Home', 'Complete guide to important legal and financial checks before property purchase. Make sure you have everything verified before making one of the biggest investments of your life.', 1, 'assets/img/blog/blog-2-1.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Real Estate Market Prediction 2026', 'Our experts analyze future property market growth and identify upcoming investment opportunities. Understand interest rates, demand trends, and location advantages for maximum returns.', 1, 'assets/img/blog/blog-2-2.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('How to Increase Your Property Value', 'Strategic renovations and improvements that add real value to your property. Learn which upgrades give the best return on investment and attract premium buyers.', 1, 'assets/img/blog/blog-2-1.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Tax Benefits of Real Estate Investment', 'Understand deductions, depreciation benefits, and tax advantages of property ownership. Maximize your returns through smart tax planning strategies.', 1, 'assets/img/blog/blog-2-2.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Guide to Rental Property Management', 'Best practices for managing rental properties efficiently. From tenant screening to maintenance coordination, learn how to maximize profitability and minimize hassles.', 1, 'assets/img/blog/blog-2-3.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)"
    ];

    foreach ($sample_blog as $blog) {
        $insert = "INSERT INTO blog_posts (title, content, author_id, featured_image, created_at, updated_at) VALUES " . $blog;
        $conn->query($insert);
    }
}

// Insert sample agents if table is empty
$check_agents = $conn->query("SELECT COUNT(*) as count FROM agents");
$row_agents = $check_agents->fetch_assoc();
if ($row_agents['count'] == 0) {
    $sample_agents = [
        "('Natasha Williams', 'Senior Property Consultant', 'natasha@Zemaraim.com', '+1-555-0101', 'Residential & Luxury Properties', 12, 'assets/img/team/team-2-1.png', 'With 12 years of experience, Natasha specializes in luxury residential properties and high-end transactions. Her attention to detail and market expertise have helped hundreds of clients find their perfect homes. Known for exceptional client service and personalized property recommendations.', 245, 4.9, 'https://facebook.com', 'https://twitter.com', 'https://linkedin.com', 'https://instagram.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Daniel Roberts', 'Luxury Property Specialist', 'daniel@Zemaraim.com', '+1-555-0102', 'Luxury & Commercial Properties', 15, 'assets/img/team/team-2-2.png', 'Daniel is a luxury property expert with 15 years in real estate. Specializing in high-end residential and commercial properties, he has successfully closed over 180 transactions. His expertise in market analysis and negotiation skills make him invaluable to discerning clients.', 180, 4.95, 'https://facebook.com', 'https://twitter.com', 'https://linkedin.com', 'https://instagram.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Michael Anderson', 'Commercial Property Advisor', 'michael@Zemaraim.com', '+1-555-0103', 'Commercial & Industrial Properties', 18, 'assets/img/team/team-2-3.png', 'Michael brings 18 years of commercial real estate expertise to clients seeking business properties. His strong relationships with commercial lenders and investment groups have resulted in numerous successful deals. Specializes in office spaces, retail locations, and commercial complexes.', 320, 4.85, 'https://facebook.com', 'https://twitter.com', 'https://linkedin.com', 'https://instagram.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Sophia Martinez', 'Residential Property Expert', 'sophia@Zemaraim.com', '+1-555-0104', 'Residential Properties & First-Time Buyers', 10, 'assets/img/team/team-2-4.png', 'Sophia is passionate about helping first-time homebuyers and families find their dream homes. With 10 years of experience, she has built a reputation for patience, honesty, and market knowledge. Her personalized approach ensures clients understand every step of the buying process.', 156, 4.88, 'https://facebook.com', 'https://twitter.com', 'https://linkedin.com', 'https://instagram.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('James Wilson', 'Investment Property Coach', 'james@Zemaraim.com', '+1-555-0105', 'Investment Properties & Portfolio Building', 13, 'assets/img/team/team-2-1.png', 'James specializes in helping investors build profitable real estate portfolios. With 13 years in investment real estate, he has helped clients generate significant returns. His analytical skills and investment strategies make him the go-to advisor for serious investors.', 198, 4.92, 'https://facebook.com', 'https://twitter.com', 'https://linkedin.com', 'https://instagram.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
        "('Emily Chen', 'Relocation Specialist', 'emily@Zemaraim.com', '+1-555-0106', 'Corporate Relocations & International Clients', 11, 'assets/img/team/team-2-2.png', 'Emily specializes in corporate relocations and assisting international clients with their property moves. Her multilingual abilities and understanding of diverse needs make her invaluable for complicated transactions. She has successfully handled relocations for Fortune 500 companies.', 167, 4.90, 'https://facebook.com', 'https://twitter.com', 'https://linkedin.com', 'https://instagram.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)"
    ];

    foreach ($sample_agents as $agent) {
        $insert = "INSERT INTO agents (name, position, email, phone, specialization, experience, image_url, bio, properties_sold, rating, facebook_url, twitter_url, linkedin_url, instagram_url, created_at, updated_at) VALUES " . $agent;
        $conn->query($insert);
    }
}

// Insert sample customers if table is empty
$check_customers = $conn->query("SELECT COUNT(*) as count FROM customers");
$row_customers = $check_customers->fetch_assoc();
if ($row_customers['count'] == 0) {
    $sample_customers = [
        [
            'firstname' => 'John',
            'lastname' => 'Anderson',
            'email' => 'john.anderson@example.com',
            'password' => password_hash('CustomerPass123', PASSWORD_BCRYPT),
            'phone' => '555-0001',
            'address' => '123 Main Street',
            'city' => 'New York',
            'role' => 'customer'
        ],
        [
            'firstname' => 'Sarah',
            'lastname' => 'Johnson',
            'email' => 'sarah.johnson@example.com',
            'password' => password_hash('CustomerPass123', PASSWORD_BCRYPT),
            'phone' => '555-0002',
            'address' => '456 Park Avenue',
            'city' => 'Los Angeles',
            'role' => 'customer'
        ],
        [
            'firstname' => 'Michael',
            'lastname' => 'Davis',
            'email' => 'michael.davis@example.com',
            'password' => password_hash('CustomerPass123', PASSWORD_BCRYPT),
            'phone' => '555-0003',
            'address' => '789 Oak Drive',
            'city' => 'Chicago',
            'role' => 'customer'
        ],
        [
            'firstname' => 'Emily',
            'lastname' => 'Wilson',
            'email' => 'emily.wilson@example.com',
            'password' => password_hash('CustomerPass123', PASSWORD_BCRYPT),
            'phone' => '555-0004',
            'address' => '321 Elm Road',
            'city' => 'Houston',
            'role' => 'customer'
        ],
        [
            'firstname' => 'David',
            'lastname' => 'Brown',
            'email' => 'david.brown@example.com',
            'password' => password_hash('CustomerPass123', PASSWORD_BCRYPT),
            'phone' => '555-0005',
            'address' => '654 Pine Street',
            'city' => 'Phoenix',
            'role' => 'customer'
        ]
    ];

    foreach ($sample_customers as $customer) {
        $stmt = $conn->prepare("INSERT INTO customers (firstname, lastname, email, password, phone, address, city, role) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bind_param('ssssssss', $customer['firstname'], $customer['lastname'], $customer['email'], $customer['password'], $customer['phone'], $customer['address'], $customer['city'], $customer['role']);
        $stmt->execute();
        $stmt->close();
    }
}

// Insert test users if table is empty
$check_users = $conn->query("SELECT COUNT(*) as count FROM users");
$row_users = $check_users->fetch_assoc();
if ($row_users['count'] == 0) {
    $test_users = [
        [
            'full_name' => 'Admin User',
            'email' => 'admin@test.com',
            'password' => password_hash('AdminPass123', PASSWORD_BCRYPT),
            'role' => 'admin',
            'status' => 'active'
        ],
        [
            'full_name' => 'Bob Broker',
            'email' => 'broker@test.com',
            'password' => password_hash('BrokerPass123', PASSWORD_BCRYPT),
            'role' => 'broker',
            'status' => 'active'
        ],
        [
            'full_name' => 'Mike Staff',
            'email' => 'staff@test.com',
            'password' => password_hash('StaffPass123', PASSWORD_BCRYPT),
            'role' => 'staff',
            'status' => 'active'
        ]
    ];

    foreach ($test_users as $user) {
        $stmt = $conn->prepare("INSERT INTO users (full_name, email, password, role, status) VALUES (?, ?, ?, ?, ?)");
        $stmt->bind_param('sssss', $user['full_name'], $user['email'], $user['password'], $user['role'], $user['status']);
        $stmt->execute();
        $stmt->close();
    }
}

// Check if user is logged in
$is_logged_in = isset($_SESSION['user_id']) && !empty($_SESSION['user_id']) ? true : false;

// Base URL
define('BASE_URL', 'http://localhost/Zemaraim/');

// Directory paths
define('ROOT_DIR', dirname(__FILE__));
?>
