<?php
/**
 * Commission_Controller — کمیسیون ماده ۱۰۰
 *
 * نقشه واقعی جداول (نام‌ها گمراه‌کننده هستند):
 *   wp_gis_trepass      → پرونده‌ها (با NidCommission UUID)
 *   wp_gis_vote_result  → تخلفات واقعی (با NidTrepass, Title, MinPenalty, MaxPenalty, …)
 *   wp_gis_vote         → رأی‌های صادر شده
 *   wp_gis_vote_vs_trepass → رابط رأی↔تخلف
 *   wp_gis_commission   → خلاصه/جریمه‌ها (lookup ثانوی)
 */

if ( ! defined( 'ABSPATH' ) ) exit;

class Commission_Controller {

    public static function register_routes(): void {
        $ns = 'acc/v1';
        $p  = 'acc_jwt_check_permission';

        register_rest_route($ns, '/commission/by-code', [
            'methods' => 'GET', 'callback' => [self::class,'by_code'], 'permission_callback' => $p,
        ]);
        register_rest_route($ns, '/commission/stats', [
            'methods' => 'GET', 'callback' => [self::class,'stats'], 'permission_callback' => $p,
        ]);
        register_rest_route($ns, '/commission/search', [
            'methods' => 'GET', 'callback' => [self::class,'search'], 'permission_callback' => $p,
        ]);
        register_rest_route($ns, '/commission/(?P<nid>[a-fA-F0-9\-]+)', [
            'methods' => 'GET', 'callback' => [self::class,'detail'], 'permission_callback' => $p,
        ]);
    }

    /** همه پرونده‌های یک کد نوسازی (با تخلفات + رأی‌ها + خلاصه) */
    public static function by_code( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $code = sanitize_text_field( $req->get_param('code') ?? '' );
        if ( ! $code ) return new WP_REST_Response(['message' => 'code الزامی است'], 400);

        $cache_key = 'acc_com_by_code_' . md5( $code );
        $cached = get_transient( $cache_key );
        if ( $cached !== false ) return new WP_REST_Response($cached);

        // ── (1) پرونده‌ها از wp_gis_trepass ──
        $files = $wpdb->get_results( $wpdb->prepare(
            "SELECT NidCommission, UrbanNidKartablItem, OwnerName, Address, Regplaque,
                    CodeString, CommissionDate, UrbanRequestDate, ComEnterDate,
                    UsingGroup_Mojood, UsingTypeTitle, CI_CommissionType, CI_Commission,
                    ColorInKartabl, VoterUserName, ExpertName,
                    CurrentArea, DocArea, Density,
                    IsRun, IsMeeting, CommissionState,
                    Run_Comments, AllUrbanComments, CommissionComments,
                    PenaltyValue, OwnerCellNo, OwnerTelNo, OwnerNationalCode,
                    SecretariatNo, BackStateTitle, WorkflowTitel
             FROM {$pre}gis_trepass
             WHERE CodeString = %s
             ORDER BY CommissionDate DESC, UrbanRequestDate DESC
             LIMIT 100",
            $code
        ), ARRAY_A );

        if ( empty($files) ) {
            $result = ['code' => $code, 'files' => [], 'summary' => self::_empty_summary()];
            set_transient( $cache_key, $result, 2 * MINUTE_IN_SECONDS );
            return new WP_REST_Response($result);
        }

        $nids = array_filter( array_column( $files, 'NidCommission' ) );
        $placeholders = implode( ',', array_fill( 0, count($nids), '%s' ) );

        // ── (2) تخلفات واقعی از wp_gis_vote_result ──
        $trepass_all = $wpdb->get_results( $wpdb->prepare(
            "SELECT NidTrepass, NidCommission, Title, TrepassValue, Trepass_Comments,
                    MinPenalty, MaxPenalty, PenaltyFormula, K, KCoeffiecient, CI_Years,
                    UsingTitle, UsingGroupTitle, Floor,
                    AreaMojod, AreaMojaz, CreateDate, DescriptionOfSystem,
                    IsPaid, TrepassStatus, CalculationPrice
             FROM {$pre}gis_vote_result
             WHERE NidCommission IN ({$placeholders})
             ORDER BY CAST(Floor AS SIGNED) ASC, CreateDate ASC",
            ...$nids
        ), ARRAY_A ) ?? [];

        // ── (3) رأی‌ها از wp_gis_vote ──
        $votes_all = $wpdb->get_results( $wpdb->prepare(
            "SELECT NidVote, NidCommission, VoteNo, VoteDate, VoteTime,
                    CI_VoteType, CI_VoteTypeDetail, VoteValue,
                    Vote_Comments, VotePriority, TechnicalValue
             FROM {$pre}gis_vote
             WHERE NidCommission IN ({$placeholders})
             ORDER BY VoteDate ASC, CAST(VotePriority AS SIGNED) ASC",
            ...$nids
        ), ARRAY_A ) ?? [];

        // ── (4) رابط رأی↔تخلف ──
        $vt_all = [];
        if ( ! empty($votes_all) ) {
            $vote_nids = array_filter( array_column($votes_all, 'NidVote') );
            if ( ! empty($vote_nids) ) {
                $vp = implode( ',', array_fill( 0, count($vote_nids), '%s' ) );
                $vt_all = $wpdb->get_results( $wpdb->prepare(
                    "SELECT NidVote, NidTrepass FROM {$pre}gis_vote_vs_trepass
                     WHERE NidVote IN ({$vp})",
                    ...$vote_nids
                ), ARRAY_A ) ?? [];
            }
        }

        // ── (5) خلاصه از wp_gis_commission ──
        $summary_rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT UrbanNidKartablItem, OwnerName, Regplaque, CodeString,
                    VoteType, CommissionType, CI_VoteType, VoteNo, VoteDate,
                    votevalue, jarime, ci_commissionType, ParentTotal, UsingGroup
             FROM {$pre}gis_commission
             WHERE CodeString = %s
             ORDER BY VoteDate DESC",
            $code
        ), ARRAY_A ) ?? [];

        // ── گروه‌بندی ──
        $trepass_by_com = [];
        foreach ($trepass_all as $t) $trepass_by_com[ $t['NidCommission'] ][] = $t;

        $votes_by_com = [];
        foreach ($votes_all as $v) $votes_by_com[ $v['NidCommission'] ][] = $v;

        $vt_by_vote = [];
        foreach ($vt_all as $vt) $vt_by_vote[ $vt['NidVote'] ][] = $vt['NidTrepass'];

        $summary_by_urban = [];
        foreach ($summary_rows as $sr) $summary_by_urban[ $sr['UrbanNidKartablItem'] ][] = $sr;

        // ── ضمیمه به هر پرونده ──
        foreach ( $files as &$f ) {
            $f['trepasses']     = $trepass_by_com[ $f['NidCommission'] ] ?? [];
            $f['votes']         = $votes_by_com[ $f['NidCommission'] ] ?? [];
            $f['summary_rows']  = $summary_by_urban[ $f['UrbanNidKartablItem'] ] ?? [];

            // برای هر رأی، تخلف‌های مرتبط
            foreach ( $f['votes'] as &$v ) {
                $v['trepass_nids'] = $vt_by_vote[ $v['NidVote'] ] ?? [];
            }
            unset($v);

            $f['status_label']  = self::_file_status_label($f);
            $f['total_penalty'] = array_sum(array_map(fn($v) => (float)($v['VoteValue'] ?? 0), $f['votes']));
        }
        unset($f);

        $summary = self::_calc_summary( $files, $votes_all, $trepass_all, $summary_rows );

        $result = ['code' => $code, 'files' => $files, 'summary' => $summary];
        set_transient( $cache_key, $result, 5 * MINUTE_IN_SECONDS );
        return new WP_REST_Response($result);
    }

    /** جزئیات یک پرونده */
    public static function detail( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $nid = sanitize_text_field( $req->get_param('nid') ?? '' );
        if ( ! $nid ) return new WP_REST_Response(['message' => 'nid الزامی است'], 400);

        $file = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$pre}gis_trepass WHERE NidCommission = %s", $nid
        ), ARRAY_A );
        if ( ! $file ) return new WP_REST_Response(['message' => 'یافت نشد'], 404);

        $file['trepasses'] = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$pre}gis_vote_result WHERE NidCommission = %s ORDER BY Floor, CreateDate", $nid
        ), ARRAY_A ) ?? [];

        $file['votes'] = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$pre}gis_vote WHERE NidCommission = %s ORDER BY VoteDate", $nid
        ), ARRAY_A ) ?? [];

        return new WP_REST_Response(['data' => $file]);
    }

    /** آمار سریع */
    public static function stats( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $code = sanitize_text_field( $req->get_param('code') ?? '' );
        if ( ! $code ) return new WP_REST_Response(['message' => 'code الزامی است'], 400);

        $files_count   = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$pre}gis_trepass WHERE CodeString = %s", $code
        ) );
        $votes_count   = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(v.id) FROM {$pre}gis_vote v
             INNER JOIN {$pre}gis_trepass t ON t.NidCommission = v.NidCommission
             WHERE t.CodeString = %s", $code
        ) );
        $trepass_count = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$pre}gis_vote_result vr
             INNER JOIN {$pre}gis_trepass t ON t.NidCommission = vr.NidCommission
             WHERE t.CodeString = %s", $code
        ) );
        $summary_count = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$pre}gis_commission WHERE CodeString = %s", $code
        ) );

        return new WP_REST_Response([
            'files_count'   => $files_count,
            'votes_count'   => $votes_count,
            'trepass_count' => $trepass_count,
            'summary_count' => $summary_count,
        ]);
    }

    /** جستجو در پرونده‌ها */
    public static function search( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $q = sanitize_text_field( $req->get_param('q') ?? '' );
        if ( strlen($q) < 2 ) return new WP_REST_Response(['data' => []]);

        $like = '%' . $wpdb->esc_like($q) . '%';
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT NidCommission, CodeString, OwnerName, Address, Regplaque,
                    UrbanNidKartablItem, CommissionDate
             FROM {$pre}gis_trepass
             WHERE CodeString = %s
                OR OwnerName LIKE %s
                OR Regplaque LIKE %s
                OR Address   LIKE %s
                OR UrbanNidKartablItem = %s
             ORDER BY CommissionDate DESC
             LIMIT 50",
            $q, $like, $like, $like, $q
        ), ARRAY_A );

        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    /* ════════════ توابع داخلی ════════════ */
    private static function _empty_summary(): array {
        return [
            'files_count'    => 0,
            'votes_count'    => 0,
            'trepass_count'  => 0,
            'summary_count'  => 0,
            'total_penalty'  => 0,
            'first_date'     => null,
            'last_date'      => null,
        ];
    }

    private static function _calc_summary( array $files, array $votes, array $trepasses, array $summary_rows ): array {
        $total_penalty = 0;
        foreach ( $votes as $v ) $total_penalty += (float)($v['VoteValue'] ?? 0);
        if ( $total_penalty == 0 ) {
            foreach ( $summary_rows as $r ) $total_penalty += (float)($r['votevalue'] ?? 0);
        }

        $dates = [];
        foreach ($files as $f) {
            if (!empty($f['CommissionDate']))   $dates[] = $f['CommissionDate'];
            if (!empty($f['UrbanRequestDate'])) $dates[] = $f['UrbanRequestDate'];
        }
        sort($dates);

        return [
            'files_count'   => count($files),
            'votes_count'   => count($votes),
            'trepass_count' => count($trepasses),
            'summary_count' => count($summary_rows),
            'total_penalty' => $total_penalty,
            'first_date'    => $dates[0] ?? null,
            'last_date'     => end($dates) ?: null,
        ];
    }

    private static function _file_status_label( array $f ): string {
        if ( ! empty($f['IsRun']) && (string)$f['IsRun'] === '1' ) return 'اجرا شده';
        if ( empty($f['votes']) )         return 'بدون رأی';
        if ( empty($f['CommissionDate']) ) return 'در انتظار کمیسیون';
        return 'دارای رأی';
    }
}
