<?php
declare(strict_types=1);

/**
 * Registro local dos leads — é o que alimenta o painel /admin.
 *
 * Duas camadas, de propósito:
 *   1. leads.jsonl  — append-only, escrito ANTES de qualquer coisa poder falhar.
 *                     É a rede de segurança: mesmo que o SQLite ou o CRM caiam,
 *                     o lead nunca se perde.
 *   2. leads.sqlite — base consultável (filtros, KPIs, sincronização de FTD).
 *
 * Nada aqui lança exceção para fora: a captura do lead na landing tem
 * prioridade sobre a contabilidade local.
 */

require_once __DIR__ . '/config.php';

function store_data_dir(): string
{
    $dir = DATA_DIR;
    if (!is_dir($dir)) {
        @mkdir($dir, 0770, true);
    }
    // Cinto e suspensório: se o diretório acabar dentro do docroot, o .htaccess
    // impede que leads.sqlite/leads.jsonl sejam baixados pela web.
    $htaccess = $dir . '/.htaccess';
    if (is_dir($dir) && !is_file($htaccess)) {
        @file_put_contents($htaccess, "Require all denied\n<IfModule !mod_authz_core.c>\nDeny from all\n</IfModule>\n");
    }
    return $dir;
}

/** Grava a linha crua no log append-only. Nunca falha silenciosamente para o chamador. */
function store_append_jsonl(array $row): void
{
    $file = store_data_dir() . '/leads.jsonl';
    $line = json_encode($row, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    if ($line !== false) {
        @file_put_contents($file, $line . "\n", FILE_APPEND | LOCK_EX);
    }
}

/** Conexão SQLite com o schema já aplicado, ou null se o PDO/sqlite não existir. */
function store_db(): ?PDO
{
    static $pdo = null;
    static $tried = false;
    if ($tried) {
        return $pdo;
    }
    $tried = true;

    if (!class_exists(PDO::class) || !in_array('sqlite', PDO::getAvailableDrivers(), true)) {
        return null;
    }
    try {
        $pdo = new PDO('sqlite:' . store_data_dir() . '/leads.sqlite');
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->exec('PRAGMA journal_mode = WAL');
        $pdo->exec('PRAGMA busy_timeout = 5000');
        store_migrate($pdo);
    } catch (Throwable) {
        $pdo = null;
    }
    return $pdo;
}

function store_migrate(PDO $pdo): void
{
    $pdo->exec(<<<'SQL'
        CREATE TABLE IF NOT EXISTS leads (
            id             INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at     TEXT NOT NULL,          -- quando entrou aqui (UTC ISO-8601)
            crm_created_at TEXT,                   -- created_at devolvido pelo CRM
            origin         TEXT NOT NULL DEFAULT 'landing',  -- landing | crm
            name           TEXT,
            first_name     TEXT,
            last_name      TEXT,
            email          TEXT,
            phone          TEXT,
            country        TEXT,
            lang           TEXT,
            funnel_name    TEXT,
            source         TEXT,
            ip             TEXT,
            user_agent     TEXT,
            referer        TEXT,
            message        TEXT,
            send_status    TEXT NOT NULL DEFAULT 'pending', -- ok | failed | pending
            http_code      INTEGER,
            error          TEXT,
            uid            TEXT,                   -- uid do CRM (AFT2026...-5)
            crm_id         TEXT,
            status_title   TEXT,
            status_bind_id INTEGER,
            affiliate_name TEXT,
            ftd            INTEGER NOT NULL DEFAULT 0,
            ftd_time       TEXT,
            autologin_url  TEXT,
            synced_at      TEXT
        )
    SQL);
    $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS leads_uid_idx ON leads(uid) WHERE uid IS NOT NULL AND uid != ""');
    $pdo->exec('CREATE INDEX IF NOT EXISTS leads_email_idx ON leads(email)');
    $pdo->exec('CREATE INDEX IF NOT EXISTS leads_created_idx ON leads(created_at)');
    $pdo->exec('CREATE TABLE IF NOT EXISTS meta (k TEXT PRIMARY KEY, v TEXT)');
}

function store_meta_set(string $key, string $value): void
{
    $db = store_db();
    if ($db === null) {
        return;
    }
    try {
        $st = $db->prepare('INSERT INTO meta (k, v) VALUES (:k, :v) ON CONFLICT(k) DO UPDATE SET v = :v');
        $st->execute([':k' => $key, ':v' => $value]);
    } catch (Throwable) {
    }
}

function store_meta_get(string $key): ?string
{
    $db = store_db();
    if ($db === null) {
        return null;
    }
    try {
        $st = $db->prepare('SELECT v FROM meta WHERE k = :k');
        $st->execute([':k' => $key]);
        $v = $st->fetchColumn();
        return $v === false ? null : (string) $v;
    } catch (Throwable) {
        return null;
    }
}

/**
 * Grava um lead capturado na landing junto com o resultado do envio ao CRM.
 *
 * @param array<string,mixed> $lead   dados normalizados do formulário
 * @param array{ok:bool,http:int,error:?string,body:?array} $result resposta de partner_register_lead()
 * @return int|null id local, ou null se o SQLite não estiver disponível
 */
function store_record_lead(array $lead, array $result): ?int
{
    $data = is_array($result['body']['data'] ?? null) ? $result['body']['data'] : [];
    $status = is_array($result['body']['lead_status'] ?? null) ? $result['body']['lead_status'] : [];

    $row = [
        'created_at' => gmdate('c'),
        'crm_created_at' => isset($data['created_at']) ? (string) $data['created_at'] : null,
        'origin' => 'landing',
        'name' => (string) ($lead['name'] ?? ''),
        'first_name' => (string) ($lead['first_name'] ?? ''),
        'last_name' => (string) ($lead['last_name'] ?? ''),
        'email' => (string) ($lead['email'] ?? ''),
        'phone' => (string) ($lead['phone'] ?? ''),
        'country' => (string) ($lead['country'] ?? ''),
        'lang' => (string) ($lead['lang'] ?? ''),
        'funnel_name' => (string) ($lead['funnel_name'] ?? ''),
        'source' => (string) ($lead['source'] ?? ''),
        'ip' => (string) ($lead['ip'] ?? ''),
        'user_agent' => (string) ($lead['user_agent'] ?? ''),
        'referer' => (string) ($lead['referer'] ?? ''),
        'message' => (string) ($lead['message'] ?? ''),
        // 'duplicate' não é falha: o lead já está no CRM, só não foi criado de novo.
        'send_status' => $result['ok'] ? 'ok' : (partner_is_duplicate($result) ? 'duplicate' : 'failed'),
        'http_code' => (int) ($result['http'] ?? 0),
        'error' => $result['error'] !== null ? (string) $result['error'] : null,
        'uid' => isset($data['uid']) ? (string) $data['uid'] : null,
        'crm_id' => isset($data['id']) ? (string) $data['id'] : null,
        'status_title' => isset($status['title']) ? (string) $status['title'] : null,
        'status_bind_id' => isset($status['bind_id']) ? (int) $status['bind_id'] : null,
        'affiliate_name' => null,
        'ftd' => 0,
        'ftd_time' => null,
        'autologin_url' => isset($data['autologinUrl']) ? (string) $data['autologinUrl'] : null,
        'synced_at' => null,
    ];

    store_append_jsonl($row);

    $db = store_db();
    if ($db === null) {
        return null;
    }
    try {
        $cols = array_keys($row);
        $sql = 'INSERT INTO leads (' . implode(', ', $cols) . ') VALUES (:' . implode(', :', $cols) . ')';
        $st = $db->prepare($sql);
        foreach ($row as $k => $v) {
            $st->bindValue(':' . $k, $v);
        }
        $st->execute();
        return (int) $db->lastInsertId();
    } catch (Throwable) {
        return null;
    }
}

/**
 * Sincroniza status/FTD a partir de GET /lead/external-leads.
 *
 * Casa o registro local pelo `uid` e, na falta dele (lead que falhou na
 * primeira tentativa mas o CRM acabou gravando), pelo e-mail. Leads que só
 * existem no CRM entram como origin='crm' para o painel refletir a conta real.
 *
 * @param array<int,array<string,mixed>> $remoteLeads campo `data` da resposta
 * @return array{matched:int,updated:int,inserted:int,ftd:int}
 */
function store_sync_remote(array $remoteLeads): array
{
    $db = store_db();
    $stats = ['matched' => 0, 'updated' => 0, 'inserted' => 0, 'ftd' => 0];
    if ($db === null) {
        return $stats;
    }

    $now = gmdate('c');
    $findByUid = $db->prepare('SELECT id FROM leads WHERE uid = :uid LIMIT 1');
    $findByEmail = $db->prepare('SELECT id FROM leads WHERE email = :email AND (uid IS NULL OR uid = "") ORDER BY id DESC LIMIT 1');

    foreach ($remoteLeads as $r) {
        if (!is_array($r)) {
            continue;
        }
        $uid = isset($r['uid']) ? (string) $r['uid'] : '';
        $email = isset($r['email']) ? (string) $r['email'] : '';
        // `ftd_checker` marca o depósito; algumas contas só preenchem `ftd_time`.
        $ftd = ((int) ($r['ftd_checker'] ?? 0) === 1 || !empty($r['ftd_time'])) ? 1 : 0;
        $statusTitle = null;
        $statusBind = null;
        if (is_array($r['status'] ?? null)) {
            $statusTitle = isset($r['status']['title']) ? (string) $r['status']['title'] : null;
            $statusBind = isset($r['status']['bind_id']) ? (int) $r['status']['bind_id'] : null;
        }

        $id = null;
        if ($uid !== '') {
            $findByUid->execute([':uid' => $uid]);
            $found = $findByUid->fetchColumn();
            $id = $found === false ? null : (int) $found;
        }
        if ($id === null && $email !== '') {
            $findByEmail->execute([':email' => $email]);
            $found = $findByEmail->fetchColumn();
            $id = $found === false ? null : (int) $found;
        }

        try {
            if ($id !== null) {
                $st = $db->prepare(
                    'UPDATE leads SET uid = :uid, status_title = COALESCE(:st, status_title),
                        status_bind_id = COALESCE(:sb, status_bind_id),
                        affiliate_name = COALESCE(:aff, affiliate_name),
                        ftd = :ftd, ftd_time = :ftd_time,
                        crm_created_at = COALESCE(:cca, crm_created_at),
                        -- achamos o lead no CRM: o que constava como falha/duplicado está, de fato, lá.
                        send_status = CASE WHEN send_status IN (\'failed\', \'duplicate\', \'pending\') THEN \'ok\' ELSE send_status END,
                        synced_at = :now
                     WHERE id = :id'
                );
                $st->execute([
                    ':uid' => $uid !== '' ? $uid : null,
                    ':st' => $statusTitle,
                    ':sb' => $statusBind,
                    ':aff' => isset($r['affiliate_name']) ? (string) $r['affiliate_name'] : null,
                    ':ftd' => $ftd,
                    ':ftd_time' => isset($r['ftd_time']) && $r['ftd_time'] !== null ? (string) $r['ftd_time'] : null,
                    ':cca' => isset($r['created_at']) ? (string) $r['created_at'] : null,
                    ':now' => $now,
                    ':id' => $id,
                ]);
                $stats['matched']++;
                $stats['updated'] += $st->rowCount() > 0 ? 1 : 0;
            } else {
                $first = isset($r['first_name']) ? (string) $r['first_name'] : '';
                $last = isset($r['last_name']) ? (string) $r['last_name'] : '';
                $st = $db->prepare(
                    'INSERT INTO leads (created_at, crm_created_at, origin, name, first_name, last_name, email, phone,
                        country, funnel_name, source, send_status, uid, status_title, status_bind_id,
                        affiliate_name, ftd, ftd_time, synced_at)
                     VALUES (:created, :cca, \'crm\', :name, :first, :last, :email, :phone,
                        :country, :funnel, :source, \'ok\', :uid, :st, :sb, :aff, :ftd, :ftd_time, :now)'
                );
                $st->execute([
                    ':created' => isset($r['created_at']) ? (string) $r['created_at'] : $now,
                    ':cca' => isset($r['created_at']) ? (string) $r['created_at'] : null,
                    ':name' => trim($first . ' ' . $last),
                    ':first' => $first,
                    ':last' => $last,
                    ':email' => $email,
                    ':phone' => isset($r['phone']) ? (string) $r['phone'] : '',
                    ':country' => isset($r['country']) ? (string) $r['country'] : '',
                    ':funnel' => isset($r['funnel_name']) ? (string) $r['funnel_name'] : '',
                    ':source' => isset($r['source']) ? (string) $r['source'] : '',
                    ':uid' => $uid !== '' ? $uid : null,
                    ':st' => $statusTitle,
                    ':sb' => $statusBind,
                    ':aff' => isset($r['affiliate_name']) ? (string) $r['affiliate_name'] : null,
                    ':ftd' => $ftd,
                    ':ftd_time' => isset($r['ftd_time']) && $r['ftd_time'] !== null ? (string) $r['ftd_time'] : null,
                    ':now' => $now,
                ]);
                $stats['inserted']++;
            }
            $stats['ftd'] += $ftd;
        } catch (Throwable) {
            // Um registro problemático não pode abortar a sincronização inteira.
        }
    }

    store_meta_set('last_sync_at', $now);
    return $stats;
}

/**
 * Monta o WHERE dos filtros do painel.
 *
 * @param array<string,string> $f  from,to,q,status,ftd,origin
 * @return array{0:string,1:array<string,mixed>}
 */
function store_filter_sql(array $f): array
{
    $where = ['1=1'];
    $params = [];

    if (($f['from'] ?? '') !== '') {
        $where[] = 'date(COALESCE(crm_created_at, created_at)) >= date(:from)';
        $params[':from'] = $f['from'];
    }
    if (($f['to'] ?? '') !== '') {
        $where[] = 'date(COALESCE(crm_created_at, created_at)) <= date(:to)';
        $params[':to'] = $f['to'];
    }
    if (($f['q'] ?? '') !== '') {
        $where[] = '(name LIKE :q OR email LIKE :q OR phone LIKE :q OR uid LIKE :q)';
        $params[':q'] = '%' . $f['q'] . '%';
    }
    if (($f['status'] ?? '') !== '') {
        $where[] = 'send_status = :status';
        $params[':status'] = $f['status'];
    }
    if (($f['ftd'] ?? '') !== '') {
        $where[] = 'ftd = :ftd';
        $params[':ftd'] = (int) $f['ftd'];
    }
    if (($f['origin'] ?? '') !== '') {
        $where[] = 'origin = :origin';
        $params[':origin'] = $f['origin'];
    }

    return [implode(' AND ', $where), $params];
}

/**
 * @param array<string,string> $filters
 * @return array<int,array<string,mixed>>
 */
function store_list_leads(array $filters, int $limit, int $offset): array
{
    $db = store_db();
    if ($db === null) {
        return [];
    }
    [$where, $params] = store_filter_sql($filters);
    $st = $db->prepare(
        "SELECT * FROM leads WHERE $where
         ORDER BY COALESCE(crm_created_at, created_at) DESC, id DESC
         LIMIT :limit OFFSET :offset"
    );
    foreach ($params as $k => $v) {
        $st->bindValue($k, $v);
    }
    $st->bindValue(':limit', $limit, PDO::PARAM_INT);
    $st->bindValue(':offset', $offset, PDO::PARAM_INT);
    $st->execute();
    return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

/**
 * KPIs do topo do painel.
 *
 * @param array<string,string> $filters
 * @return array{total:int,sent:int,failed:int,ftd:int,countries:int,conv:float}
 */
function store_stats(array $filters): array
{
    $empty = ['total' => 0, 'sent' => 0, 'failed' => 0, 'dup' => 0, 'ftd' => 0, 'countries' => 0, 'conv' => 0.0];
    $db = store_db();
    if ($db === null) {
        return $empty;
    }
    [$where, $params] = store_filter_sql($filters);
    $st = $db->prepare(
        "SELECT COUNT(*) total,
                SUM(CASE WHEN send_status = 'ok' THEN 1 ELSE 0 END) sent,
                SUM(CASE WHEN send_status = 'failed' THEN 1 ELSE 0 END) failed,
                SUM(CASE WHEN send_status = 'duplicate' THEN 1 ELSE 0 END) dup,
                SUM(ftd) ftd,
                COUNT(DISTINCT NULLIF(country, '')) countries
         FROM leads WHERE $where"
    );
    $st->execute($params);
    $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];

    $total = (int) ($row['total'] ?? 0);
    $sent = (int) ($row['sent'] ?? 0);
    $ftd = (int) ($row['ftd'] ?? 0);

    return [
        'total' => $total,
        'sent' => $sent,
        'failed' => (int) ($row['failed'] ?? 0),
        'dup' => (int) ($row['dup'] ?? 0),
        'ftd' => $ftd,
        'countries' => (int) ($row['countries'] ?? 0),
        // Conversão = FTDs sobre leads efetivamente criados no CRM. Duplicados
        // ficam de fora: o depósito deles é contabilizado no lead original.
        'conv' => $sent > 0 ? round($ftd * 100 / $sent, 1) : 0.0,
    ];
}

/**
 * @param array<string,string> $filters
 * @return array<int,array{d:string,leads:int,ftd:int}>
 */
function store_daily(array $filters, int $days = 30): array
{
    $db = store_db();
    if ($db === null) {
        return [];
    }
    [$where, $params] = store_filter_sql($filters);
    $st = $db->prepare(
        "SELECT date(COALESCE(crm_created_at, created_at)) d,
                COUNT(*) leads, SUM(ftd) ftd
         FROM leads WHERE $where
         GROUP BY d ORDER BY d DESC LIMIT :days"
    );
    foreach ($params as $k => $v) {
        $st->bindValue($k, $v);
    }
    $st->bindValue(':days', $days, PDO::PARAM_INT);
    $st->execute();
    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    return array_reverse(array_map(
        static fn(array $r): array => ['d' => (string) $r['d'], 'leads' => (int) $r['leads'], 'ftd' => (int) $r['ftd']],
        $rows
    ));
}

/**
 * @param array<string,string> $filters
 * @return array<int,array{country:string,leads:int,ftd:int}>
 */
function store_by_country(array $filters, int $limit = 10): array
{
    $db = store_db();
    if ($db === null) {
        return [];
    }
    [$where, $params] = store_filter_sql($filters);
    $st = $db->prepare(
        "SELECT COALESCE(NULLIF(country, ''), '—') country, COUNT(*) leads, SUM(ftd) ftd
         FROM leads WHERE $where GROUP BY country ORDER BY leads DESC LIMIT :limit"
    );
    foreach ($params as $k => $v) {
        $st->bindValue($k, $v);
    }
    $st->bindValue(':limit', $limit, PDO::PARAM_INT);
    $st->execute();
    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    return array_map(
        static fn(array $r): array => ['country' => (string) $r['country'], 'leads' => (int) $r['leads'], 'ftd' => (int) $r['ftd']],
        $rows
    );
}

/**
 * Leads recusados por país não habilitado na conta de afiliado ("Bad country").
 * É a falha mais provável nesta integração, então o painel a destaca separada
 * das falhas genéricas de rede.
 *
 * @param array<string,string> $filters
 * @return array{count:int,countries:array<int,string>}
 */
function store_bad_country(array $filters): array
{
    $db = store_db();
    if ($db === null) {
        return ['count' => 0, 'countries' => []];
    }
    [$where, $params] = store_filter_sql($filters);
    $st = $db->prepare(
        "SELECT COALESCE(NULLIF(country, ''), '—') c, COUNT(*) n
         FROM leads
         WHERE $where AND send_status = 'failed' AND error LIKE '%country%'
         GROUP BY c ORDER BY n DESC"
    );
    $st->execute($params);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

    $count = 0;
    $countries = [];
    foreach ($rows as $r) {
        $count += (int) $r['n'];
        $countries[] = (string) $r['c'] . ' (' . (int) $r['n'] . ')';
    }
    return ['count' => $count, 'countries' => $countries];
}

/** @return array<string,mixed>|null */
function store_get_lead(int $id): ?array
{
    $db = store_db();
    if ($db === null) {
        return null;
    }
    $st = $db->prepare('SELECT * FROM leads WHERE id = :id');
    $st->execute([':id' => $id]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    return is_array($row) ? $row : null;
}

/**
 * Regrava o resultado de um reenvio manual feito pelo painel.
 *
 * @param array{ok:bool,http:int,error:?string,body:?array} $result
 */
function store_update_send_result(int $id, array $result): void
{
    $db = store_db();
    if ($db === null) {
        return;
    }
    $data = is_array($result['body']['data'] ?? null) ? $result['body']['data'] : [];
    $status = is_array($result['body']['lead_status'] ?? null) ? $result['body']['lead_status'] : [];

    try {
        $st = $db->prepare(
            'UPDATE leads SET send_status = :ss, http_code = :http, error = :err,
                uid = COALESCE(NULLIF(:uid, \'\'), uid),
                crm_id = COALESCE(NULLIF(:crm_id, \'\'), crm_id),
                status_title = COALESCE(:st, status_title),
                status_bind_id = COALESCE(:sb, status_bind_id),
                autologin_url = COALESCE(NULLIF(:auto, \'\'), autologin_url),
                synced_at = :now
             WHERE id = :id'
        );
        $st->execute([
            ':ss' => $result['ok'] ? 'ok' : (partner_is_duplicate($result) ? 'duplicate' : 'failed'),
            ':http' => (int) ($result['http'] ?? 0),
            ':err' => $result['error'] !== null ? (string) $result['error'] : null,
            ':uid' => isset($data['uid']) ? (string) $data['uid'] : '',
            ':crm_id' => isset($data['id']) ? (string) $data['id'] : '',
            ':st' => isset($status['title']) ? (string) $status['title'] : null,
            ':sb' => isset($status['bind_id']) ? (int) $status['bind_id'] : null,
            ':auto' => isset($data['autologinUrl']) ? (string) $data['autologinUrl'] : '',
            ':now' => gmdate('c'),
            ':id' => $id,
        ]);
    } catch (Throwable) {
    }
}

/** @param array<string,string> $filters */
function store_count(array $filters): int
{
    $db = store_db();
    if ($db === null) {
        return 0;
    }
    [$where, $params] = store_filter_sql($filters);
    $st = $db->prepare("SELECT COUNT(*) FROM leads WHERE $where");
    $st->execute($params);
    return (int) $st->fetchColumn();
}
