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

class PerformanceController {
    private $conn;

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

    private function getPeriodFilter($period, $tableAlias = 't') {
        switch ($period) {
            case 'Yearly':
                return "$tableAlias.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND $tableAlias.period = :period"; // Add period condition
            case 'Quarterly':
                return "$tableAlias.created_at >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)";
            case 'Monthly':
            default:
                return "$tableAlias.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)";
        }
    }

    public function getSummaryMetrics($params, $user_id) {
        try {
            $where = [];
            $bindings = [];
            if (isset($params['ipo_id'])) {
                $where[] = "t.ipo_id = :ipo_id";
                $bindings[':ipo_id'] = $params['ipo_id'];
            }

            $whereSql = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

            // Active Targets
            $query = "SELECT COUNT(*) AS activeTargets FROM performance_targets t $whereSql";
            $stmt = $this->conn->prepare($query);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->execute();
            $activeTargets = (int)$stmt->fetchColumn();

            // Avg Performance
            $query = "SELECT AVG(CASE WHEN t.unit = 'days' THEN (t.target / t.current * 100) ELSE (t.current / t.target * 100) END) AS avgPerformance
                      FROM performance_targets t $whereSql";
            $stmt = $this->conn->prepare($query);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->execute();
            $avgPerformance = (float)($stmt->fetchColumn() ?? 0);

            // Reviews Due
            $whereReviews = ["status = 'scheduled'", "review_date <= CURDATE()"];
            $whereReviewsSql = 'WHERE ' . implode(' AND ', $whereReviews);
            $query = "SELECT COUNT(*) AS reviewsDue FROM performance_reviews $whereReviewsSql";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            $reviewsDue = (int)$stmt->fetchColumn();

            // Top Performers
            $whereUsersSql = "WHERE t.ipo_id = :ipo_id";
            $query = "SELECT COUNT(DISTINCT t.ipo_id) AS topPerformers
                      FROM performance_targets t
                      JOIN users u ON t.ipo_id = u.id
                      $whereUsersSql
                      GROUP BY t.ipo_id
                      HAVING AVG(CASE WHEN t.unit = 'days' THEN (t.target / t.current * 100) ELSE (t.current / t.target * 100) END) >= 90";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':ipo_id', $user_id);
            $stmt->execute();
            $topPerformers = (int)$stmt->fetchColumn();

            return json_encode([
                'status' => 'success',
                'data' => [
                    'activeTargets' => $activeTargets,
                    'avgPerformance' => round($avgPerformance, 1),
                    'reviewsDue' => $reviewsDue,
                    'topPerformers' => $topPerformers
                ]
            ]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function getTargets() {
        try {
            $where = [];
            $bindings = [];

            $whereSql = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

            $query = "SELECT t.id, t.metric, t.target, t.current, t.unit, t.period, CONCAT(u.first_name, ' ', u.last_name) AS assignedTo, t.ipo_id AS ipoId
                      FROM performance_targets t
                      LEFT JOIN users u ON t.ipo_id = u.id
                      $whereSql";
            $stmt = $this->conn->prepare($query);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->execute();
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

            return json_encode(['status' => 'success', 'data' => $data]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function createTarget($data) {
        if (!isset($data['metric'], $data['target'], $data['unit'], $data['period']) || !in_array($data['period'], ['Monthly', 'Quarterly', 'Yearly'])) {
            return json_encode(['status' => 'error', 'message' => 'Invalid input']);
        }

        try {
            $query = "INSERT INTO performance_targets (metric, target, unit, period, ipo_id)
                      VALUES (:metric, :target, :unit, :period, :ipo_id)";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':metric', $data['metric']);
            $stmt->bindValue(':target', $data['target']);
            $stmt->bindValue(':unit', $data['unit']);
            $stmt->bindValue(':period', $data['period']);
            $stmt->bindValue(':ipo_id', $data['ipo_id'] ?? null, PDO::PARAM_INT);
            $stmt->execute();

            $id = $this->conn->lastInsertId();
            $query = "SELECT t.id, t.metric, t.target, t.current, t.unit, t.period, CONCAT(u.first_name, ' ', u.last_name) AS assignedTo, t.ipo_id AS ipoId
                      FROM performance_targets t
                      LEFT JOIN users u ON t.ipo_id = u.id WHERE t.id = :id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':id', $id);
            $stmt->execute();
            $target = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode(['status' => 'success', 'data' => $target]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function updateTarget($id, $data) {
        if (!isset($data['metric'], $data['target'], $data['unit'], $data['period']) || !in_array($data['period'], ['Monthly', 'Quarterly', 'Yearly'])) {
            return json_encode(['status' => 'error', 'message' => 'Invalid input']);
        }

        try {
            $query = "UPDATE performance_targets
                      SET metric = :metric, target = :target, unit = :unit, period = :period
                      WHERE id = :id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':metric', $data['metric']);
            $stmt->bindValue(':target', $data['target']);
            $stmt->bindValue(':unit', $data['unit']);
            $stmt->bindValue(':period', $data['period']);
            $stmt->bindValue(':id', $id);
            $stmt->execute();

            if ($stmt->rowCount() === 0) {
                return json_encode(['status' => 'error', 'message' => 'Target not found']);
            }

            $query = "SELECT t.id, t.metric, t.target, t.current, t.unit, t.period, CONCAT(u.first_name, ' ', u.last_name) AS assignedTo, t.ipo_id AS ipoId
                      FROM performance_targets t
                      LEFT JOIN users u ON t.ipo_id = u.id WHERE t.id = :id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':id', $id);
            $stmt->execute();
            $target = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode(['status' => 'success', 'data' => $target]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function deleteTarget($id) {
        try {
            $query = "DELETE FROM performance_targets WHERE id = :id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':id', $id);
            $stmt->execute();

            if ($stmt->rowCount() === 0) {
                return json_encode(['status' => 'error', 'message' => 'Target not found']);
            }

            return json_encode(['status' => 'success', 'message' => 'Target deleted']);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function getReviews() {
        try {
            $where = [];
            $bindings = [];

            $whereSql = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

            $query = "SELECT r.id, CONCAT(u.first_name, ' ', u.last_name) AS employee, r.ipo_id AS ipoId, r.period, r.score, r.status, r.review_date AS reviewDate, CONCAT(u2.first_name, ' ', u2.last_name) AS reviewer
                      FROM performance_reviews r
                      JOIN users u ON r.ipo_id = u.id
                      JOIN users u2 ON r.reviewer_id = u2.id
                      $whereSql";
            $stmt = $this->conn->prepare($query);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->execute();
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

            return json_encode(['status' => 'success', 'data' => $data]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function conductReview($id, $data) {
        try {
            $query = "UPDATE performance_reviews
                      SET status = 'completed', score = :score
                      WHERE id = :id AND status = 'scheduled'";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':score', $data['score']);
            $stmt->bindValue(':id', $id);
            $stmt->execute();

            if ($stmt->rowCount() === 0) {
                return json_encode(['status' => 'error', 'message' => 'Review not found or not scheduled']);
            }

            $query = "SELECT r.id, CONCAT(u.first_name, ' ', u.last_name) AS employee, r.ipo_id AS ipoId, r.period, r.score, r.status, r.review_date AS reviewDate, CONCAT(u2.first_name, ' ', u2.last_name) AS reviewer
                      FROM performance_reviews r
                      JOIN users u ON r.ipo_id = u.id
                      JOIN users u2 ON r.reviewer_id = u.id WHERE r.id = :id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':id', $id);
            $stmt->execute();
            $review = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode(['status' => 'success', 'data' => $review]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function getKpiSettings() {
        try {
            $query = "SELECT invoice_target AS invoiceTarget, collection_rate AS collectionRate, response_time AS responseTime, customer_satisfaction AS customerSatisfaction
                      FROM kpi_settings LIMIT 1";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            $data = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode(['status' => 'success', 'data' => $data]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function updateKpiSettings($data, $user_id) {
        try {
            $query = "UPDATE kpi_settings
                      SET invoice_target = :invoiceTarget, collection_rate = :collectionRate, 
                          response_time = :responseTime, customer_satisfaction = :customerSatisfaction,
                          updated_by = :updatedBy
                      WHERE id = 1";
            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':invoiceTarget', $data['invoiceTarget']);
            $stmt->bindValue(':collectionRate', $data['collectionRate']);
            $stmt->bindValue(':responseTime', $data['responseTime']);
            $stmt->bindValue(':customerSatisfaction', $data['customerSatisfaction']);
            $stmt->bindValue(':updatedBy', $user_id);
            $stmt->execute();

            if ($stmt->rowCount() === 0) {
                $query = "INSERT INTO kpi_settings (invoice_target, collection_rate, response_time, customer_satisfaction, updated_by)
                          VALUES (:invoiceTarget, :collectionRate, :responseTime, :customerSatisfaction, :updatedBy)";
                $stmt = $this->conn->prepare($query);
                $stmt->bindValue(':invoiceTarget', $data['invoiceTarget']);
                $stmt->bindValue(':collectionRate', $data['collectionRate']);
                $stmt->bindValue(':responseTime', $data['responseTime']);
                $stmt->bindValue(':customerSatisfaction', $data['customerSatisfaction']);
                $stmt->bindValue(':updatedBy', $user_id);
                $stmt->execute();
            }

            $query = "SELECT invoice_target AS invoiceTarget, collection_rate AS collectionRate, response_time AS responseTime, customer_satisfaction AS customerSatisfaction
                      FROM kpi_settings WHERE id = 1";
            $stmt = $this->conn->prepare($query);
            $stmt->execute();
            $data = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode(['status' => 'success', 'data' => $data]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function getTeamAnalytics($params) {
        try {
            $where = [];
            $bindings = [];
            if (isset($params['period']) && in_array($params['period'], ['Monthly', 'Quarterly', 'Yearly'])) {
                $where[] = $this->getPeriodFilter($params['period'], 't'); // Ensure the table alias is passed
            }

            $whereSql = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

            // Team Trends
            $query = "SELECT CONCAT(u.first_name, ' ', u.last_name) AS employee, t.ipo_id AS ipoId, 
                             AVG(CASE WHEN t.unit = 'days' THEN (t.target / t.current * 100) ELSE (t.current / t.target * 100) END) AS performance
                      FROM performance_targets t
                      JOIN users u ON t.ipo_id = u.id
                      $whereSql
                      GROUP BY t.ipo_id, CONCAT(u.first_name, ' ', u.last_name)";
            $stmt = $this->conn->prepare($query);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->execute();
            $trends = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Performance Distribution
            $query = "SELECT 
                        SUM(CASE WHEN avg_perf >= 90 THEN 1 ELSE 0 END) AS excellent,
                        SUM(CASE WHEN avg_perf >= 80 AND avg_perf < 90 THEN 1 ELSE 0 END) AS good,
                        SUM(CASE WHEN avg_perf < 80 THEN 1 ELSE 0 END) AS needsImprovement
                      FROM (
                          SELECT AVG(CASE WHEN t.unit = 'days' THEN (t.target / t.current * 100) ELSE (t.current / t.target * 100) END) AS avg_perf
                          FROM performance_targets t
                          JOIN users u ON t.ipo_id = u.id
                          $whereSql
                          GROUP BY t.ipo_id
                      ) sub";
            $stmt = $this->conn->prepare($query);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->execute();
            $dist = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode([
                'status' => 'success',
                'data' => [
                    'trends' => $trends,
                    'distribution' => [
                        'excellent' => ['count' => (int)($dist['excellent'] ?? 0), 'threshold' => 90],
                        'good' => ['count' => (int)($dist['good'] ?? 0), 'threshold' => 80],
                        'needsImprovement' => ['count' => (int)($dist['needsImprovement'] ?? 0), 'threshold' => 80]
                    ]
                ]
            ]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }
}
