<?php
/**
 * Expense Categories Migration Script
 * 
 * This script migrates data from the expense_ref table to the expense_categories table
 * It combines category, code, and description from expense_ref into the appropriate fields in expense_categories
 */

// Include database connection
include 'layouts/dbconn.php';

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

echo "Starting migration process...\n";

// Get current user ID (assuming admin with ID 1 if not specified)
$current_user_id = 1;

// Check if the expense_categories table exists and has the needed structure
$check_table_sql = "SHOW TABLES LIKE 'expense_categories'";
$result = $conn->query($check_table_sql);

if ($result->num_rows == 0) {
    echo "Error: expense_categories table doesn't exist.\n";
    exit;
}

// Map expense_ref categories to expense_categories types
function mapCategoryToType($category) {
    $mapping = [
        'Operating' => 'Office',
        'Administration' => 'Office',
        'Employment' => 'Office',
        'Insurance' => 'Office',
        'Repairs & Maintenance' => 'Office',
        'Utility' => 'Office',
        'Finance' => 'Office',
        'Selling & Distribution' => 'Office'
    ];
    
    return $mapping[$category] ?? 'Other';
}

// Fetch all records from expense_ref
$fetch_sql = "SELECT * FROM expense_ref WHERE category IS NOT NULL AND category != ''";
$result = $conn->query($fetch_sql);

if ($result->num_rows > 0) {
    // Begin transaction
    $conn->begin_transaction();
    
    try {
        $count = 0;
        $duplicates = 0;
        
        while ($row = $result->fetch_assoc()) {
            // Prepare the data for insertion
            $category = $row['category'];
            $code = $row['code'];
            $description = $row['description'];
            
            // Combine code and description if both exist
            $name = $category . ' - ' . ($code ? $code : '');
            $fullDescription = $code ? "$code - $description" : $description;
            $type = mapCategoryToType($category);
            
            // Check if this category/code combination already exists
            $check_sql = "SELECT * FROM expense_categories WHERE name = ? AND description = ?";
            $check_stmt = $conn->prepare($check_sql);
            $check_stmt->bind_param("ss", $name, $fullDescription);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();
            
            if ($check_result->num_rows > 0) {
                echo "Skipping duplicate entry: $name\n";
                $duplicates++;
                continue;
            }
            
            // Insert into expense_categories
            $insert_sql = "INSERT INTO expense_categories (name, description, type, status, created_by) 
                          VALUES (?, ?, ?, 'Active', ?)";
            
            $stmt = $conn->prepare($insert_sql);
            $stmt->bind_param("sssi", $name, $fullDescription, $type, $current_user_id);
            
            if ($stmt->execute()) {
                $count++;
                echo "Migrated: $name\n";
            } else {
                throw new Exception("Error inserting record: " . $stmt->error);
            }
        }
        
        // Commit transaction
        $conn->commit();
        echo "Migration completed successfully. Migrated $count records. Skipped $duplicates duplicates.\n";
        
    } catch (Exception $e) {
        // Rollback transaction on error
        $conn->rollback();
        echo "Migration failed: " . $e->getMessage() . "\n";
    }
} else {
    echo "No records found in expense_ref table.\n";
}

// Close connection
$conn->close();
?>