<?php
/**
 * Browser-safe preview/apply wrapper for wrongly successful leads.
 *
 * Preview:
 *   /backfill_lead_sale_status_without_payment.php?key=secret123
 *
 * Apply:
 *   /backfill_lead_sale_status_without_payment.php?key=secret123&apply=1&confirm=RESET
 */

$expectedKey = getenv('MAINTENANCE_KEY') ?: 'secret123';
$providedKey = $_GET['key'] ?? '';

if (!hash_equals((string)$expectedKey, (string)$providedKey)) {
    http_response_code(403);
    header('Content-Type: text/html; charset=UTF-8');
    echo '<!doctype html><meta charset="utf-8"><title>Forbidden</title><h1>Forbidden</h1><p>Invalid maintenance key.</p>';
    exit;
}

header('Content-Type: text/html; charset=UTF-8');

try {
    require_once __DIR__ . '/../vendor/autoload.php';
    $app = require __DIR__ . '/../app/bootstrap.php';
    $pdo = $app->db->getPdo();
} catch (Throwable $e) {
    http_response_code(500);
    echo '<!doctype html><html lang="fa" dir="rtl"><meta charset="utf-8"><title>Backfill error</title>';
    echo '<body style="font-family:Tahoma,Arial,sans-serif;margin:24px">';
    echo '<h1>Backfill bootstrap failed</h1>';
    echo '<pre style="direction:ltr;text-align:left;white-space:pre-wrap;background:#f7f7f8;border:1px solid #ddd;padding:12px">';
    echo htmlspecialchars($e->getMessage(), ENT_QUOTES, 'UTF-8');
    echo '</pre></body></html>';
    exit;
}

$scope = $_GET['scope'] ?? 'all';
$apply = ($_GET['apply'] ?? '') === '1';
$undo = ($_GET['undo'] ?? '') === '1';
$confirmed = ($_GET['confirm'] ?? '') === 'RESET';
$undoConfirmed = ($_GET['confirm'] ?? '') === 'UNDO';
$limit = max(0, (int)($_GET['limit'] ?? 0));
$createdFrom = trim((string)($_GET['created_from'] ?? ''));
$createdTo = trim((string)($_GET['created_to'] ?? ''));
$selectedBackupTable = trim((string)($_GET['backup_table'] ?? ''));
$message = null;
$error = null;
$applyResult = null;
$undoResult = null;
$backupTables = [];

if (!in_array($scope, ['all', 'requested-services'], true)) {
    $scope = 'all';
    $error = 'Invalid scope was reset to all.';
}

function bf_h($value)
{
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

function bf_normalizeDateInput($value)
{
    if ($value === '') {
        return '';
    }
    $date = DateTime::createFromFormat('!Y-m-d', $value);
    return $date && $date->format('Y-m-d') === $value ? $value : null;
}

function bf_getBackupTables(PDO $pdo)
{
    $stmt = $pdo->query("SHOW TABLES LIKE 'lead_sale_status_backfill_%'");
    $tables = [];
    foreach ($stmt->fetchAll(PDO::FETCH_NUM) as $row) {
        $table = (string)($row[0] ?? '');
        if (preg_match('/^lead_sale_status_backfill_[0-9]{8}_[0-9]{6}$/', $table)) {
            $tables[] = $table;
        }
    }
    rsort($tables);
    return $tables;
}

$createdFrom = bf_normalizeDateInput($createdFrom);
$createdTo = bf_normalizeDateInput($createdTo);
if ($createdFrom === null || $createdTo === null) {
    $error = 'Invalid date filter. Use YYYY-MM-DD.';
    $createdFrom = '';
    $createdTo = '';
}

$createdSql = '';
if ($createdFrom !== '') {
    $createdSql .= " AND p.register_time >= " . $pdo->quote($createdFrom . ' 00:00:00');
}
if ($createdTo !== '') {
    $toExclusive = (new DateTime($createdTo))->modify('+1 day')->format('Y-m-d 00:00:00');
    $createdSql .= " AND p.register_time < " . $pdo->quote($toExclusive);
}

$scopeSql = '';
if ($scope === 'requested-services') {
    $scopeSql = "
      AND EXISTS (
          SELECT 1
          FROM deals ds
          WHERE ds.contact_didar_id = p.didar_contact_id
            AND COALESCE(ds.status, 'Pending') <> 'Won'
            AND NULLIF(TRIM(COALESCE(ds.requested_services, '')), '') IS NOT NULL
            AND TRIM(COALESCE(ds.requested_services, '')) <> '[]'
      )";
}

$candidateWhere = "
    COALESCE(p.sale_status, 0) = 1
    {$scopeSql}
    {$createdSql}
    AND NOT EXISTS (
        SELECT 1
        FROM deals d_won
        WHERE d_won.contact_didar_id = p.didar_contact_id
          AND d_won.status = 'Won'
    )
    AND NOT EXISTS (
        SELECT 1
        FROM deals d_tx
        INNER JOIN transactions t ON t.deal_id = d_tx.id
        WHERE d_tx.contact_didar_id = p.didar_contact_id
          AND t.status = 'confirmed'
          AND COALESCE(t.amount, 0) > 0
    )
    AND NOT EXISTS (
        SELECT 1
        FROM deals d_paid
        WHERE d_paid.contact_didar_id = p.didar_contact_id
          AND (
              COALESCE(d_paid.payment_amount, 0) > 0
              OR (
                  NULLIF(TRIM(COALESCE(d_paid.payments, '')), '') IS NOT NULL
                  AND TRIM(COALESCE(d_paid.payments, '')) <> '[]'
                  AND d_paid.payments REGEXP '\"amount\"[[:space:]]*:[[:space:]]*\"?[1-9]'
              )
          )
    )
";

$limitSql = $limit > 0 ? " LIMIT {$limit}" : '';

$summarySql = "
    SELECT
        COUNT(*) AS candidates,
        SUM(EXISTS (SELECT 1 FROM deals d WHERE d.contact_didar_id = p.didar_contact_id)) AS with_any_deal,
        SUM(EXISTS (
            SELECT 1 FROM deals d
            WHERE d.contact_didar_id = p.didar_contact_id
              AND NULLIF(TRIM(COALESCE(d.requested_services, '')), '') IS NOT NULL
              AND TRIM(COALESCE(d.requested_services, '')) <> '[]'
        )) AS with_requested_service_deal,
        SUM(NULLIF(TRIM(COALESCE(p.customer_products, '')), '') IS NOT NULL) AS with_customer_products
    FROM persons p
    WHERE {$candidateWhere}
";

$sampleSql = "
    SELECT
        p.didar_contact_id,
        p.customer_code,
        TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))) AS lead_name,
        p.mobile_phone,
        p.register_time AS lead_created_at,
        p.customer_products,
        (SELECT COUNT(*) FROM deals d WHERE d.contact_didar_id = p.didar_contact_id) AS deal_count,
        (SELECT MAX(d.register_time) FROM deals d WHERE d.contact_didar_id = p.didar_contact_id) AS last_deal_at,
        (SELECT GROUP_CONCAT(DISTINCT d.status ORDER BY d.status SEPARATOR ',')
         FROM deals d WHERE d.contact_didar_id = p.didar_contact_id) AS deal_statuses
    FROM persons p
    WHERE {$candidateWhere}
    ORDER BY p.register_time DESC, p.id DESC
    LIMIT 50
";

try {
    $backupTables = bf_getBackupTables($pdo);

    if ($undo && !$undoConfirmed) {
        $error = 'Undo was not run. Type UNDO in the confirmation field.';
        $undo = false;
    }

    if ($undo) {
        if (!preg_match('/^lead_sale_status_backfill_[0-9]{8}_[0-9]{6}$/', $selectedBackupTable)
            || !in_array($selectedBackupTable, $backupTables, true)) {
            throw new RuntimeException('Invalid backup table selected for undo.');
        }

        $pdo->beginTransaction();
        try {
            $restored = $pdo->exec("
                UPDATE persons p
                INNER JOIN `{$selectedBackupTable}` b ON b.didar_contact_id = p.didar_contact_id
                SET p.sale_status = b.old_sale_status
            ");

            $pdo->commit();
            $undoResult = [
                'restored' => (int)$restored,
                'backup_table' => $selectedBackupTable,
            ];
            $message = 'Undo applied successfully.';
        } catch (Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    if ($apply && !$confirmed) {
        $error = 'Apply was not run. Type RESET in the confirmation field.';
        $apply = false;
    }

    if ($apply) {
        $backupTable = 'lead_sale_status_backfill_' . date('Ymd_His');
        $pdo->beginTransaction();
        try {
            $pdo->exec("
                CREATE TABLE `{$backupTable}` AS
                SELECT
                    p.id AS person_id,
                    p.didar_contact_id,
                    p.sale_status AS old_sale_status,
                    0 AS new_sale_status,
                    p.customer_code,
                    p.customer_products,
                    NOW() AS backed_up_at,
                    'no_won_deal_no_positive_payment' AS reason
                FROM persons p
                WHERE {$candidateWhere}
                {$limitSql}
            ");

            $updated = $pdo->exec("
                UPDATE persons p
                INNER JOIN `{$backupTable}` b ON b.didar_contact_id = p.didar_contact_id
                SET p.sale_status = 0
                WHERE p.sale_status = 1
            ");

            $pdo->commit();
            $applyResult = [
                'updated' => (int)$updated,
                'backup_table' => $backupTable,
                'restore_sql' => "UPDATE persons p INNER JOIN `{$backupTable}` b ON b.didar_contact_id = p.didar_contact_id SET p.sale_status = b.old_sale_status;",
            ];
            $message = 'Backfill applied successfully.';
            $backupTables = bf_getBackupTables($pdo);
        } catch (Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    $summary = $pdo->query($summarySql)->fetch(PDO::FETCH_ASSOC) ?: [];
    $sample = $pdo->query($sampleSql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
    $summary = [];
    $sample = [];
    $error = 'Backfill failed: ' . $e->getMessage();
}

$baseParams = [
    'key' => $providedKey,
    'scope' => $scope,
    'limit' => $limit,
];
?>
<!doctype html>
<html lang="fa" dir="rtl">
<head>
    <meta charset="utf-8">
    <title>Backfill lead sale_status</title>
    <style>
        body { font-family: Tahoma, Arial, sans-serif; margin: 24px; background: #f7f7f8; color: #222; }
        .wrap { max-width: 1180px; margin: 0 auto; }
        .panel { background: #fff; border: 1px solid #ddd; border-radius: 6px; padding: 18px; margin-bottom: 16px; }
        .grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(180px, 1fr)); gap: 12px; }
        .metric { background: #f2f5f8; border-radius: 6px; padding: 12px; }
        .metric strong { display: block; font-size: 24px; direction: ltr; text-align: left; }
        label { display: block; margin-bottom: 6px; font-weight: bold; }
        input, select, button { font: inherit; padding: 8px 10px; border: 1px solid #bbb; border-radius: 4px; }
        button { cursor: pointer; background: #174ea6; color: #fff; border-color: #174ea6; }
        button.danger { background: #b3261e; border-color: #b3261e; }
        table { width: 100%; border-collapse: collapse; direction: ltr; text-align: left; font-size: 13px; }
        th, td { border-bottom: 1px solid #e5e5e5; padding: 8px; vertical-align: top; }
        th { background: #f2f5f8; }
        .ok { color: #146c2e; }
        .err { color: #b3261e; }
        code { direction: ltr; display: inline-block; background: #f2f5f8; padding: 2px 4px; border-radius: 3px; }
        .truncate { max-width: 360px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
    </style>
</head>
<body>
<div class="wrap">
    <h1>Backfill lead sale_status بدون پرداخت</h1>

    <?php if ($message): ?><div class="panel ok"><?= bf_h($message) ?></div><?php endif; ?>
    <?php if ($error): ?><div class="panel err"><?= bf_h($error) ?></div><?php endif; ?>

    <div class="panel">
        <form method="get">
            <input type="hidden" name="key" value="<?= bf_h($providedKey) ?>">
            <div class="grid">
                <div>
                    <label for="scope">Scope</label>
                    <select name="scope" id="scope">
                        <option value="all" <?= $scope === 'all' ? 'selected' : '' ?>>All wrong successful leads</option>
                        <option value="requested-services" <?= $scope === 'requested-services' ? 'selected' : '' ?>>Requested-services bug only</option>
                    </select>
                </div>
                <div>
                    <label for="limit">Apply limit (0 = all)</label>
                    <input type="number" min="0" name="limit" id="limit" value="<?= bf_h($limit) ?>">
                </div>
                <div>
                    <label for="created_from">Lead created from</label>
                    <input type="date" name="created_from" id="created_from" value="<?= bf_h($createdFrom) ?>">
                </div>
                <div>
                    <label for="created_to">Lead created to</label>
                    <input type="date" name="created_to" id="created_to" value="<?= bf_h($createdTo) ?>">
                </div>
                <div style="align-self:end">
                    <button type="submit">Preview</button>
                </div>
            </div>
        </form>
    </div>

    <div class="panel">
        <h2>Preview summary</h2>
        <div class="grid">
            <?php foreach (['candidates', 'with_any_deal', 'with_requested_service_deal', 'with_customer_products'] as $key): ?>
                <div class="metric">
                    <?= bf_h($key) ?>
                    <strong><?= bf_h((int)($summary[$key] ?? 0)) ?></strong>
                </div>
            <?php endforeach; ?>
        </div>
    </div>

    <div class="panel">
        <h2>Apply</h2>
        <p>Apply resets matching <code>persons.sale_status</code> from <code>1</code> to <code>0</code>. It first creates a timestamped backup table.</p>
        <form method="get">
            <input type="hidden" name="key" value="<?= bf_h($providedKey) ?>">
            <input type="hidden" name="scope" value="<?= bf_h($scope) ?>">
            <input type="hidden" name="limit" value="<?= bf_h($limit) ?>">
            <input type="hidden" name="created_from" value="<?= bf_h($createdFrom) ?>">
            <input type="hidden" name="created_to" value="<?= bf_h($createdTo) ?>">
            <input type="hidden" name="apply" value="1">
            <label for="confirm">Type RESET to apply</label>
            <input type="text" name="confirm" id="confirm" autocomplete="off">
            <button class="danger" type="submit">Apply backfill</button>
        </form>
        <?php if ($applyResult): ?>
            <p>Updated: <code><?= bf_h($applyResult['updated']) ?></code></p>
            <p>Backup table: <code><?= bf_h($applyResult['backup_table']) ?></code></p>
            <p>Restore SQL: <code><?= bf_h($applyResult['restore_sql']) ?></code></p>
        <?php endif; ?>
    </div>

    <div class="panel">
        <h2>Undo</h2>
        <p>Undo restores <code>persons.sale_status</code> from a backup table created by this web/CLI backfill.</p>
        <form method="get">
            <input type="hidden" name="key" value="<?= bf_h($providedKey) ?>">
            <input type="hidden" name="undo" value="1">
            <div class="grid">
                <div>
                    <label for="backup_table">Backup table</label>
                    <select name="backup_table" id="backup_table">
                        <?php foreach ($backupTables as $table): ?>
                            <option value="<?= bf_h($table) ?>" <?= $selectedBackupTable === $table ? 'selected' : '' ?>><?= bf_h($table) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div>
                    <label for="undo_confirm">Type UNDO to restore</label>
                    <input type="text" name="confirm" id="undo_confirm" autocomplete="off">
                </div>
                <div style="align-self:end">
                    <button class="danger" type="submit">Undo backfill</button>
                </div>
            </div>
        </form>
        <?php if (!$backupTables): ?>
            <p>No backup tables found.</p>
        <?php endif; ?>
        <?php if ($undoResult): ?>
            <p>Restored rows: <code><?= bf_h($undoResult['restored']) ?></code></p>
            <p>Backup table: <code><?= bf_h($undoResult['backup_table']) ?></code></p>
        <?php endif; ?>
    </div>

    <div class="panel">
        <h2>Sample candidates</h2>
        <table>
            <thead>
            <tr>
                <th>didar_contact_id</th>
                <th>customer_code</th>
                <th>lead_name</th>
                <th>mobile_phone</th>
                <th>lead_created_at</th>
                <th>deal_count</th>
                <th>deal_statuses</th>
                <th>last_deal_at</th>
                <th>customer_products</th>
            </tr>
            </thead>
            <tbody>
            <?php foreach ($sample as $row): ?>
                <tr>
                    <td><?= bf_h($row['didar_contact_id'] ?? '') ?></td>
                    <td><?= bf_h($row['customer_code'] ?? '') ?></td>
                    <td><?= bf_h($row['lead_name'] ?? '') ?></td>
                    <td><?= bf_h($row['mobile_phone'] ?? '') ?></td>
                    <td><?= bf_h($row['lead_created_at'] ?? '') ?></td>
                    <td><?= bf_h($row['deal_count'] ?? '') ?></td>
                    <td><?= bf_h($row['deal_statuses'] ?? '') ?></td>
                    <td><?= bf_h($row['last_deal_at'] ?? '') ?></td>
                    <td class="truncate" title="<?= bf_h($row['customer_products'] ?? '') ?>"><?= bf_h($row['customer_products'] ?? '') ?></td>
                </tr>
            <?php endforeach; ?>
            <?php if (!$sample): ?>
                <tr><td colspan="9">No candidates found.</td></tr>
            <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>
</body>
</html>

