<?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, with optional filtering and pagination
    public function getAllResponses($page = 1, $records_per_page = 10, $month = null, $year = null) {
        $query = "SELECT
                    sr.id, sq.question_text, sq.question_type, 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";
        
        $conditions = [];
        $params = [];

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

        if (!empty($conditions)) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

        $query .= " ORDER BY sr.created_at DESC";

        // Pagination
        $from_record_num = ($records_per_page * $page) - $records_per_page;
        $query .= " LIMIT :from_record_num, :records_per_page";

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

        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }
        $stmt->bindParam(":from_record_num", $from_record_num, PDO::PARAM_INT);
        $stmt->bindParam(":records_per_page", $records_per_page, PDO::PARAM_INT);

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

    // Count all responses with optional filtering
    public function countAllResponses($month = null, $year = null) {
        $query = "SELECT COUNT(*) as total_rows
                  FROM " . $this->table_responses . " sr";
        
        $conditions = [];
        $params = [];

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

        if (!empty($conditions)) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

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

        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }

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

        return $row['total_rows'];
    }

    // Get unique respondent submissions with optional filtering and pagination
    public function getUniqueRespondentSubmissions($page = 1, $records_per_page = 10, $month = null, $year = null) {
        $query = "SELECT
                    sr.nama, sr.unit, sr.email, DATE(sr.created_at) as submission_date, MIN(sr.created_at) as created_at_min
                  FROM " . $this->table_responses . " sr";
        
        $conditions = [];
        $params = [];

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

        if (!empty($conditions)) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

        $query .= " GROUP BY sr.nama, sr.unit, sr.email, submission_date
                    ORDER BY created_at_min DESC";

        // Pagination
        $from_record_num = ($records_per_page * $page) - $records_per_page;
        $query .= " LIMIT :from_record_num, :records_per_page";

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

        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }
        $stmt->bindParam(":from_record_num", $from_record_num, PDO::PARAM_INT);
        $stmt->bindParam(":records_per_page", $records_per_page, PDO::PARAM_INT);

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

    // Count unique respondent submissions with optional filtering
    public function countUniqueRespondentSubmissions($month = null, $year = null) {
        $query = "SELECT COUNT(DISTINCT CONCAT(sr.email, DATE(sr.created_at))) as total_rows
                  FROM " . $this->table_responses . " sr";
        
        $conditions = [];
        $params = [];

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

        if (!empty($conditions)) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

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

        foreach ($params as $key => &$val) {
            $stmt->bindParam($key, $val);
        }

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

        return $row['total_rows'];
    }

    // Get all responses for a specific respondent submission
    public function getResponsesByRespondentSubmission($email, $submission_timestamp) {
        $query = "SELECT
                    sr.id, sq.question_text, sq.question_type, 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
                  WHERE sr.email = :email AND sr.created_at = :submission_timestamp
                  ORDER BY sr.created_at ASC, sq.question_order ASC"; // Order to maintain logical question flow

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(":email", $email);
        $stmt->bindParam(":submission_timestamp", $submission_timestamp);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}