<?php
// api/cotisations/OperationController.php
// Version PRO : list, add, update, delete, totals, analytics, export_excel, pagination, search
ini_set('display_errors', 1);
error_reporting(E_ALL);

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

class OperationController {

    private $db;

    public function __construct() {
        $this->db = (new Database())->getConnection();
    }

    // ───────────────────────────────────────────────
    // GET ALL + FILTRES + PAGINATION
    // ───────────────────────────────────────────────
    public function getAll() {
        header("Content-Type: application/json; charset=utf-8");

        $type = $_GET['type'] ?? null;
        $categorie = isset($_GET['categorie_id']) ? intval($_GET['categorie_id']) : null;
        $q = $_GET['q'] ?? null;
        $dateFrom = $_GET['date_from'] ?? null;
        $dateTo = $_GET['date_to'] ?? null;
        $page = max(1, intval($_GET['page'] ?? 1));
        $perPage = max(10, min(200, intval($_GET['per_page'] ?? 50)));
        $offset = ($page - 1) * $perPage;

        $where = [];
        $params = [];

        if ($type) {
            $where[] = "o.type = ?";
            $params[] = $type;
        }
        if ($categorie) {
            $where[] = "o.categorie_id = ?";
            $params[] = $categorie;
        }
        if ($q) {
            $where[] = "(o.description LIKE ? OR o.commentaire LIKE ?)";
            $params[] = "%$q%";
            $params[] = "%$q%";
        }
        if ($dateFrom) {
            $where[] = "o.date_operation >= ?";
            $params[] = $dateFrom;
        }
        if ($dateTo) {
            $where[] = "o.date_operation <= ?";
            $params[] = $dateTo;
        }

        $whereSql = count($where) ? "WHERE " . implode(" AND ", $where) : "";

        // Total count
        $countStmt = $this->db->prepare("
            SELECT COUNT(*) AS cnt 
            FROM operations o
            $whereSql
        ");
        $countStmt->execute($params);
        $total = intval($countStmt->fetch(PDO::FETCH_ASSOC)['cnt']);

        // Query data
        $sql = "
            SELECT o.*, c.nom AS categorie_nom
            FROM operations o
            LEFT JOIN categories c ON o.categorie_id = c.id
            $whereSql
            ORDER BY o.date_operation DESC, o.created_at DESC
            LIMIT $perPage OFFSET $offset
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        echo json_encode([
            "success" => true,
            "data" => $rows,
            "meta" => [
                "total" => $total,
                "page" => $page,
                "per_page" => $perPage,
                "pages" => ceil($total / $perPage)
            ]
        ]);
    }

    // ───────────────────────────────────────────────
    // ADD
    // ───────────────────────────────────────────────
    public function add() {
        header("Content-Type: application/json; charset=utf-8");

        $input = json_decode(file_get_contents("php://input"), true);

        if (!$input || !isset($input['type']) || !isset($input['categorie_id']) || !isset($input['montant']) || !isset($input['date_operation'])) {
            echo json_encode(["success" => false, "message" => "Paramètres manquants"]);
            return;
        }

        try {
            $stmt = $this->db->prepare("
                INSERT INTO operations (type, montant, description, categorie_id, date_operation, commentaire, created_by, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
            ");
            $stmt->execute([
                $input['type'],
                $input['montant'],
                $input['description'] ?? "",
                intval($input['categorie_id']),
                $input['date_operation'],
                $input['commentaire'] ?? "",
                isset($input['created_by']) ? intval($input['created_by']) : null
            ]);

            echo json_encode(["success" => true, "message" => "Opération ajoutée"]);
        } catch (Exception $e) {
            echo json_encode(["success" => false, "message" => $e->getMessage()]);
        }
    }

    // ───────────────────────────────────────────────
    // UPDATE
    // ───────────────────────────────────────────────
    public function update() {
        header("Content-Type: application/json; charset=utf-8");

        if (!isset($_GET['id'])) {
            echo json_encode(["success" => false, "message" => "ID manquant"]);
            return;
        }

        $id = intval($_GET['id']);
        $input = json_decode(file_get_contents("php://input"), true);

        if (!$input) {
            echo json_encode(["success" => false, "message" => "Payload JSON manquant"]);
            return;
        }

        $fields = [];
        $params = [];

        foreach (["montant", "description", "commentaire", "date_operation"] as $field) {
            if (isset($input[$field])) {
                $fields[] = "$field = ?";
                $params[] = $input[$field];
            }
        }

        if (isset($input['categorie_id'])) {
            $fields[] = "categorie_id = ?";
            $params[] = intval($input['categorie_id']);
        }

        if (empty($fields)) {
            echo json_encode(["success" => false, "message" => "Aucun champ à mettre à jour"]);
            return;
        }

        $params[] = $id;

        $sql = "UPDATE operations SET " . implode(", ", $fields) . ", updated_at = NOW() WHERE id = ?";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        echo json_encode(["success" => true, "message" => "Opération mise à jour"]);
    }

    // ───────────────────────────────────────────────
    // DELETE
    // ───────────────────────────────────────────────
    public function delete() {
        header("Content-Type: application/json; charset=utf-8");

        if (!isset($_GET['id'])) {
            echo json_encode(["success" => false, "message" => "ID manquant"]);
            return;
        }

        $id = intval($_GET['id']);

        $stmt = $this->db->prepare("DELETE FROM operations WHERE id = ?");
        $stmt->execute([$id]);

        echo json_encode(["success" => true, "message" => "Opération supprimée"]);
    }

    // ───────────────────────────────────────────────
    // TOTALS RECETTES / DÉPENSES / SOLDE
    // ───────────────────────────────────────────────
    public function totals() {
        header("Content-Type: application/json; charset=utf-8");

        $dateFrom = $_GET['date_from'] ?? null;
        $dateTo = $_GET['date_to'] ?? null;

        $where = [];
        $params = [];

        if ($dateFrom) { $where[] = "date_operation >= ?"; $params[] = $dateFrom; }
        if ($dateTo)   { $where[] = "date_operation <= ?"; $params[] = $dateTo; }

        $whereSql = count($where) ? "WHERE " . implode(" AND ", $where) : "";

        $sql = "
            SELECT
                SUM(CASE WHEN type='recette' THEN montant ELSE 0 END) AS total_recettes,
                SUM(CASE WHEN type='depense' THEN montant ELSE 0 END) AS total_depenses
            FROM operations
            $whereSql
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $r = $stmt->fetch(PDO::FETCH_ASSOC);

        $rec = floatval($r["total_recettes"] ?? 0);
        $dep = floatval($r["total_depenses"] ?? 0);

        echo json_encode([
            "success" => true,
            "total_recettes" => $rec,
            "total_depenses" => $dep,
            "solde" => $rec - $dep
        ]);
    }

    // ───────────────────────────────────────────────
    // ANALYTICS (monthly or by_category)
    // ───────────────────────────────────────────────
    public function analytics() {
        header("Content-Type: application/json; charset=utf-8");

        $mode = $_GET['mode'] ?? "monthly";
        $dateFrom = $_GET['date_from'] ?? null;
        $dateTo = $_GET['date_to'] ?? null;

        $where = [];
        $params = [];

        if ($dateFrom) { $where[] = "o.date_operation >= ?"; $params[] = $dateFrom; }
        if ($dateTo)   { $where[] = "o.date_operation <= ?"; $params[] = $dateTo; }

        $whereSql = count($where) ? "WHERE " . implode(" AND ", $where) : "";

        if ($mode === "monthly") {
            $sql = "
                SELECT DATE_FORMAT(o.date_operation, '%Y-%m') AS month, o.type, SUM(o.montant) AS total
                FROM operations o
                $whereSql
                GROUP BY month, o.type
                ORDER BY month ASC
            ";
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            echo json_encode(["success" => true, "data" => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
            return;
        }

        if ($mode === "by_category") {
            $sql = "
                SELECT c.nom, c.type AS categorie_type, SUM(o.montant) AS total
                FROM operations o
                LEFT JOIN categories c ON o.categorie_id = c.id
                $whereSql
                GROUP BY c.id
                ORDER BY total DESC
            ";
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            echo json_encode(["success" => true, "data" => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
            return;
        }

        echo json_encode(["success" => false, "message" => "Mode analytique inconnu"]);
    }

    // ───────────────────────────────────────────────
    // EXPORT EXCEL (.xlsx minimal, sans composer)
    // ───────────────────────────────────────────────
    public function exportExcel() {
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header("Content-Disposition: attachment; filename=operations_kliv.xlsx");

        $sql = "
            SELECT o.*, c.nom AS categorie_nom
            FROM operations o
            LEFT JOIN categories c ON o.categorie_id = c.id
            ORDER BY o.date_operation DESC
        ";

        $req = $this->db->prepare($sql);
        $req->execute();
        $rows = $req->fetchAll(PDO::FETCH_ASSOC);

        // XML Excel simple
        $xml = '<?xml version="1.0"?>
        <?mso-application progid="Excel.Sheet"?>
        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
          <Worksheet ss:Name="KLIV">
            <Table>
              <Row>
                <Cell><Data ss:Type="String">Date</Data></Cell>
                <Cell><Data ss:Type="String">Type</Data></Cell>
                <Cell><Data ss:Type="String">Catégorie</Data></Cell>
                <Cell><Data ss:Type="String">Montant</Data></Cell>
                <Cell><Data ss:Type="String">Description</Data></Cell>
                <Cell><Data ss:Type="String">Commentaire</Data></Cell>
              </Row>';

        foreach ($rows as $r) {
            $xml .= '<Row>
                <Cell><Data ss:Type="String">'.htmlspecialchars($r["date_operation"]).'</Data></Cell>
                <Cell><Data ss:Type="String">'.htmlspecialchars($r["type"]).'</Data></Cell>
                <Cell><Data ss:Type="String">'.htmlspecialchars($r["categorie_nom"]).'</Data></Cell>
                <Cell><Data ss:Type="Number">'.$r["montant"].'</Data></Cell>
                <Cell><Data ss:Type="String">'.htmlspecialchars($r["description"]).'</Data></Cell>
                <Cell><Data ss:Type="String">'.htmlspecialchars($r["commentaire"]).'</Data></Cell>
              </Row>';
        }

        $xml .= '</Table></Worksheet></Workbook>';

        echo $xml;
    }
}

// ───────────────────────────────────────────────
// ROUTER
// ───────────────────────────────────────────────
$controller = new OperationController();

if (!isset($_GET['action'])) {
    echo json_encode(["success" => false, "message" => "Aucune action fournie"]);
    exit;
}

switch ($_GET['action']) {
    case 'all': $controller->getAll(); break;
    case 'add': $controller->add(); break;
    case 'update': $controller->update(); break;
    case 'delete': $controller->delete(); break;
    case 'totals': $controller->totals(); break;
    case 'analytics': $controller->analytics(); break;
    case 'export_excel': $controller->exportExcel(); break;
    default: 
        echo json_encode(["success" => false, "message" => "Action inconnue"]);
}
?>
