<?php

namespace App\Database\Seeds;

use CodeIgniter\Database\Seeder;
use CodeIgniter\Database\BaseConnection;

class FamiliesBootstrapSeeder extends Seeder
{
    /** @var BaseConnection */
    protected $db;

    // Map your legacy `parents` table/columns
    protected array $legacy = [
        'table'          => 'parents',                 // legacy table
        'firstparent_id' => 'firstparent_id',          // users.id (primary parent)
        'second_user_id' => 'secondparent_id',         // users.id of 2nd parent if present (may be 0)
        'email'          => 'secondparent_email',
        'firstname'      => 'secondparent_firstname',
        'lastname'       => 'secondparent_lastname',
    ];

    public function run()
    {
        $this->db = \Config\Database::connect();

        // While tuning, you can comment these two lines to avoid full rollback
        $this->db->transStart();

        [$familiesCreated, $studentsLinked, $primGuardiansLinked] = $this->bootstrapByPrimaryParents();
        [$createdUsers, $secGuardiansLinked, $skippedLegacy, $legacyTotal] = $this->importLegacySecondParents();

        $this->db->transComplete();

        echo PHP_EOL;
        echo "Families created:       {$familiesCreated}\n";
        echo "Students linked:        {$studentsLinked}\n";
        echo "Primary guardians:      {$primGuardiansLinked}\n";
        echo "Legacy rows scanned:    {$legacyTotal}\n";
        echo "Stub users created:     {$createdUsers}\n";
        echo "Secondary guardians:    {$secGuardiansLinked}\n";
        echo "Legacy rows skipped:    {$skippedLegacy}\n";
        echo PHP_EOL;
    }

    /** Create/ensure one family per primary parent (students.parent_id), link students, add primary guardian. */
    protected function bootstrapByPrimaryParents(): array
    {
        $familiesCreated = 0; $studentsLinked = 0; $primGuardiansLinked = 0;

        $primaryParents = $this->db->query(
            "SELECT DISTINCT parent_id FROM students WHERE parent_id IS NOT NULL"
        )->getResultArray();

        foreach ($primaryParents as $row) {
            $primaryUserId = (int) $row['parent_id'];
            if ($primaryUserId <= 0) { continue; }

            $familyId = $this->ensureFamilyForPrimaryParent($primaryUserId, $familiesCreated);

            // link all kids of this primary parent
            $kids = $this->db->query("SELECT id FROM students WHERE parent_id = ?", [$primaryUserId])->getResultArray();
            foreach ($kids as $s) {
                $studentsLinked += $this->attachStudentToFamily((int)$s['id'], $familyId);
            }

            // ensure primary guardian link
            $primGuardiansLinked += $this->attachGuardianUser($familyId, $primaryUserId, 'primary', 1, 1, 0);
        }

        return [$familiesCreated, $studentsLinked, $primGuardiansLinked];
    }

    /** Import second parents from legacy `parents` table (by email or secondparent_id). */
    protected function importLegacySecondParents(): array
    {
        $L = $this->legacy;
        $createdUsers = 0; $linked = 0; $skipped = 0; $total = 0;

        if (! $this->db->tableExists($L['table'])) {
            echo "[LEGACY] Table '{$L['table']}' not found; skipping.\n";
            return [0,0,0,0];
        }

        $fields = array_map('strtolower', array_column($this->db->getFieldData($L['table']), 'name'));
        foreach (['firstparent_id','email','firstname','lastname','second_user_id'] as $k) {
            if (! array_key_exists($k, $L)) continue;
            $col = strtolower($L[$k]);
            if ($col && ! in_array($col, $fields, true)) {
                echo "[LEGACY] Missing column {$L[$k]} in {$L['table']}; skipping import.\n";
                return [0,0,0,0];
            }
        }

        $qb = $this->db->table($L['table']);
        $qb->select("{$L['firstparent_id']} AS firstparent_id", false)
           ->select("{$L['second_user_id']} AS secondparent_id", false)
           ->select("{$L['email']} AS email", false)
           ->select("{$L['firstname']} AS firstname", false)
           ->select("{$L['lastname']} AS lastname", false)
           ->where("{$L['email']} IS NOT NULL", null, false)
           ->where("{$L['email']} !=", '');

        $q = $qb->get();
        if ($q === false) {
            $err = $this->db->error();
            echo "[LEGACY] Query failed: {$err['code']} {$err['message']}\n";
            return [0,0,0,0];
        }

        $rows = $q->getResultArray();
        $total = count($rows);

        foreach ($rows as $r) {
            $primaryUserId = (int)($r['firstparent_id'] ?? 0);
            $email         = trim((string)($r['email'] ?? ''));
            $first         = trim((string)($r['firstname'] ?? ''));
            $last          = trim((string)($r['lastname'] ?? ''));
            $secondUserId  = (int)($r['secondparent_id'] ?? 0);

            if ($primaryUserId <= 0 || $email === '') { $skipped++; continue; }

            // students for this primary parent
            $students = $this->db->query("SELECT id FROM students WHERE parent_id = ?", [$primaryUserId])->getResultArray();
            if (!$students) { $skipped++; continue; }

            // resolve/create the second parent user
            $userRow = null;
            if ($secondUserId > 0) {
                $userRow = $this->db->table('users')->select('id, firstname, lastname')->where('id', $secondUserId)->get()->getRowArray();
            }
            if (!$userRow) {
                $userRow = $this->db->table('users')->select('id, firstname, lastname')->where('email', $email)->get()->getRowArray();
            }

            if (!$userRow) {
                // STUB USER with safe defaults for your NOT NULL columns
                $this->db->table('users')->insert([
                    'firstname'            => $first ?: '(Parent)',
                    'lastname'             => $last  ?: '(Unspecified)',
                    'email'                => $email,
                    'cellphone'            => '',
                    'address_street'       => '',
                    'apt'                  => null,
                    'city'                 => '',
                    'state'                => '',
                    'zip'                  => '',
                    'accept_school_policy' => 0,
                    'is_verified'          => 0,
                    'status'               => 'Active',
                    'is_suspended'         => 0,
                    'failed_attempts'      => 0,
                    'password'             => password_hash(bin2hex(random_bytes(8)), PASSWORD_BCRYPT),
                    'created_at'           => utc_now(),
                    'updated_at'           => utc_now(),
                    'token'                => null,
                    'account_id'           => null,
                    'user_type'            => 'primary',
                    'semester'             => '',
                    'school_year'          => null,
                    'rfid_tag'             => null,
                    'last_failed_at'       => null,
                ]);
                if ($this->db->affectedRows() === 0) {
                    $err = $this->db->error();
                    echo "[ERROR] users insert failed for {$email}: {$err['code']} {$err['message']}\n";
                    $skipped++; continue;
                }
                $secondUserId = (int)$this->db->insertID();
                $createdUsers++;
                $userRow = ['id' => $secondUserId, 'firstname' => $first, 'lastname' => $last];
            } else {
                $secondUserId = (int)$userRow['id'];
                if (empty($userRow['firstname']) || empty($userRow['lastname'])) {
                    $this->db->table('users')->where('id', $secondUserId)->update([
                        'firstname' => $userRow['firstname'] ?: ($first ?: '(Parent)'),
                        'lastname'  => $userRow['lastname']  ?: ($last  ?: '(Unspecified)'),
                    ]);
                }
            }

            // link second parent to each student’s family
            foreach ($students as $s) {
                $studentId = (int)$s['id'];
                $familyId = $this->familyIdFromStudentPrimary($studentId);
                if (!$familyId) {
                    $familyId = $this->ensureFamilyForPrimaryParent($primaryUserId, $dummy = 0);
                    $this->attachStudentToFamily($studentId, $familyId);
                }
                $linked += $this->attachGuardianUser($familyId, $secondUserId, 'secondary', 0, 1, 0);
            }
        }

        return [$createdUsers, $linked, $skipped, $total];
    }

    // ---------- Helpers ----------

    /** Ensure there is exactly one family per primary parent user. */
    protected function ensureFamilyForPrimaryParent(int $primaryUserId, int &$createdCounter): int
    {
        $code = 'FAM-' . $primaryUserId;
        $row = $this->db->query("SELECT id FROM families WHERE family_code = ?", [$code])->getRowArray();
        if ($row) return (int)$row['id'];

        $this->db->table('families')->insert([
            'family_code'    => $code,
            'household_name' => 'Family of User ' . $primaryUserId,
            'is_active'      => 1,
        ]);
        $createdCounter++;
        return (int)$this->db->insertID();
    }

    /** Idempotent link student → family. */
    protected function attachStudentToFamily(int $studentId, int $familyId): int
    {
        $this->db->query(
            "INSERT IGNORE INTO family_students (family_id, student_id, is_primary_home) VALUES (?, ?, 1)",
            [$familyId, $studentId]
        );
        return $this->db->affectedRows();
    }

    /** Idempotent link guardian user → family. */
    protected function attachGuardianUser(int $familyId, int $userId, string $relation, int $isPrimary, int $receiveEmails, int $receiveSms): int
    {
        $this->db->query(
            "INSERT IGNORE INTO family_guardians (family_id, user_id, relation, is_primary, receive_emails, receive_sms) VALUES (?, ?, ?, ?, ?, ?)",
            [$familyId, $userId, $relation, $isPrimary, $receiveEmails, $receiveSms]
        );
        return $this->db->affectedRows();
    }

    /** Canonical family for a student based on parent_id → FAM-{parent_id}, fallback to any linked family. */
    protected function familyIdFromStudentPrimary(int $studentId): ?int
    {
        $row = $this->db->query("SELECT parent_id FROM students WHERE id = ?", [$studentId])->getRowArray();
        if (!$row || empty($row['parent_id'])) return null;

        $code = 'FAM-' . (int)$row['parent_id'];
        $fam = $this->db->query("SELECT id FROM families WHERE family_code = ?", [$code])->getRowArray();
        if ($fam) return (int)$fam['id'];

        $fam2 = $this->db->query(
            "SELECT family_id FROM family_students WHERE student_id = ? ORDER BY is_primary_home DESC LIMIT 1",
            [$studentId]
        )->getRowArray();
        return $fam2 ? (int)$fam2['family_id'] : null;
    }
}