<?php
/**
 * Expense Categories Controller
 * 
 * A class to handle CRUD operations for expense categories
 */
class ExpenseCategoriesController {
    private $conn;
    
    /**
     * Constructor
     * 
     * @param mysqli $conn Database connection
     */
    public function __construct($conn) {
        $this->conn = $conn;
    }
    
    /**
     * Get all expense categories
     * 
     * @param string $status Filter by status (optional)
     * @param string $type Filter by type (optional)
     * @return array List of categories
     */
    public function getAllCategories($status = null, $type = null) {
        $categories = [];
        $sql = "SELECT * FROM expense_categories";
        $params = [];
        $types = "";
        $where = [];
        
        // Add filters if provided
        if ($status) {
            $where[] = "status = ?";
            $params[] = $status;
            $types .= "s";
        }
        
        if ($type) {
            $where[] = "type = ?";
            $params[] = $type;
            $types .= "s";
        }
        
        // Construct WHERE clause if needed
        if (!empty($where)) {
            $sql .= " WHERE " . implode(" AND ", $where);
        }
        
        $sql .= " ORDER BY name ASC";
        
        // Prepare and execute statement
        try {
            $stmt = $this->conn->prepare($sql);
            
            if (!empty($params)) {
                $stmt->bind_param($types, ...$params);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result && $result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
                    $categories[] = $row;
                }
            }
            
            return $categories;
        } catch (Exception $e) {
            error_log("Error getting categories: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get active expense categories
     * 
     * @param string $type Filter by type (optional)
     * @return array List of active categories
     */
    public function getActiveCategories($type = null) {
        return $this->getAllCategories('Active', $type);
    }
    
    /**
     * Get a category by ID
     * 
     * @param int $categoryId The category ID
     * @return array|null The category data or null if not found
     */
    public function getCategoryById($categoryId) {
        try {
            $sql = "SELECT * FROM expense_categories WHERE category_id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("i", $categoryId);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows > 0) {
                return $result->fetch_assoc();
            }
            
            return null;
        } catch (Exception $e) {
            error_log("Error getting category by ID: " . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Add a new expense category
     * 
     * @param array $categoryData The category data
     * @param int $userId User creating the category
     * @return int|bool The inserted ID if successful, false otherwise
     */
    public function addCategory($categoryData, $userId) {
        try {
            // Check for duplicate name
            $check_sql = "SELECT * FROM expense_categories WHERE name = ?";
            $check_stmt = $this->conn->prepare($check_sql);
            $check_stmt->bind_param("s", $categoryData['name']);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();
            
            if ($check_result->num_rows > 0) {
                throw new Exception("A category with that name already exists");
            }
            
            // Insert new category
            $sql = "INSERT INTO expense_categories (name, description, type, status, created_by) 
                    VALUES (?, ?, ?, ?, ?)";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                "ssssi", 
                $categoryData['name'], 
                $categoryData['description'], 
                $categoryData['type'], 
                $categoryData['status'], 
                $userId
            );
            
            if ($stmt->execute()) {
                return $stmt->insert_id;
            } else {
                throw new Exception("Error adding category: " . $stmt->error);
            }
        } catch (Exception $e) {
            error_log("Error adding category: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Update an existing expense category
     * 
     * @param int $categoryId The category ID
     * @param array $categoryData The category data
     * @return bool True if successful, false otherwise
     */
    public function updateCategory($categoryId, $categoryData) {
        try {
            // Check if category exists
            $check_sql = "SELECT * FROM expense_categories WHERE category_id = ?";
            $check_stmt = $this->conn->prepare($check_sql);
            $check_stmt->bind_param("i", $categoryId);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();
            
            if ($check_result->num_rows === 0) {
                throw new Exception("Category not found");
            }
            
            // Check for duplicate name
            $name_check_sql = "SELECT * FROM expense_categories WHERE name = ? AND category_id != ?";
            $name_check_stmt = $this->conn->prepare($name_check_sql);
            $name_check_stmt->bind_param("si", $categoryData['name'], $categoryId);
            $name_check_stmt->execute();
            $name_check_result = $name_check_stmt->get_result();
            
            if ($name_check_result->num_rows > 0) {
                throw new Exception("Another category with that name already exists");
            }
            
            // Update category
            $sql = "UPDATE expense_categories 
                    SET name = ?, description = ?, type = ?, status = ? 
                    WHERE category_id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                "ssssi", 
                $categoryData['name'], 
                $categoryData['description'], 
                $categoryData['type'], 
                $categoryData['status'], 
                $categoryId
            );
            
            return $stmt->execute();
        } catch (Exception $e) {
            error_log("Error updating category: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Delete an expense category
     * 
     * @param int $categoryId The category ID
     * @return bool True if successful, false otherwise
     */
    public function deleteCategory($categoryId) {
        try {
            // Check if category exists
            $check_sql = "SELECT * FROM expense_categories WHERE category_id = ?";
            $check_stmt = $this->conn->prepare($check_sql);
            $check_stmt->bind_param("i", $categoryId);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();
            
            if ($check_result->num_rows === 0) {
                throw new Exception("Category not found");
            }
            
            // Check if the category is in use
            $usage_check_sql = "SELECT COUNT(*) as count FROM expenses WHERE category_id = ?";
            $usage_check_stmt = $this->conn->prepare($usage_check_sql);
            $usage_check_stmt->bind_param("i", $categoryId);
            $usage_check_stmt->execute();
            $usage_result = $usage_check_stmt->get_result();
            $usage_count = $usage_result->fetch_assoc()['count'];
            
            if ($usage_count > 0) {
                throw new Exception("Cannot delete category because it is being used by {$usage_count} expenses.");
            }
            
            // Delete category
            $sql = "DELETE FROM expense_categories WHERE category_id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("i", $categoryId);
            
            return $stmt->execute();
        } catch (Exception $e) {
            error_log("Error deleting category: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Get category usage statistics
     * 
     * @param int $categoryId The category ID (optional)
     * @return array Usage statistics
     */
    public function getCategoryUsageStats($categoryId = null) {
        try {
            $sql = "SELECT ec.category_id, ec.name, COUNT(e.expense_id) as usage_count, 
                           SUM(e.amount) as total_amount
                    FROM expense_categories ec
                    LEFT JOIN expenses e ON ec.category_id = e.category_id";
            
            if ($categoryId) {
                $sql .= " WHERE ec.category_id = ?";
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param("i", $categoryId);
            } else {
                $sql .= " GROUP BY ec.category_id
                         ORDER BY usage_count DESC";
                $stmt = $this->conn->prepare($sql);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            
            $stats = [];
            while ($row = $result->fetch_assoc()) {
                $stats[] = $row;
            }
            
            return $stats;
        } catch (Exception $e) {
            error_log("Error getting category usage stats: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get categories by type
     * 
     * @param string $type The category type
     * @param bool $activeOnly Get only active categories
     * @return array List of categories
     */
    public function getCategoriesByType($type, $activeOnly = true) {
        try {
            $sql = "SELECT * FROM expense_categories WHERE type = ?";
            
            if ($activeOnly) {
                $sql .= " AND status = 'Active'";
            }
            
            $sql .= " ORDER BY name ASC";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("s", $type);
            $stmt->execute();
            $result = $stmt->get_result();
            
            $categories = [];
            while ($row = $result->fetch_assoc()) {
                $categories[] = $row;
            }
            
            return $categories;
        } catch (Exception $e) {
            error_log("Error getting categories by type: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Update category status
     * 
     * @param int $categoryId The category ID
     * @param string $status The new status ('Active' or 'Inactive')
     * @return bool True if successful, false otherwise
     */
    public function updateCategoryStatus($categoryId, $status) {
        try {
            $sql = "UPDATE expense_categories SET status = ? WHERE category_id = ?";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("si", $status, $categoryId);
            
            return $stmt->execute();
        } catch (Exception $e) {
            error_log("Error updating category status: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Import categories from expense_ref table
     * 
     * @param int $userId User ID for created_by field
     * @return array Result with counts of imported and skipped categories
     */
    public function importFromExpenseRef($userId) {
        try {
            $this->conn->begin_transaction();
            
            // Function to map category to type
            $mapCategoryToType = function($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 = $this->conn->query($fetch_sql);
            
            $imported = 0;
            $skipped = 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 = $this->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) {
                    $skipped++;
                    continue;
                }
                
                // Insert into expense_categories
                $insert_sql = "INSERT INTO expense_categories (name, description, type, status, created_by) 
                              VALUES (?, ?, ?, 'Active', ?)";
                
                $stmt = $this->conn->prepare($insert_sql);
                $stmt->bind_param("sssi", $name, $fullDescription, $type, $userId);
                
                if ($stmt->execute()) {
                    $imported++;
                } else {
                    throw new Exception("Error inserting record: " . $stmt->error);
                }
            }
            
            $this->conn->commit();
            
            return [
                'success' => true,
                'imported' => $imported,
                'skipped' => $skipped
            ];
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Error importing from expense_ref: " . $e->getMessage());
            
            return [
                'success' => false,
                'error' => $e->getMessage(),
                'imported' => 0,
                'skipped' => 0
            ];
        }
    }
}
?>