<?php
// Controllers/CustomerMergeController.php
// Merge customers based on Mobile + Email rule.
// Moves related followups, tasks, messages to primary customer and logs merge operations.

class CustomerMergeController
{
    protected $pdo;
    protected $loggerTable = 'sd_customer_merge_logs';

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Find duplicate groups using Mobile + Email pairs (both non-empty).
     * Returns array of groups: [ ['mobile'=>'..','email'=>'..','ids'=>[id1,id2,...] ], ... ]
     */
    public function findDuplicateGroups()
    {
        $sql = "SELECT mobile, email, GROUP_CONCAT(id ORDER BY created_at ASC) AS ids, COUNT(*) as cnt
                FROM sd_customers
                WHERE mobile IS NOT NULL AND mobile <> '' AND email IS NOT NULL AND email <> ''
                GROUP BY mobile, email
                HAVING cnt > 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $groups = [];
        foreach ($rows as $r) {
            $groups[] = [
                'mobile' => $r['mobile'],
                'email'  => $r['email'],
                'ids'    => array_map('intval', explode(',', $r['ids']))
            ];
        }
        return $groups;
    }

    /**
     * Merge a group of customer IDs into a chosen primary ID.
     *
     * @param int $primaryId   The id to keep
     * @param array $duplicateIds  Array of other ids to merge into primary
     * @param int|null $performedBy User id performing merge (for audit)
     * @return array report
     */
    public function mergeIntoPrimary($primaryId, array $duplicateIds, $performedBy = null)
    {
        if (!$primaryId || empty($duplicateIds)) {
            return ['error' => true, 'msg' => 'Invalid primary or duplicates list'];
        }

        // Ensure primary is not in duplicate list
        $duplicateIds = array_diff($duplicateIds, [$primaryId]);
        if (empty($duplicateIds)) {
            return ['error' => true, 'msg' => 'No duplicates to merge after removing primary id'];
        }

        try {
            $this->pdo->beginTransaction();

            // Verify primary exists
            $stmt = $this->pdo->prepare("SELECT * FROM sd_customers WHERE id = :id FOR UPDATE");
            $stmt->execute([':id' => $primaryId]);
            $primary = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$primary) {
                $this->pdo->rollBack();
                return ['error' => true, 'msg' => "Primary customer id {$primaryId} not found"];
            }

            // Load duplicates for audit and locking
            $in = implode(',', array_fill(0, count($duplicateIds), '?'));
            $stmt = $this->pdo->prepare("SELECT * FROM sd_customers WHERE id IN ($in) FOR UPDATE");
            $stmt->execute($duplicateIds);
            $dups = $stmt->fetchAll(PDO::FETCH_ASSOC);
            if (count($dups) !== count($duplicateIds)) {
                $this->pdo->rollBack();
                return ['error' => true, 'msg' => 'One or more duplicate IDs not found'];
            }

            // Reassign related tables that reference customer_id: sd_followups, sd_tasks, sd_messages (if present)
            $related = [
                'sd_followups' => 'customer_id',
                'sd_tasks'     => 'customer_id',
                'sd_messages'  => 'customer_id'
            ];

            $totalMoved = [];
            foreach ($related as $table => $col) {
                // Update rows pointing to any duplicate id -> primary id
                $sql = "UPDATE {$table} SET {$col} = :primary WHERE {$col} IN ($in)";
                $stmt = $this->pdo->prepare($sql);
                $params = array_merge([':primary' => $primaryId], $duplicateIds);
                // bind positional params after primary
                $stmt->execute($params);
                $totalMoved[$table] = $stmt->rowCount();
            }

            // Optionally merge other non-null fields from duplicate into primary when primary field empty
            // e.g., address, email (should be same), alternate_mobile etc.
            $fieldsToConsider = ['alternate_mobile','address','notes'];
            $updatePairs = [];
            $updateParams = [':id' => $primaryId];
            foreach ($fieldsToConsider as $f) {
                // only if primary field is empty/null
                if (empty($primary[$f])) {
                    // find first non-empty value among duplicates
                    $value = null;
                    foreach ($dups as $d) {
                        if (!empty($d[$f])) { $value = $d[$f]; break; }
                    }
                    if ($value !== null) {
                        $updatePairs[] = "{$f} = :{$f}";
                        $updateParams[":{$f}"] = $value;
                    }
                }
            }
            if ($updatePairs) {
                $sql = "UPDATE sd_customers SET " . implode(', ', $updatePairs) . " WHERE id = :id";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($updateParams);
            }

            // Delete duplicates from sd_customers
            $sql = "DELETE FROM sd_customers WHERE id IN ($in)";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($duplicateIds);
            $deletedCount = $stmt->rowCount();

            // Insert an audit log entry
            $logSql = "INSERT INTO {$this->loggerTable} 
                       (primary_customer_id, merged_customer_ids, performed_by, meta, created_at)
                       VALUES (:primary, :merged, :performed_by, :meta, NOW())";
            $meta = [
                'moved_counts' => $totalMoved,
                'primary_snapshot' => $primary,
                'duplicates_snapshot' => $dups
            ];
            $stmt = $this->pdo->prepare($logSql);
            $stmt->execute([
                ':primary' => $primaryId,
                ':merged' => json_encode(array_values($duplicateIds)),
                ':performed_by' => $performedBy ?: null,
                ':meta' => json_encode($meta)
            ]);

            $this->pdo->commit();

            return [
                'error' => false,
                'msg' => 'Merge successful',
                'primary' => $primaryId,
                'merged_count' => $deletedCount,
                'moved' => $totalMoved
            ];
        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            return ['error' => true, 'msg' => 'Merge failed: ' . $e->getMessage()];
        }
    }

    /**
     * Helper: run automatic merge for all detected groups.
     * Criteria: keep the oldest created (first id in group) as primary.
     * Returns list of merge reports.
     */
    public function autoMergeAll($performedBy = null)
    {
        $groups = $this->findDuplicateGroups();
        $reports = [];
        foreach ($groups as $g) {
            $ids = $g['ids'];
            // choose primary as first (ids were ordered by created_at asc in query)
            $primary = array_shift($ids); // returns primary id, $ids now duplicates
            $reports[] = [
                'mobile' => $g['mobile'],
                'email' => $g['email'],
                'result' => $this->mergeIntoPrimary($primary, $ids, $performedBy)
            ];
        }
        return $reports;
    }
}
