<?php

class KpiManualInput {
    private $conn;
    private $table_name = "kpi_manual_inputs";

    public $id;
    public $kpi_name;
    public $value;
    public $month;
    public $year;
    public $input_by_user_id;
    public $created_at;
    public $updated_at;

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

    // Create a new manual KPI input or update an existing one
    public function createOrUpdate() {
        // Check if an entry for this KPI, month, and year already exists
        $query_check = "SELECT id FROM " . $this->table_name . "
                        WHERE kpi_name = :kpi_name AND month = :month AND year = :year
                        LIMIT 0,1";
        $stmt_check = $this->conn->prepare($query_check);
        $stmt_check->bindParam(":kpi_name", $this->kpi_name, PDO::PARAM_STR);
        $stmt_check->bindParam(":month", $this->month, PDO::PARAM_INT);
        $stmt_check->bindParam(":year", $this->year, PDO::PARAM_INT);
        $stmt_check->execute();
        $existing_id = $stmt_check->fetchColumn();

        if ($existing_id) {
            // Update existing record
            $query = "UPDATE " . $this->table_name . "
                      SET value = :value, input_by_user_id = :input_by_user_id
                      WHERE id = :id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":id", $existing_id);
        } else {
            // Create new record
            $query = "INSERT INTO " . $this->table_name . "
                      SET kpi_name = :kpi_name, value = :value, month = :month, year = :year, input_by_user_id = :input_by_user_id";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(":kpi_name", $this->kpi_name, PDO::PARAM_STR);
            $stmt->bindParam(":month", $this->month, PDO::PARAM_INT);
            $stmt->bindParam(":year", $this->year, PDO::PARAM_INT);
        }

        // Sanitize
        $this->value = htmlspecialchars(strip_tags($this->value));
        $this->input_by_user_id = htmlspecialchars(strip_tags($this->input_by_user_id));

        // Bind common values
        $stmt->bindParam(":value", $this->value, PDO::PARAM_STR);
        $stmt->bindParam(":input_by_user_id", $this->input_by_user_id, PDO::PARAM_INT);

        if ($stmt->execute()) {
            return true;
        }
        return false;
    }

    // Read a specific manual KPI input
    public function read($kpiName, $month, $year) {
        $query = "SELECT value FROM " . $this->table_name . "
                  WHERE kpi_name = :kpi_name AND month = :month AND year = :year
                  LIMIT 0,1";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":kpi_name", $kpiName, PDO::PARAM_STR);
        $stmt->bindParam(":month", $month, PDO::PARAM_INT);
        $stmt->bindParam(":year", $year, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row['value'] ?? null;
    }

    // Read all manual inputs (optional, for a management view)
    public function readAll() {
        $query = "SELECT kpi.id, kpi.kpi_name, kpi.value, kpi.month, kpi.year, u.nama_lengkap as input_by
                  FROM " . $this->table_name . " kpi
                  LEFT JOIN users u ON kpi.input_by_user_id = u.id_user
                  ORDER BY kpi.year DESC, kpi.month DESC, kpi.kpi_name ASC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getHistoryByUser($user_id) {
        $query = "SELECT 
                    kpi.kpi_name, kpi.value, kpi.month, kpi.year, kpi.created_at, kpi.updated_at
                  FROM " . $this->table_name . " kpi
                  WHERE kpi.input_by_user_id = :user_id
                  ORDER BY kpi.created_at DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":user_id", $user_id);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}