<?php
// Database configuration
define('DB_HOST', 'localhost');
define('DB_NAME', 'sidd_astro');
define('DB_USER', 'sidd_astro');
define('DB_PASS', 'sidd_astro');

// Create database connection
function getDBConnection() {
    try {
        $pdo = new PDO(
            "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8",
            DB_USER,
            DB_PASS,
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]
        );
        return $pdo;
    } catch (PDOException $e) {
        die("Connection failed: " . $e->getMessage());
    }
}

// Create database and tables if they don't exist
function initializeDatabase() {
    try {
        // Connect without database first
        $pdo = new PDO(
            "mysql:host=" . DB_HOST . ";charset=utf8",
            DB_USER,
            DB_PASS,
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]
        );
        
        // Create database if it doesn't exist
        $pdo->exec("CREATE DATABASE IF NOT EXISTS " . DB_NAME . " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $pdo->exec("USE " . DB_NAME);
        
        // Create users table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                username VARCHAR(100) UNIQUE NOT NULL,
                mobile VARCHAR(15) UNIQUE NOT NULL,
                city VARCHAR(100) NOT NULL,
                password VARCHAR(255) NOT NULL,
                wallet_enabled TINYINT(1) DEFAULT 1,
                wallet_limit DECIMAL(10,2) DEFAULT 10000.00,
                wallet_status ENUM('active','suspended','closed') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ");
        
        // Create appointments table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS appointments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                user_name VARCHAR(100) NOT NULL,
                user_mobile VARCHAR(15) NOT NULL,
                appointment_date DATE NOT NULL,
                time_slot VARCHAR(50) NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'pending',
                consultation_type ENUM('office_meeting', 'video_call', 'audio_call') DEFAULT 'video_call',
                payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
                payment_transaction_id VARCHAR(255) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        ");
        
        // Add payment columns to appointments table if they don't exist
        $pdo->exec("
            ALTER TABLE appointments 
            ADD COLUMN IF NOT EXISTS payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending'
        ");
        
        $pdo->exec("
            ALTER TABLE appointments 
            ADD COLUMN IF NOT EXISTS payment_transaction_id VARCHAR(255) NULL
        ");
        
        $pdo->exec("
            CREATE INDEX IF NOT EXISTS idx_appointments_payment_status ON appointments(payment_status)
        ");
        
        // Create settings table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS settings (
                id INT AUTO_INCREMENT PRIMARY KEY,
                setting_key VARCHAR(100) UNIQUE NOT NULL,
                setting_value TEXT NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ");
        
        // Create notifications table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS notifications (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                body TEXT NOT NULL,
                device_token VARCHAR(500),
                status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ");
        
        // Create panchang_data table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS panchang_data (
                id INT AUTO_INCREMENT PRIMARY KEY,
                date DATE NOT NULL UNIQUE,
                tithi VARCHAR(100) DEFAULT NULL,
                nakshatra VARCHAR(100) DEFAULT NULL,
                yoga VARCHAR(100) DEFAULT NULL,
                karana VARCHAR(100) DEFAULT NULL,
                muhurat VARCHAR(100) DEFAULT NULL,
                rahu_kaal VARCHAR(100) DEFAULT NULL,
                gulika_kaal VARCHAR(100) DEFAULT NULL,
                sunrise VARCHAR(20) DEFAULT NULL,
                sunset VARCHAR(20) DEFAULT NULL,
                moonrise VARCHAR(20) DEFAULT NULL,
                moonset VARCHAR(20) DEFAULT NULL,
                special_day VARCHAR(200) DEFAULT NULL,
                festival VARCHAR(200) DEFAULT NULL,
                remarks TEXT DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_date (date),
                INDEX idx_tithi (tithi),
                INDEX idx_festival (festival)
            )
        ");
        
        // Create panchang_backups table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS panchang_backups (
                id INT AUTO_INCREMENT PRIMARY KEY,
                backup_id VARCHAR(50) NOT NULL UNIQUE,
                data LONGTEXT NOT NULL,
                version VARCHAR(20) DEFAULT '1.0.0',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                data_size INT DEFAULT NULL,
                INDEX idx_created_at (created_at)
            )
        ");
        
        // Create user_wallets table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS user_wallets (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL UNIQUE,
                balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
                currency VARCHAR(3) DEFAULT 'INR',
                status ENUM('active', 'suspended', 'closed') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_balance (balance),
                INDEX idx_status (status)
            )
        ");
        
        // Create wallet_transactions table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS wallet_transactions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                type ENUM('credit', 'debit', 'transfer') NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                description VARCHAR(255) DEFAULT NULL,
                payment_method VARCHAR(100) DEFAULT NULL,
                balance_after DECIMAL(10,2) NOT NULL,
                transaction_id VARCHAR(100) DEFAULT NULL,
                reference_id VARCHAR(100) DEFAULT NULL,
                status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'completed',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_user_id (user_id),
                INDEX idx_type (type),
                INDEX idx_status (status),
                INDEX idx_created_at (created_at),
                INDEX idx_transaction_id (transaction_id)
            )
        ");
        
        // Create admin_logs table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS admin_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                admin_id INT DEFAULT NULL,
                action VARCHAR(100) NOT NULL,
                details TEXT DEFAULT NULL,
                ip_address VARCHAR(45) DEFAULT NULL,
                user_agent TEXT DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_admin_id (admin_id),
                INDEX idx_action (action),
                INDEX idx_created_at (created_at)
            )
        ");
        
        // Create day_off table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS day_off (
                id INT AUTO_INCREMENT PRIMARY KEY,
                date DATE NOT NULL,
                reason VARCHAR(255),
                is_full_day BOOLEAN DEFAULT TRUE,
                start_time TIME NULL,
                end_time TIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY unique_date (date)
            )
        ");
        
// Create payments table for PhonePe integration
$pdo->exec("
    CREATE TABLE IF NOT EXISTS payments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        transaction_id VARCHAR(255) UNIQUE NOT NULL,
        appointment_id INT,
        amount DECIMAL(10,2) DEFAULT 0.00,
        status ENUM('PENDING', 'SUCCESS', 'FAILED', 'CANCELLED') DEFAULT 'PENDING',
        payment_method VARCHAR(50) DEFAULT 'PHONEPE',
        payment_id VARCHAR(255) NULL,
        phonepe_response TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_transaction_id (transaction_id),
        INDEX idx_appointment_id (appointment_id),
        INDEX idx_status (status),
        FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE CASCADE
    )
");

// Create settings table for dynamic configuration
$pdo->exec("
    CREATE TABLE IF NOT EXISTS settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        default_consultation_price DECIMAL(10,2) DEFAULT 500.00,
        office_phone VARCHAR(20) DEFAULT '',
        office_address TEXT DEFAULT '',
        business_hours VARCHAR(100) DEFAULT '9:00 AM - 6:00 PM',
        cancellation_policy TEXT DEFAULT '',
        refund_policy TEXT DEFAULT '',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
");

// Add default_consultation_price column to settings table if it doesn't exist
$pdo->exec("
    ALTER TABLE settings 
    ADD COLUMN IF NOT EXISTS default_consultation_price DECIMAL(10,2) DEFAULT 500.00
");

// Insert default settings if table is empty
$stmt = $pdo->query("SELECT COUNT(*) FROM settings");
$count = $stmt->fetchColumn();

if ($count == 0) {
    $pdo->exec("
        INSERT INTO settings (
            default_consultation_price,
            office_phone,
            office_address,
            business_hours,
            cancellation_policy,
            refund_policy
        ) VALUES (
            500.00,
            '+91-9876543210',
            'Your Office Address',
            '9:00 AM - 6:00 PM',
            'Cancellation allowed 24 hours before appointment',
            'Full refund if cancelled 24 hours before appointment'
        )
    ");
}
        
        // Create slot_off table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS slot_off (
                id INT AUTO_INCREMENT PRIMARY KEY,
                date DATE NOT NULL,
                start_time TIME NOT NULL,
                end_time TIME NOT NULL,
                reason VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ");
        
        // Insert default settings
        $pdo->exec("
            INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
            ('advance_booking_days', '30'),
            ('slot_duration', '60'),
            ('slots_per_hour', '1'),
            ('start_time', '09:00'),
            ('end_time', '18:00'),
            ('lunch_start', '13:00'),
            ('lunch_end', '14:00'),
            ('tuesday_price', '300'),
            ('saturday_price', '300'),
            ('sunday_price', '300'),
            ('other_days_price', '500'),
            ('system_enabled', '1')
        ");
        
        return true;
    } catch (PDOException $e) {
        die("Database initialization failed: " . $e->getMessage());
    }
}

// Don't auto-initialize database to prevent header issues
// Call initializeDatabase() manually when needed

// Only initialize if explicitly called
if (defined('INITIALIZE_DB') && INITIALIZE_DB === true) {
    initializeDatabase();
}
