<?php
declare(strict_types=1);

require_once __DIR__ . '/../../app/Config/database.php';

/**
 * GST Model — SGAccount
 * -------------------------------------------------------------
 * Handles all GST (Goods and Services Tax) computations,
 * reporting, and filing summary tracking.
 * 
 * Compatible with IRAS GST requirements.
 * -------------------------------------------------------------
 */

class Gst
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::getConnection();
    }

    // ==========================================================
    // 📊 Compute GST Summary for a Period
    // ==========================================================
    public function getSummary(string $start, string $end): array
    {
        // Output GST from AR Invoices
        $stmtOut = $this->db->prepare("
            SELECT COALESCE(SUM(gst_amount), 0) AS total 
            FROM ar_invoices 
            WHERE invoice_date BETWEEN ? AND ?
        ");
        $stmtOut->execute([$start, $end]);
        $outputGst = (float)$stmtOut->fetchColumn();

        // Input GST from AP Bills
        $stmtIn = $this->db->prepare("
            SELECT COALESCE(SUM(gst_amount), 0) AS total 
            FROM ap_bills 
            WHERE bill_date BETWEEN ? AND ?
        ");
        $stmtIn->execute([$start, $end]);
        $inputGst = (float)$stmtIn->fetchColumn();

        // Net GST payable or claimable
        $net = $outputGst - $inputGst;

        return [
            'period_start' => $start,
            'period_end'   => $end,
            'output_gst'   => $outputGst,
            'input_gst'    => $inputGst,
            'net_gst'      => $net
        ];
    }

    // ==========================================================
    // 💾 Save GST Filing Summary
    // ==========================================================
    public function saveSummary(array $data): bool
    {
        $sql = "INSERT INTO gst_summary 
                (period_start, period_end, output_gst, input_gst) 
                VALUES (?, ?, ?, ?)";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            $data['period_start'] ?? null,
            $data['period_end']   ?? null,
            $data['output_gst']   ?? 0,
            $data['input_gst']    ?? 0
        ]);
    }

    // ==========================================================
    // 🧾 Retrieve Filing History
    // ==========================================================
    public function getHistory(): array
    {
        $stmt = $this->db->query("
            SELECT * FROM gst_summary 
            ORDER BY period_end DESC
        ");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // ==========================================================
    // 📋 Detailed Transaction Reports (Sales / Purchases)
    // ==========================================================
    public function getTransactions(string $start, string $end, string $type): array
    {
        if ($type === 'sales') {
            // 🔹 Output GST (Sales)
            $sql = "
                SELECT 
                    ar.invoice_no   AS ref_no,
                    c.name          AS name,
                    ar.invoice_date AS date,
                    ar.total_amount,
                    ar.gst_amount,
                    ar.gst_rate
                FROM ar_invoices ar
                LEFT JOIN customers c ON ar.customer_id = c.id
                WHERE ar.invoice_date BETWEEN ? AND ?
                ORDER BY ar.invoice_date ASC
            ";
        } else {
            // 🔸 Input GST (Purchases)
            $sql = "
                SELECT 
                    ap.bill_no      AS ref_no,
                    v.name          AS name,
                    ap.bill_date    AS date,
                    ap.total_amount,
                    ap.gst_amount,
                    ap.gst_rate
                FROM ap_bills ap
                LEFT JOIN vendors v ON ap.vendor_id = v.id
                WHERE ap.bill_date BETWEEN ? AND ?
                ORDER BY ap.bill_date ASC
            ";
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute([$start, $end]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
