<?php
require_once 'config/database.php';

try {
    // Check if expense_categories table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'expense_categories'");
    $exists = $stmt->fetch();
    
    if (!$exists) {
        echo "Creating expense_categories table...\n";
        
        $pdo->exec("
            CREATE TABLE expense_categories (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL UNIQUE,
                description TEXT,
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ");
        
        // Insert some default categories
        $default_categories = [
            ['Food', 'Food related expenses'],
            ['Transportation', 'Transport and travel costs'],
            ['Utilities', 'Electricity, water, gas bills'],
            ['Office Supplies', 'Stationery and office materials'],
            ['Maintenance', 'Building and equipment maintenance'],
            ['Salary', 'Employee salaries and wages'],
            ['Marketing', 'Advertising and promotional expenses'],
            ['Miscellaneous', 'Other expenses']
        ];
        
        $stmt = $pdo->prepare("INSERT IGNORE INTO expense_categories (name, description) VALUES (?, ?)");
        foreach ($default_categories as $category) {
            $stmt->execute($category);
        }
        
        echo "Expense categories table created with default data.\n";
    } else {
        echo "expense_categories table already exists.\n";
        
        // Check for duplicate entries
        $stmt = $pdo->query("
            SELECT name, COUNT(*) as count 
            FROM expense_categories 
            GROUP BY name 
            HAVING COUNT(*) > 1
        ");
        $duplicates = $stmt->fetchAll();
        
        if (!empty($duplicates)) {
            echo "Found duplicate categories:\n";
            foreach ($duplicates as $dup) {
                echo "- " . $dup['name'] . " (appears " . $dup['count'] . " times)\n";
            }
            
            // Remove duplicates, keeping only the first occurrence
            $pdo->exec("
                DELETE e1 FROM expense_categories e1
                INNER JOIN expense_categories e2 
                WHERE e1.id > e2.id AND e1.name = e2.name
            ");
            echo "Duplicate entries removed.\n";
        }
    }
    
    // Check if expenses table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'expenses'");
    $exists = $stmt->fetch();
    
    if (!$exists) {
        echo "Creating expenses table...\n";
        
        $pdo->exec("
            CREATE TABLE expenses (
                id INT AUTO_INCREMENT PRIMARY KEY,
                category_id INT NOT NULL,
                title VARCHAR(200) NOT NULL,
                description TEXT,
                amount DECIMAL(10,2) NOT NULL,
                expense_date DATE NOT NULL,
                payment_method ENUM('cash', 'bank_transfer', 'mobile_banking', 'check') DEFAULT 'cash',
                receipt_no VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (category_id) REFERENCES expense_categories(id) ON DELETE CASCADE
            )
        ");
        echo "Expenses table created.\n";
    } else {
        echo "expenses table already exists.\n";
    }
    
    // Show current data
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM expense_categories");
    $count = $stmt->fetch();
    echo "Total categories: " . $count['count'] . "\n";
    
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM expenses");
    $count = $stmt->fetch();
    echo "Total expenses: " . $count['count'] . "\n";
    
    echo "Database setup completed successfully!\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>