<?php
/**
 * Database Schema Initialization
 * Run this once to set up the database
 */

require_once __DIR__ . '/../config/database.php';

try {
    // Create admins table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS admins (
            id INT PRIMARY KEY AUTO_INCREMENT,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            full_name VARCHAR(100),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create parcels table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS parcels (
            id INT PRIMARY KEY AUTO_INCREMENT,
            tracking_code VARCHAR(20) UNIQUE NOT NULL,
            sender_name VARCHAR(100) NOT NULL,
            sender_email VARCHAR(100),
            sender_phone VARCHAR(20),
            sender_address TEXT,
            sender_city VARCHAR(50),
            sender_country VARCHAR(50),
            receiver_name VARCHAR(100) NOT NULL,
            receiver_email VARCHAR(100),
            receiver_phone VARCHAR(20),
            receiver_address TEXT,
            receiver_city VARCHAR(50),
            receiver_country VARCHAR(50),
            package_description TEXT,
            package_weight DECIMAL(10, 2),
            package_dimensions VARCHAR(100),
            service_type ENUM('air_freight', 'sea_freight', 'road_transport', 'warehousing') DEFAULT 'air_freight',
            status ENUM('pending', 'picked_up', 'in_transit', 'out_for_delivery', 'delivered', 'cancelled') DEFAULT 'pending',
            current_location VARCHAR(100),
            current_latitude DECIMAL(10, 8),
            current_longitude DECIMAL(11, 8),
            estimated_delivery DATE,
            actual_delivery DATE,
            image_path VARCHAR(255),
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_tracking_code (tracking_code),
            INDEX idx_status (status),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create parcel_updates table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS parcel_updates (
            id INT PRIMARY KEY AUTO_INCREMENT,
            parcel_id INT NOT NULL,
            status VARCHAR(50) NOT NULL,
            location VARCHAR(100),
            latitude DECIMAL(10, 8),
            longitude DECIMAL(11, 8),
            description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE CASCADE,
            INDEX idx_parcel_id (parcel_id),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create reviews table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS reviews (
            id INT PRIMARY KEY AUTO_INCREMENT,
            reviewer_name VARCHAR(100) NOT NULL,
            reviewer_email VARCHAR(100),
            rating INT CHECK (rating >= 1 AND rating <= 5),
            review_text TEXT,
            image_path VARCHAR(255),
            is_featured BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create email_logs table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS email_logs (
            id INT PRIMARY KEY AUTO_INCREMENT,
            recipient_email VARCHAR(100) NOT NULL,
            subject VARCHAR(255),
            parcel_id INT,
            status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
            error_message TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (parcel_id) REFERENCES parcels(id) ON DELETE SET NULL,
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Create settings table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS settings (
            id INT PRIMARY KEY AUTO_INCREMENT,
            setting_key VARCHAR(100) UNIQUE NOT NULL,
            setting_value LONGTEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Insert default settings
    $pdo->exec("
        INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
        ('company_name', 'RoutexShipping'),
        ('company_email', 'info@routexshipping.com'),
        ('company_phone', '+1-800-ROUTEX-1'),
        ('company_address', '123 Logistics Avenue, Global City, GC 12345'),
        ('smtp_host', 'smtp.gmail.com'),
        ('smtp_port', '587'),
        ('smtp_user', 'your-email@gmail.com'),
        ('smtp_password', 'your-app-password'),
        ('google_maps_api_key', 'YOUR_GOOGLE_MAPS_API_KEY')
    ");

    // Insert default admin (username: admin, password: admin123)
    $admin_password = password_hash('admin123', PASSWORD_BCRYPT);
    $pdo->exec("
        INSERT IGNORE INTO admins (username, email, password, full_name) VALUES
        ('admin', 'admin@routexshipping.com', '$admin_password', 'Administrator')
    ");

    echo "✓ Database initialized successfully!\n";
    echo "✓ Default admin created: username=admin, password=admin123\n";
    echo "✓ Please change the default password after first login.\n";

} catch (PDOException $e) {
    die("Error initializing database: " . $e->getMessage());
}
