<?php
declare(strict_types=1);

namespace App\Modules\Archive\Services;

use App\Core\App;
use App\Core\Logger;
use App\Modules\Archive\Models\ArchiveSnapshot;
use App\Modules\Archive\Models\MembershipNumberChain;

final class ArchiveService
{
    /**
     * Takes a complete snapshot of an entity and all its related data.
     * This is the core archive operation — called before major lifecycle events.
     *
     * @param string $entityType Table name or entity identifier (e.g., 'members', 'employees')
     * @param int $entityId Primary key of the entity
     * @param string $reason Why the snapshot is being taken
     * @param string|null $notes Optional human-readable notes
     * @param array|null $relatedData Optional pre-loaded related data
     * @param string|null $membershipNumber Optional membership number for cross-reference
     * @return int The snapshot ID
     */
    public static function takeSnapshot(
        string $entityType,
        int $entityId,
        string $reason,
        ?string $notes = null,
        ?array $relatedData = null,
        ?string $membershipNumber = null
    ): int {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();

        // Load the main entity record
        $entityData = $db->selectOne("SELECT * FROM `{$entityType}` WHERE id = ?", [$entityId]);
        if (!$entityData) {
            throw new \RuntimeException("Entity not found: {$entityType} #{$entityId}");
        }

        // If membership_number not provided, try to extract from entity data
        if ($membershipNumber === null && isset($entityData['membership_number'])) {
            $membershipNumber = $entityData['membership_number'];
        }

        // If related data not provided, try to auto-load based on entity type
        if ($relatedData === null) {
            $relatedData = self::autoLoadRelatedData($entityType, $entityId, $db);
        }

        $snapshotId = ArchiveSnapshot::create([
            'entity_type'       => $entityType,
            'entity_id'         => $entityId,
            'membership_number' => $membershipNumber,
            'snapshot_reason'   => $reason,
            'full_data_json'    => json_encode($entityData, JSON_UNESCAPED_UNICODE),
            'related_data_json' => $relatedData !== null ? json_encode($relatedData, JSON_UNESCAPED_UNICODE) : null,
            'snapshot_taken_by' => $employee ? (int) ($employee->id ?? 0) : null,
            'snapshot_taken_at' => date('Y-m-d H:i:s'),
            'notes'             => $notes,
        ]);

        Logger::info("Archive snapshot taken", [
            'snapshot_id'  => $snapshotId,
            'entity_type'  => $entityType,
            'entity_id'    => $entityId,
            'reason'       => $reason,
        ]);

        return $snapshotId;
    }

    /**
     * Auto-loads related data for known entity types.
     * For unknown types, returns null (caller can provide their own).
     */
    private static function autoLoadRelatedData(string $entityType, int $entityId, $db): ?array
    {
        $related = [];

        switch ($entityType) {
            case 'members':
                // Load spouses if table exists
                if ($db->tableExists('spouses')) {
                    $related['spouses'] = $db->select(
                        "SELECT * FROM spouses WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                // Load children if table exists
                if ($db->tableExists('children')) {
                    $related['children'] = $db->select(
                        "SELECT * FROM children WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                // Load temporary members if table exists
                if ($db->tableExists('temporary_members')) {
                    $related['temporary_members'] = $db->select(
                        "SELECT * FROM temporary_members WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                // Load payments if table exists
                if ($db->tableExists('payments')) {
                    $related['payments'] = $db->select(
                        "SELECT * FROM payments WHERE member_id = ? AND is_voided = 0 ORDER BY payment_date DESC",
                        [$entityId]
                    );
                }
                // Load installment plans if table exists
                if ($db->tableExists('installment_plans')) {
                    $related['installment_plans'] = $db->select(
                        "SELECT * FROM installment_plans WHERE member_id = ?",
                        [$entityId]
                    );
                }
                // Load subscriptions if table exists
                if ($db->tableExists('subscriptions')) {
                    $related['subscriptions'] = $db->select(
                        "SELECT * FROM subscriptions WHERE member_id = ? ORDER BY financial_year DESC",
                        [$entityId]
                    );
                }
                // Load fines if table exists
                if ($db->tableExists('fines')) {
                    $related['fines'] = $db->select(
                        "SELECT * FROM fines WHERE member_id = ?",
                        [$entityId]
                    );
                }
                // Load documents if table exists
                if ($db->tableExists('documents')) {
                    $related['documents'] = $db->select(
                        "SELECT id, member_id, document_type, original_filename, file_path, uploaded_at FROM documents WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                // Load installment cheques (linked via installment_plans)
                if ($db->tableExists('installment_cheques') && !empty($related['installment_plans'])) {
                    $planIds = array_column($related['installment_plans'], 'id');
                    if (!empty($planIds)) {
                        $placeholders = implode(',', array_fill(0, count($planIds), '?'));
                        $related['installment_cheques'] = $db->select(
                            "SELECT * FROM installment_cheques WHERE installment_plan_id IN ({$placeholders}) ORDER BY cheque_date ASC",
                            $planIds
                        );
                    }
                }
                // Load installment schedule (linked via installment_plans)
                if ($db->tableExists('installment_schedule') && !empty($related['installment_plans'])) {
                    $planIds = array_column($related['installment_plans'], 'id');
                    if (!empty($planIds)) {
                        $placeholders = implode(',', array_fill(0, count($planIds), '?'));
                        $related['installment_schedule'] = $db->select(
                            "SELECT * FROM installment_schedule WHERE installment_plan_id IN ({$placeholders}) ORDER BY due_date ASC",
                            $planIds
                        );
                    }
                }
                // Load seasonal if table exists
                if ($db->tableExists('seasonal_memberships')) {
                    $related['seasonal_memberships'] = $db->select(
                        "SELECT * FROM seasonal_memberships WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                // Load sports if table exists
                if ($db->tableExists('sports_members')) {
                    $related['sports_members'] = $db->select(
                        "SELECT * FROM sports_members WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                // Load honorary if table exists
                if ($db->tableExists('honorary_members')) {
                    $related['honorary_members'] = $db->select(
                        "SELECT * FROM honorary_members WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                // Load foreign details if table exists
                if ($db->tableExists('foreign_member_details')) {
                    $related['foreign_member_details'] = $db->select(
                        "SELECT * FROM foreign_member_details WHERE member_id = ? AND is_archived = 0",
                        [$entityId]
                    );
                }
                break;

            case 'employees':
                // Load employee roles
                $related['roles'] = $db->select(
                    "SELECT er.*, r.role_code, r.name_ar FROM employee_roles er JOIN roles r ON r.id = er.role_id WHERE er.employee_id = ? AND er.is_active = 1",
                    [$entityId]
                );
                break;

            case 'roles':
                // Load role permissions
                $related['permissions'] = $db->select(
                    "SELECT * FROM role_permissions WHERE role_id = ?",
                    [$entityId]
                );
                break;

            default:
                // For unknown entity types, return empty array — caller can provide their own
                return !empty($related) ? $related : null;
        }

        return !empty($related) ? $related : null;
    }

    /**
     * Get all snapshots for an entity.
     */
    public static function getSnapshots(string $entityType, int $entityId): array
    {
        return ArchiveSnapshot::getForEntity($entityType, $entityId);
    }

    /**
     * Get a single snapshot with decoded JSON.
     */
    public static function getSnapshot(int $snapshotId): ?array
    {
        $snapshot = ArchiveSnapshot::find($snapshotId);
        if (!$snapshot) {
            return null;
        }

        $snapshot['full_data'] = json_decode($snapshot['full_data_json'] ?? '{}', true) ?? [];
        $snapshot['related_data'] = json_decode($snapshot['related_data_json'] ?? 'null', true);

        return $snapshot;
    }

    /**
     * Compare two snapshots and return the differences.
     */
    public static function compareSnapshots(int $snapshotId1, int $snapshotId2): array
    {
        $snap1 = self::getSnapshot($snapshotId1);
        $snap2 = self::getSnapshot($snapshotId2);

        if (!$snap1 || !$snap2) {
            throw new \RuntimeException('One or both snapshots not found');
        }

        $diff = [
            'snapshot_1' => [
                'id'        => $snap1['id'],
                'reason'    => $snap1['snapshot_reason'],
                'taken_at'  => $snap1['snapshot_taken_at'],
            ],
            'snapshot_2' => [
                'id'        => $snap2['id'],
                'reason'    => $snap2['snapshot_reason'],
                'taken_at'  => $snap2['snapshot_taken_at'],
            ],
            'main_entity_diff' => self::diffArrays($snap1['full_data'], $snap2['full_data']),
            'related_diff'     => self::diffRelatedData(
                $snap1['related_data'] ?? [],
                $snap2['related_data'] ?? []
            ),
        ];

        return $diff;
    }

    /**
     * Recursively diff two associative arrays.
     * Returns fields that changed with old/new values.
     */
    public static function diffArrays(array $old, array $new): array
    {
        $diff = [];
        $allKeys = array_unique(array_merge(array_keys($old), array_keys($new)));

        foreach ($allKeys as $key) {
            $oldVal = $old[$key] ?? null;
            $newVal = $new[$key] ?? null;

            if (is_array($oldVal) && is_array($newVal)) {
                $subDiff = self::diffArrays($oldVal, $newVal);
                if (!empty($subDiff)) {
                    $diff[$key] = $subDiff;
                }
            } elseif ((string) $oldVal !== (string) $newVal) {
                $diff[$key] = [
                    'old' => $oldVal,
                    'new' => $newVal,
                ];
            }
        }

        return $diff;
    }

    /**
     * Diff related data sections.
     */
    private static function diffRelatedData(?array $old, ?array $new): array
    {
        $diff = [];
        $old = $old ?? [];
        $new = $new ?? [];
        $allSections = array_unique(array_merge(array_keys($old), array_keys($new)));

        foreach ($allSections as $section) {
            $oldSection = $old[$section] ?? [];
            $newSection = $new[$section] ?? [];

            $oldCount = count($oldSection);
            $newCount = count($newSection);

            if ($oldCount !== $newCount) {
                $diff[$section] = [
                    'count_changed' => true,
                    'old_count'     => $oldCount,
                    'new_count'     => $newCount,
                ];
            }

            // Compare individual records by ID if available
            $oldById = [];
            $newById = [];
            foreach ($oldSection as $item) {
                $id = $item['id'] ?? null;
                if ($id !== null) {
                    $oldById[$id] = $item;
                }
            }
            foreach ($newSection as $item) {
                $id = $item['id'] ?? null;
                if ($id !== null) {
                    $newById[$id] = $item;
                }
            }

            // Added records
            $addedIds = array_diff(array_keys($newById), array_keys($oldById));
            if (!empty($addedIds)) {
                $diff[$section]['added'] = array_values(array_intersect_key($newById, array_flip($addedIds)));
            }

            // Removed records
            $removedIds = array_diff(array_keys($oldById), array_keys($newById));
            if (!empty($removedIds)) {
                $diff[$section]['removed'] = array_values(array_intersect_key($oldById, array_flip($removedIds)));
            }

            // Changed records
            $commonIds = array_intersect(array_keys($oldById), array_keys($newById));
            foreach ($commonIds as $id) {
                $fieldDiff = self::diffArrays($oldById[$id], $newById[$id]);
                if (!empty($fieldDiff)) {
                    $diff[$section]['changed'][$id] = $fieldDiff;
                }
            }
        }

        return $diff;
    }

    /**
     * Record a membership number transfer in the chain.
     */
    public static function recordNumberTransfer(
        string $membershipNumber,
        string $holderType,
        string $entityType,
        int $entityId,
        ?int $previousHolderId = null
    ): int {
        // End the current holder's record
        MembershipNumberChain::endCurrentHolder($membershipNumber);

        // Create new chain entry
        $chainId = MembershipNumberChain::create([
            'membership_number'  => $membershipNumber,
            'holder_type'        => $holderType,
            'holder_entity_type' => $entityType,
            'holder_entity_id'   => $entityId,
            'previous_holder_id' => $previousHolderId,
            'held_from'          => date('Y-m-d H:i:s'),
            'held_until'         => null,
            'created_at'         => date('Y-m-d H:i:s'),
        ]);

        Logger::info("Membership number chain updated", [
            'membership_number' => $membershipNumber,
            'holder_type'       => $holderType,
            'entity_type'       => $entityType,
            'entity_id'         => $entityId,
            'chain_id'          => $chainId,
        ]);

        return $chainId;
    }

    /**
     * Get the complete ownership chain for a membership number.
     */
    public static function getNumberChain(string $membershipNumber): array
    {
        return MembershipNumberChain::getChainForNumber($membershipNumber);
    }

    /**
     * Get the current holder of a membership number.
     */
    public static function getCurrentHolder(string $membershipNumber): ?array
    {
        return MembershipNumberChain::getCurrentHolder($membershipNumber);
    }
}