<?php

class Survey {
    private $conn;
    private $table_questions = "survey_questions";
    private $table_responses = "survey_responses";

    public $id;
    public $question_text;
    public $question_type;
    public $question_order;
    public $response_value;
    public $survey_question_id;
    public $nama; // New
    public $unit; // New
    public $email; // New

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

    // Get all survey questions
    public function getQuestions() {
        $query = "SELECT id, question_text, question_type FROM " . $this->table_questions . " ORDER BY question_order ASC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // Save a single survey response
    public function saveResponse() {
        $query = "INSERT INTO " . $this->table_responses . "
                  SET
                    survey_question_id = :survey_question_id,
                    response_value = :response_value";

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

        // Sanitize
        $this->survey_question_id = htmlspecialchars(strip_tags($this->survey_question_id));
        $this->response_value = htmlspecialchars(strip_tags($this->response_value));

        // Bind values
        $stmt->bindParam(":survey_question_id", $this->survey_question_id);
        $stmt->bindParam(":response_value", $this->response_value);

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

    public function saveResponseWithUserInfo($nama, $unit, $email) {
        $query = "INSERT INTO " . $this->table_responses . "
                  SET
                    survey_question_id = :survey_question_id,
                    response_value = :response_value,
                    nama = :nama,
                    unit = :unit,
                    email = :email";

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

        // Sanitize existing fields
        $this->survey_question_id = htmlspecialchars(strip_tags($this->survey_question_id));
        $this->response_value = htmlspecialchars(strip_tags($this->response_value));

        // Sanitize new fields
        $nama = htmlspecialchars(strip_tags($nama));
        $unit = htmlspecialchars(strip_tags($unit));
        $email = htmlspecialchars(strip_tags($email));

        // Bind values
        $stmt->bindParam(":survey_question_id", $this->survey_question_id);
        $stmt->bindParam(":response_value", $this->response_value);
        $stmt->bindParam(":nama", $nama);
        $stmt->bindParam(":unit", $unit);
        $stmt->bindParam(":email", $email);

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

    // Get average rating for a specific question or overall
    public function getAverageRating($question_id = null, $month = null, $year = null) {
        $query = "SELECT AVG(response_value) as avg_rating
                  FROM " . $this->table_responses . " sr
                  WHERE sr.survey_question_id IN (SELECT id FROM " . $this->table_questions . " WHERE question_type = 'rating_5')";
        
        $params = [];

        if ($question_id) {
            $query .= " AND sr.survey_question_id = :question_id";
            $params[':question_id'] = $question_id;
        }

        if ($month && $year) {
            $query .= " AND MONTH(sr.created_at) = :month AND YEAR(sr.created_at) = :year";
            $params[':month'] = $month;
            $params[':year'] = $year;
        }

        $stmt = $this->conn->prepare($query);
        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result['avg_rating'] ?? 0;
    }

    // Get all responses with question text
    public function getAllResponses() {
        $query = "SELECT
                    sr.id, sq.question_text, sr.response_value, sr.created_at, sr.nama, sr.unit, sr.email
                  FROM " . $this->table_responses . " sr
                  LEFT JOIN " . $this->table_questions . " sq ON sr.survey_question_id = sq.id
                  ORDER BY sr.created_at DESC"; // Ordering by created_at still makes sense for a raw list
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}