<?php namespace App\Models;

use CodeIgniter\Model;

class WhatsappGroupMembershipModel extends Model
{
    protected $table         = 'whatsapp_group_memberships';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $allowedFields = [
        'class_section_id',
        'school_year',
        'semester',
        'subject_type', // 'primary' | 'second'
        'subject_id',   // users.id (primary) or parents.id (second)
        'is_member',    // 0/1
        'verified_by',
        'verified_at',
    ];
    protected $useTimestamps = true; // created_at, updated_at

    /**
     * Fetch all membership rows for the given sections and term.
     * Returns an associative index ["<sid>:<type>:<id>" => row].
     */
    public function getBySectionsAndTerm(array $sectionIds, string $year, string $sem): array
    {
        if (empty($sectionIds)) return [];

        $year = trim($year);
        $sem  = trim($sem);

        $qb = $this->asArray()
            ->whereIn('class_section_id', array_values(array_unique(array_map('intval', $sectionIds))))
            ->where('school_year', $year);

        if ($sem !== '') {
            $qb->where('semester', $sem);
        }

        // Prefer most recently updated membership when multiple rows exist (e.g., across semesters).
        $rows = $qb
            ->orderBy('updated_at', 'DESC')
            ->orderBy('id', 'DESC')
            ->findAll();

        $out = [];
        foreach ($rows as $r) {
            $k = sprintf('%d:%s:%d', (int)$r['class_section_id'], (string)$r['subject_type'], (int)$r['subject_id']);
            if (!isset($out[$k])) {
                $out[$k] = $r;
            }
        }
        return $out;
    }

    /**
     * Upsert membership row for a specific subject (parent) and class/term.
     */
    public function upsertMembership(int $classSectionId, string $year, string $sem, string $type, int $subjectId, bool $isMember, ?int $verifiedBy = null): int
    {
        $type = $type === 'second' ? 'second' : 'primary';
        $year = trim($year);
        $sem  = trim($sem);
        $payload = [
            'class_section_id' => $classSectionId,
            'school_year'      => $year,
            'semester'         => $sem,
            'subject_type'     => $type,
            'subject_id'       => $subjectId,
            'is_member'        => $isMember ? 1 : 0,
        ];

        if ($verifiedBy) {
            $payload['verified_by'] = $verifiedBy;
            $payload['verified_at'] = utc_now();
        }

        $existingQuery = $this->asArray()
            ->where('class_section_id', $payload['class_section_id'])
            ->where('school_year', $payload['school_year'])
            ->where('subject_type', $payload['subject_type'])
            ->where('subject_id', $payload['subject_id']);

        if ($sem !== '') {
            $existingQuery->where('semester', $payload['semester']);
        }

        $existing = $existingQuery
            ->orderBy('updated_at', 'DESC')
            ->orderBy('id', 'DESC')
            ->first();

        if ($existing) {
            if ($sem === '' && isset($existing['semester'])) {
                $payload['semester'] = (string)$existing['semester'];
            }
            $this->update((int)$existing['id'], $payload);
            return (int)$existing['id'];
        }

        return (int) $this->insert($payload, true);
    }
}
