<?php

namespace App\Models;

use App\Core\Model;

class Receipt extends Model
{
    protected $table = 'receipts';
    protected $primaryKey = 'id';

    public function getByContribution($contributionId)
    {
        $stmt = $this->db->prepare("SELECT r.*, u.name as issued_by_name FROM {$this->table} r LEFT JOIN users u ON r.issued_by = u.id WHERE r.contribution_id = ? ORDER BY r.issued_date DESC");
        $stmt->execute([$contributionId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getByUser($userId)
    {
        $stmt = $this->db->prepare("SELECT r.*, ct.name as contribution_type_name, c.amount, c.payment_date, c.payment_method, c.paid_month FROM {$this->table} r JOIN contributions c ON r.contribution_id = c.id LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id WHERE c.user_id = ? ORDER BY r.issued_date DESC");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getByPeriod($userId, $from, $to)
    {
        $stmt = $this->db->prepare("SELECT r.*, ct.name as contribution_type_name, c.amount, c.payment_date, c.payment_method, c.paid_month FROM {$this->table} r JOIN contributions c ON r.contribution_id = c.id LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id WHERE c.user_id = ? AND c.payment_date >= ? AND c.payment_date <= ? ORDER BY r.issued_date DESC");
        $stmt->execute([$userId, $from, $to]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getWithDetails($id)
    {
        $stmt = $this->db->prepare("SELECT r.*, c.user_id, c.amount, c.payment_date, c.payment_method, c.transaction_ref, c.paid_month, c.notes as contribution_notes, ct.name as contribution_type_name, ct.frequency, u.name as user_name, u.first_name, u.last_name, u.email, issuer.name as issued_by_name FROM {$this->table} r JOIN contributions c ON r.contribution_id = c.id LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id JOIN users u ON c.user_id = u.id LEFT JOIN users issuer ON r.issued_by = issuer.id WHERE r.id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(\PDO::FETCH_OBJ);
    }

    public function generateNumber()
    {
        $year = date('Y');
        $stmt = $this->db->query("SELECT COUNT(*) as count FROM {$this->table} WHERE YEAR(issued_date) = {$year}");
        $count = $stmt->fetch(\PDO::FETCH_OBJ)->count + 1;
        return "GAS/RECEIPT/{$year}/" . str_pad($count, 5, '0', STR_PAD_LEFT);
    }
}
