<?php

require_once 'Logger.php';

class Expense {
    private $conn;
    private $logger;
    private $table_name = "operational_expenses";
    private $category_table = "expense_categories";

    public $expense_id;
    public $expense_category_id;
    public $description;
    public $amount;
    public $expense_date;
    public $branch_id;
    public $user_id;
    public $created_at;
    public $updated_at;

    public $category_name;
    public $branch_name;
    public $username;

    public function __construct($db, $logger = null) {
        $this->conn = $db;
        $this->logger = $logger;
    }

    public function create() {
        $query = "INSERT INTO " . $this->table_name . "
                (expense_category_id, description, amount, expense_date, branch_id, user_id)
                VALUES
                (:expense_category_id, :description, :amount, :expense_date, :branch_id, :user_id)";

        $stmt = $this->conn->prepare($query);

        $this->expense_category_id = htmlspecialchars(strip_tags($this->expense_category_id));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->amount = htmlspecialchars(strip_tags($this->amount));
        $this->expense_date = htmlspecialchars(strip_tags($this->expense_date));
        $this->branch_id = empty($this->branch_id) ? null : htmlspecialchars(strip_tags($this->branch_id));
        $this->user_id = htmlspecialchars(strip_tags($this->user_id));

        $stmt->bindParam(':expense_category_id', $this->expense_category_id);
        $stmt->bindParam(':description', $this->description);
        $stmt->bindParam(':amount', $this->amount);
        $stmt->bindParam(':expense_date', $this->expense_date);
        $stmt->bindParam(':branch_id', $this->branch_id, PDO::PARAM_INT);
        $stmt->bindParam(':user_id', $this->user_id);

        if ($stmt->execute()) {
            $this->expense_id = $this->conn->lastInsertId();

            if ($this->logger) {
                $expenseData = [
                    'expense_category_id' => $this->expense_category_id,
                    'description' => $this->description,
                    'amount' => $this->amount,
                    'expense_date' => $this->expense_date,
                    'branch_id' => $this->branch_id,
                    'user_id' => $this->user_id
                ];
                $result = $this->logger->logExpense('CREATE', $this->expense_id, $expenseData);
            }

            return true;
        }
        return false;
    }

    public function read() {
        $query = "SELECT e.*, c.category_name, b.branch_name, u.username
                FROM " . $this->table_name . " e
                LEFT JOIN " . $this->category_table . " c ON e.expense_category_id = c.category_id
                LEFT JOIN branches b ON e.branch_id = b.branch_id
                LEFT JOIN users u ON e.user_id = u.user_id
                WHERE e.expense_id = :expense_id";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':expense_id', $this->expense_id);
        $stmt->execute();

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row) {
            $this->expense_id = $row['expense_id'];
            $this->expense_category_id = $row['expense_category_id'];
            $this->description = $row['description'];
            $this->amount = $row['amount'];
            $this->expense_date = $row['expense_date'];
            $this->branch_id = $row['branch_id'];
            $this->user_id = $row['user_id'];
            $this->created_at = $row['created_at'];
            $this->updated_at = $row['updated_at'];
            
            $this->category_name = $row['category_name'];
            $this->branch_name = $row['branch_name'];
            $this->username = $row['username'];
            
            return true;
        }
        return false;
    }

    public function update() {
        $oldData = null;
        if ($this->logger) {
            $oldExpense = new Expense($this->conn);
            $oldExpense->expense_id = $this->expense_id;
            $oldExpense->read();
            $oldData = [
                'expense_category_id' => $oldExpense->expense_category_id,
                'description' => $oldExpense->description,
                'amount' => $oldExpense->amount,
                'expense_date' => $oldExpense->expense_date,
                'branch_id' => $oldExpense->branch_id,
                'user_id' => $oldExpense->user_id
            ];
        }

        $query = "UPDATE " . $this->table_name . "
                SET
                    expense_category_id = :expense_category_id,
                    description = :description,
                    amount = :amount,
                    expense_date = :expense_date,
                    branch_id = :branch_id,
                    user_id = :user_id
                WHERE
                    expense_id = :expense_id";

        $stmt = $this->conn->prepare($query);

        $this->expense_category_id = htmlspecialchars(strip_tags($this->expense_category_id));
        $this->description = htmlspecialchars(strip_tags($this->description));
        $this->amount = htmlspecialchars(strip_tags($this->amount));
        $this->expense_date = htmlspecialchars(strip_tags($this->expense_date));
        $this->branch_id = htmlspecialchars(strip_tags($this->branch_id));
        $this->user_id = htmlspecialchars(strip_tags($this->user_id));
        $this->expense_id = htmlspecialchars(strip_tags($this->expense_id));

        $stmt->bindParam(':expense_category_id', $this->expense_category_id);
        $stmt->bindParam(':description', $this->description);
        $stmt->bindParam(':amount', $this->amount);
        $stmt->bindParam(':expense_date', $this->expense_date);
        $stmt->bindParam(':branch_id', $this->branch_id);
        $stmt->bindParam(':user_id', $this->user_id);
        $stmt->bindParam(':expense_id', $this->expense_id);

        $result = $stmt->execute();

        if ($result && $this->logger && $oldData) {
            $newData = [
                'expense_category_id' => $this->expense_category_id,
                'description' => $this->description,
                'amount' => $this->amount,
                'expense_date' => $this->expense_date,
                'branch_id' => $this->branch_id,
                'user_id' => $this->user_id
            ];
            $this->logger->logExpense('UPDATE', $this->expense_id, $newData, $oldData);
        }

        return $result;
    }

    public function delete() {
        $expenseData = null;
        if ($this->logger) {
            $expense = new Expense($this->conn);
            $expense->expense_id = $this->expense_id;
            $expense->read();
            $expenseData = [
                'expense_category_id' => $expense->expense_category_id,
                'description' => $expense->description,
                'amount' => $expense->amount,
                'expense_date' => $expense->expense_date,
                'branch_id' => $expense->branch_id,
                'user_id' => $expense->user_id
            ];
        }

        $query = "DELETE FROM " . $this->table_name . " WHERE expense_id = :expense_id";
        $stmt = $this->conn->prepare($query);

        $this->expense_id = htmlspecialchars(strip_tags($this->expense_id));

        $stmt->bindParam(':expense_id', $this->expense_id);

        $result = $stmt->execute();

        if ($result && $this->logger && $expenseData) {
            $this->logger->logExpense('DELETE', $this->expense_id, null, $expenseData);
        }

        return $result;
    }

    public function getAll($from_date = null, $to_date = null, $category_id = null, $branch_id = null) {
        $query = "SELECT e.*, c.category_name, b.branch_name, u.username
                FROM " . $this->table_name . " e
                LEFT JOIN " . $this->category_table . " c ON e.expense_category_id = c.category_id
                LEFT JOIN branches b ON e.branch_id = b.branch_id
                LEFT JOIN users u ON e.user_id = u.user_id
                WHERE 1=1";

        if ($from_date && $to_date) {
            $query .= " AND e.expense_date BETWEEN :from_date AND :to_date";
        }
        if ($category_id) {
            $query .= " AND e.expense_category_id = :category_id";
        }
        if ($branch_id) {
            $query .= " AND e.branch_id = :branch_id";
        }

        $query .= " ORDER BY e.expense_date DESC";

        $stmt = $this->conn->prepare($query);

        if ($from_date && $to_date) {
            $stmt->bindParam(':from_date', $from_date);
            $stmt->bindParam(':to_date', $to_date);
        }
        if ($category_id) {
            $stmt->bindParam(':category_id', $category_id);
        }
        if ($branch_id) {
            $stmt->bindParam(':branch_id', $branch_id);
        }

        $stmt->execute();
        return $stmt;
    }

    public function duplicateMonth($source_month, $target_month, $user_id, array $category_ids = []) {
        $sourceDate = DateTime::createFromFormat('!Y-m', (string) $source_month);
        $targetDate = DateTime::createFromFormat('!Y-m', (string) $target_month);

        if (!$sourceDate || $sourceDate->format('Y-m') !== $source_month) {
            throw new InvalidArgumentException('Source month must use YYYY-MM format.');
        }
        if (!$targetDate || $targetDate->format('Y-m') !== $target_month) {
            throw new InvalidArgumentException('Target month must use YYYY-MM format.');
        }
        if ($source_month === $target_month) {
            throw new InvalidArgumentException('Source and target months must be different.');
        }

        $sourceStart = $sourceDate->format('Y-m-01');
        $sourceEnd = (clone $sourceDate)->modify('first day of next month')->format('Y-m-01');
        $sourceParams = [
            ':source_start' => $sourceStart,
            ':source_end' => $sourceEnd
        ];
        $sourceSql = 'SELECT expense_category_id, description, amount, expense_date, branch_id
                      FROM ' . $this->table_name . '
                      WHERE expense_date >= :source_start AND expense_date < :source_end';

        $category_ids = array_values(array_unique(array_filter(array_map('intval', $category_ids))));
        if (!empty($category_ids)) {
            $categoryPlaceholders = [];
            foreach ($category_ids as $index => $categoryId) {
                $placeholder = ':category_' . $index;
                $categoryPlaceholders[] = $placeholder;
                $sourceParams[$placeholder] = $categoryId;
            }
            $sourceSql .= ' AND expense_category_id IN (' . implode(', ', $categoryPlaceholders) . ')';
        }

        $sourceSql .= ' ORDER BY expense_date, expense_id';
        $sourceStmt = $this->conn->prepare($sourceSql);
        $sourceStmt->execute($sourceParams);
        $sourceExpenses = $sourceStmt->fetchAll(PDO::FETCH_ASSOC);

        if (empty($sourceExpenses)) {
            return 0;
        }

        $insertStmt = $this->conn->prepare(
            'INSERT INTO ' . $this->table_name . '
                (expense_category_id, description, amount, expense_date, branch_id, user_id)
             VALUES
                (:expense_category_id, :description, :amount, :expense_date, :branch_id, :user_id)'
        );
        $targetLastDay = (int) $targetDate->format('t');
        $startedTransaction = !$this->conn->inTransaction();

        try {
            if ($startedTransaction) {
                $this->conn->beginTransaction();
            }

            foreach ($sourceExpenses as $sourceExpense) {
                $sourceDay = (int) date('d', strtotime($sourceExpense['expense_date']));
                $targetDay = min($sourceDay, $targetLastDay);
                $targetExpenseDate = $targetDate->format('Y-m-') . str_pad((string) $targetDay, 2, '0', STR_PAD_LEFT);
                $duplicateData = [
                    ':expense_category_id' => $sourceExpense['expense_category_id'],
                    ':description' => $sourceExpense['description'],
                    ':amount' => $sourceExpense['amount'],
                    ':expense_date' => $targetExpenseDate,
                    ':branch_id' => $sourceExpense['branch_id'] ?: null,
                    ':user_id' => (int) $user_id
                ];

                if (!$insertStmt->execute($duplicateData)) {
                    throw new RuntimeException('Failed to duplicate an expense record.');
                }

                if ($this->logger) {
                    $this->logger->logExpense('CREATE', $this->conn->lastInsertId(), [
                        'expense_category_id' => $sourceExpense['expense_category_id'],
                        'description' => $sourceExpense['description'],
                        'amount' => $sourceExpense['amount'],
                        'expense_date' => $targetExpenseDate,
                        'branch_id' => $sourceExpense['branch_id'] ?: null,
                        'user_id' => (int) $user_id,
                        'duplicated_from_month' => $source_month
                    ]);
                }
            }

            if ($startedTransaction) {
                $this->conn->commit();
            }
        } catch (Throwable $e) {
            if ($startedTransaction && $this->conn->inTransaction()) {
                $this->conn->rollBack();
            }
            throw $e;
        }

        return count($sourceExpenses);
    }

    public function getSummaryByCategory($from_date = null, $to_date = null, $branch_id = null) {
        $query = "SELECT c.category_name, SUM(e.amount) as total_amount
                FROM " . $this->table_name . " e
                LEFT JOIN " . $this->category_table . " c ON e.expense_category_id = c.category_id
                WHERE 1=1";

        if ($from_date && $to_date) {
            $query .= " AND e.expense_date BETWEEN :from_date AND :to_date";
        }
        if ($branch_id) {
            $query .= " AND e.branch_id = :branch_id";
        }

        $query .= " GROUP BY c.category_name ORDER BY total_amount DESC";

        $stmt = $this->conn->prepare($query);

        if ($from_date && $to_date) {
            $stmt->bindParam(':from_date', $from_date);
            $stmt->bindParam(':to_date', $to_date);
        }
        if ($branch_id) {
            $stmt->bindParam(':branch_id', $branch_id);
        }

        $stmt->execute();
        return $stmt;
    }


/*
    public function getTotalExpenses($from_date = null, $to_date = null, $category_id = null, $branch_id = null) {
        $query = "SELECT SUM(amount) as total FROM " . $this->table_name . " WHERE 1=1";
    
        if ($from_date && $to_date) {
            $query .= " AND expense_date BETWEEN :from_date AND :to_date";
        }
        if ($branch_id) {
            $query .= " AND branch_id = :branch_id";
        }
        if ($category_id) {
            $query .= " AND expense_category_id = :category_id";
        }
    
        $stmt = $this->conn->prepare($query);
    
        if ($from_date && $to_date) {
            $stmt->bindParam(':from_date', $from_date);
            $stmt->bindParam(':to_date', $to_date);
        }
        if ($branch_id) {
            $stmt->bindParam(':branch_id', $branch_id);
        }
        if ($category_id) {
            $stmt->bindParam(':category_id', $category_id);
        }
    
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $row['total'] ? $row['total'] : 0;
    }
*/

	public function getTotalExpenses($from_date = null, $to_date = null, $category_id = null, $branch_id = null) {
    // Preserve existing three-argument calls where the third value is a branch ID.
    if (func_num_args() < 4) {
        $branch_id = $category_id;
        $category_id = null;
    }
    
    $query = "SELECT SUM(amount) as total FROM " . $this->table_name . " WHERE 1=1";

   

   // Date filter
    if (!empty($from_date) && !empty($to_date)) {
        $query .= " AND expense_date BETWEEN :from_date AND :to_date";
    }


    // Category filter
    if (!empty($category_id) && $category_id !== 'all') {
        $query .= ' AND expense_category_id = :category_id';
    }

    // Branch filter (ignore if 'all' or empty)
    if (!empty($branch_id) && $branch_id !== 'all') {
        $query .= " AND branch_id = :branch_id";
    }

    $stmt = $this->conn->prepare($query);

    // Bind parameters
    if (!empty($from_date) && !empty($to_date)) {
        $stmt->bindParam(':from_date', $from_date);
        $stmt->bindParam(':to_date', $to_date);
    }


    if (!empty($category_id) && $category_id !== 'all') {
        $stmt->bindParam(':category_id', $category_id, PDO::PARAM_INT);
    }

    if (!empty($branch_id) && $branch_id !== 'all') {
        $stmt->bindParam(':branch_id', $branch_id, PDO::PARAM_INT);
    }

    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return !empty($row['total']) ? $row['total'] : 0;
}


    public function getAllCategories() {
        $query = "SELECT * FROM " . $this->category_table . " ORDER BY category_name ASC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }

    public function createCategory($category_name, $description = '') {
        $check_query = "SELECT COUNT(*) FROM " . $this->category_table . " WHERE category_name = :category_name";
        $check_stmt = $this->conn->prepare($check_query);
        $check_stmt->bindParam(':category_name', $category_name);
        $check_stmt->execute();
        
        if ($check_stmt->fetchColumn() > 0) {
            return false;
        }

        $query = "INSERT INTO " . $this->category_table . " (category_name, description) VALUES (:category_name, :description)";
        $stmt = $this->conn->prepare($query);

        $category_name = htmlspecialchars(strip_tags($category_name));
        $description = htmlspecialchars(strip_tags($description));

        $stmt->bindParam(':category_name', $category_name);
        $stmt->bindParam(':description', $description);

        if ($stmt->execute()) {
            $categoryId = $this->conn->lastInsertId();

            if ($this->logger) {
                $categoryData = [
                    'category_name' => $category_name,
                    'description' => $description
                ];
                $this->logger->log('CREATE_EXPENSE_CATEGORY', 'expenses', "Created expense category: {$category_name}", $categoryId, 'expense_categories', null, $categoryData);
            }

            return $categoryId;
        }
        return false;
    }

    public function updateCategory($category_id, $category_name, $description = '') {
        $oldData = null;
        if ($this->logger) {
            $query = "SELECT category_name, description FROM " . $this->category_table . " WHERE category_id = :category_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':category_id', $category_id);
            $stmt->execute();
            $oldData = $stmt->fetch(PDO::FETCH_ASSOC);
        }

        $check_query = "SELECT COUNT(*) FROM " . $this->category_table .
                      " WHERE category_name = :category_name AND category_id != :category_id";
        $check_stmt = $this->conn->prepare($check_query);
        $check_stmt->bindParam(':category_name', $category_name);
        $check_stmt->bindParam(':category_id', $category_id);
        $check_stmt->execute();

        if ($check_stmt->fetchColumn() > 0) {
            return false;
        }

        $query = "UPDATE " . $this->category_table . "
                SET category_name = :category_name, description = :description
                WHERE category_id = :category_id";
        $stmt = $this->conn->prepare($query);

        $category_id = htmlspecialchars(strip_tags($category_id));
        $category_name = htmlspecialchars(strip_tags($category_name));
        $description = htmlspecialchars(strip_tags($description));

        $stmt->bindParam(':category_id', $category_id);
        $stmt->bindParam(':category_name', $category_name);
        $stmt->bindParam(':description', $description);

        $result = $stmt->execute();

        if ($result && $this->logger && $oldData) {
            $newData = [
                'category_name' => $category_name,
                'description' => $description
            ];
            $this->logger->log('UPDATE_EXPENSE_CATEGORY', 'expenses', "Updated expense category: {$category_name}", $category_id, 'expense_categories', $oldData, $newData);
        }

        return $result;
    }

    public function deleteCategory($category_id) {
        $categoryData = null;
        if ($this->logger) {
            $query = "SELECT category_name, description FROM " . $this->category_table . " WHERE category_id = :category_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':category_id', $category_id);
            $stmt->execute();
            $categoryData = $stmt->fetch(PDO::FETCH_ASSOC);
        }

        $check_query = "SELECT COUNT(*) FROM " . $this->table_name . " WHERE expense_category_id = :category_id";
        $check_stmt = $this->conn->prepare($check_query);
        $check_stmt->bindParam(':category_id', $category_id);
        $check_stmt->execute();

        if ($check_stmt->fetchColumn() > 0) {
            return false;
        }

        $query = "DELETE FROM " . $this->category_table . " WHERE category_id = :category_id";
        $stmt = $this->conn->prepare($query);

        $category_id = htmlspecialchars(strip_tags($category_id));

        $stmt->bindParam(':category_id', $category_id);

        $result = $stmt->execute();

        if ($result && $this->logger && $categoryData) {
            $this->logger->log('DELETE_EXPENSE_CATEGORY', 'expenses', "Deleted expense category: {$categoryData['category_name']}", $category_id, 'expense_categories', $categoryData, null);
        }

        return $result;
    }

    public function getExpensesForPeriod($from_date = null, $to_date = null, $category_id = null, $branch_id = null) {
        return $this->getAll($from_date, $to_date, $category_id, $branch_id);
    }

    public function deleteExpense($expense_id) {
        $this->expense_id = $expense_id;
        return $this->delete();
    }

    public function getBranchExpenses($branchId, $startDate, $endDate) {
        $sql = "SELECT COALESCE(SUM(amount), 0) as total 
                FROM " . $this->table_name . " 
                WHERE branch_id = :branch_id 
                AND DATE(expense_date) BETWEEN :start_date AND :end_date";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        
        return $stmt->fetch(PDO::FETCH_ASSOC)['total'] ?? 0;
    }

    public function getExpenseCategories() {
        $sql = "SELECT * FROM " . $this->category_table;
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getCategoryExpenses($categoryId, $startDate, $endDate, $branchId = null) {
        $sql = "SELECT COALESCE(SUM(amount), 0) as total 
                FROM " . $this->table_name . " 
                WHERE expense_category_id = :category_id 
                AND DATE(expense_date) BETWEEN :start_date AND :end_date";

        if (!empty($branchId) && $branchId !== 'all') {
            $sql .= " AND branch_id = :branch_id";
        }
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':category_id', $categoryId, PDO::PARAM_INT);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        if (!empty($branchId) && $branchId !== 'all') {
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
        }
        $stmt->execute();
        
        return $stmt->fetch(PDO::FETCH_ASSOC)['total'] ?? 0;
    }
    public function getDetailedMonthlyReport($month, $branchId = null, $categoryId = null) {
        $date = DateTime::createFromFormat('!Y-m', (string)$month);
        if (!$date || $date->format('Y-m') !== $month) {
            throw new InvalidArgumentException('Month must use YYYY-MM format.');
        }
        $params = [':start'=>$date->format('Y-m-d'), ':end'=>(clone $date)->modify('first day of next month')->format('Y-m-d')];
        $filters = ['e.expense_date >= :start', 'e.expense_date < :end'];
        if ($branchId) {
            $filters[] = 'e.branch_id = :branch';
            $params[':branch'] = (int)$branchId;
        }
        if ($categoryId) {
            $filters[] = 'e.expense_category_id = :category';
            $params[':category'] = (int)$categoryId;
        }
        $from = " FROM operational_expenses e LEFT JOIN expense_categories c ON c.category_id=e.expense_category_id LEFT JOIN branches b ON b.branch_id=e.branch_id LEFT JOIN users u ON u.user_id=e.user_id WHERE " . implode(' AND ', $filters);
        $run = function($select, $tail='') use($from, $params) {
            $stmt = $this->conn->prepare($select.$from.$tail);
            $stmt->execute($params);
            return $stmt;
        };
        $details = $run('SELECT e.expense_id,e.expense_date,e.description,e.amount,e.created_at,c.category_name,b.branch_name,u.username', ' ORDER BY e.expense_date,e.expense_id')->fetchAll(PDO::FETCH_ASSOC);
        $summary = $run('SELECT COUNT(*) expense_count,COALESCE(SUM(e.amount),0) total_amount,COALESCE(AVG(e.amount),0) average_amount,COALESCE(MIN(e.amount),0) minimum_amount,COALESCE(MAX(e.amount),0) maximum_amount')->fetch(PDO::FETCH_ASSOC);
        $byCategory = $run("SELECT COALESCE(c.category_name,'Uncategorised') label,COUNT(*) expense_count,SUM(e.amount) total_amount", ' GROUP BY e.expense_category_id,c.category_name ORDER BY total_amount DESC')->fetchAll(PDO::FETCH_ASSOC);
        $byBranch = $run("SELECT COALESCE(b.branch_name,'Unassigned') label,COUNT(*) expense_count,SUM(e.amount) total_amount", ' GROUP BY e.branch_id,b.branch_name ORDER BY total_amount DESC')->fetchAll(PDO::FETCH_ASSOC);
        $byDay = $run('SELECT DATE(e.expense_date) expense_day,COUNT(*) expense_count,SUM(e.amount) total_amount', ' GROUP BY DATE(e.expense_date) ORDER BY expense_day')->fetchAll(PDO::FETCH_ASSOC);
        return compact('month','summary','details','byCategory','byBranch','byDay');
    }
}
?>
