<?php
/**
 * Kantan Analytics — SQLite long-term storage
 *
 * Holds indefinite-retention data that would otherwise be lost when MySQL raw
 * detail is purged at 90 days:
 *   - visitors: one row per persistent visitor_id (first/last seen, counters, first source)
 *   - daily_aggregates: one row per date (sessions, pageviews, bounces, sources, devices)
 *   - conversions_archive: mirror of wp_kantan_conversions, deduped by mysql_id
 *   - meta: key/value for schema version + last_aggregated_date
 *
 * The tracking hot-path (api.php) does not write here. All SQLite writes
 * happen in the nightly aggregation cron (kantan_analytics_daily_aggregate).
 */

if (!defined('ABSPATH')) {
    exit;
}

define('KANTAN_SQLITE_SCHEMA_VERSION', 1);

/**
 * Is the SQLite3 PHP extension available on this host?
 */
function kantan_sqlite_available() {
    return class_exists('SQLite3');
}

/**
 * Resolve the filesystem path to the SQLite database, creating the directory
 * and its access-denial guards on first call.
 *
 * Layout:
 *   wp-content/uploads/kantan/
 *     .htaccess      -> deny from all
 *     index.php      -> silence is golden
 *     analytics-{random}.sqlite
 *
 * The random filename suffix is stored in the `kantan_sqlite_filename` option
 * so the filename is stable across requests but not guessable via URL.
 */
function kantan_sqlite_path() {
    $uploads = wp_upload_dir();
    if (!empty($uploads['error'])) {
        return null;
    }

    $dir = trailingslashit($uploads['basedir']) . 'kantan';

    if (!file_exists($dir)) {
        if (!wp_mkdir_p($dir)) {
            return null;
        }
    }

    // Block direct HTTP access to anything in this directory.
    $htaccess = $dir . '/.htaccess';
    if (!file_exists($htaccess)) {
        @file_put_contents($htaccess, "Order Deny,Allow\nDeny from all\n");
    }
    $index = $dir . '/index.php';
    if (!file_exists($index)) {
        @file_put_contents($index, "<?php // Silence is golden.\n");
    }

    $filename = get_option('kantan_sqlite_filename');
    if (!$filename) {
        $filename = 'analytics-' . wp_generate_password(16, false, false) . '.sqlite';
        update_option('kantan_sqlite_filename', $filename, false);
    }

    return $dir . '/' . $filename;
}

/**
 * Get a connected SQLite3 instance (or null if unavailable).
 * Lazily runs schema migrations on first connect per request.
 */
function kantan_sqlite_db() {
    static $db = null;
    if ($db instanceof SQLite3) {
        return $db;
    }

    if (!kantan_sqlite_available()) {
        return null;
    }

    $path = kantan_sqlite_path();
    if (!$path) {
        return null;
    }

    try {
        $db = new SQLite3($path);
    } catch (Exception $e) {
        error_log('[Kantan Analytics] SQLite open failed: ' . $e->getMessage());
        $db = null;
        return null;
    }

    $db->busyTimeout(5000);
    $db->exec('PRAGMA journal_mode = WAL');
    $db->exec('PRAGMA synchronous = NORMAL');
    $db->exec('PRAGMA foreign_keys = ON');

    kantan_sqlite_migrate($db);

    return $db;
}

/**
 * Run pending schema migrations. Current schema version is compared against
 * the `schema_version` row in the `meta` table; each version block is
 * idempotent so re-running is safe.
 */
function kantan_sqlite_migrate(SQLite3 $db) {
    $db->exec("CREATE TABLE IF NOT EXISTS meta (
        key TEXT PRIMARY KEY,
        value TEXT
    )");

    $current = 0;
    $result = $db->query("SELECT value FROM meta WHERE key = 'schema_version'");
    if ($result) {
        $row = $result->fetchArray(SQLITE3_ASSOC);
        if ($row) {
            $current = intval($row['value']);
        }
    }

    if ($current < 1) {
        $db->exec("CREATE TABLE IF NOT EXISTS visitors (
            visitor_id TEXT PRIMARY KEY,
            first_seen TEXT NOT NULL,
            last_seen TEXT NOT NULL,
            session_count INTEGER DEFAULT 0,
            pageview_count INTEGER DEFAULT 0,
            conversion_count INTEGER DEFAULT 0,
            first_source TEXT,
            first_referrer TEXT,
            first_landing_url TEXT,
            last_source TEXT,
            device_category TEXT,
            updated_at TEXT NOT NULL
        )");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_visitors_last_seen ON visitors(last_seen)");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_visitors_first_source ON visitors(first_source)");

        $db->exec("CREATE TABLE IF NOT EXISTS daily_aggregates (
            date TEXT PRIMARY KEY,
            sessions INTEGER DEFAULT 0,
            unique_visitors INTEGER DEFAULT 0,
            new_visitors INTEGER DEFAULT 0,
            returning_visitors INTEGER DEFAULT 0,
            pageviews INTEGER DEFAULT 0,
            bounces INTEGER DEFAULT 0,
            total_duration INTEGER DEFAULT 0,
            total_scroll_depth INTEGER DEFAULT 0,
            conversions INTEGER DEFAULT 0,
            source_direct INTEGER DEFAULT 0,
            source_organic INTEGER DEFAULT 0,
            source_social INTEGER DEFAULT 0,
            source_referral INTEGER DEFAULT 0,
            source_ai INTEGER DEFAULT 0,
            device_mobile INTEGER DEFAULT 0,
            device_tablet INTEGER DEFAULT 0,
            device_laptop INTEGER DEFAULT 0,
            device_desktop INTEGER DEFAULT 0,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )");

        $db->exec("CREATE TABLE IF NOT EXISTS conversions_archive (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            mysql_id INTEGER UNIQUE,
            session_id TEXT,
            visitor_id TEXT,
            conversion_type TEXT,
            conversion_label TEXT,
            conversion_value TEXT,
            page_url TEXT,
            converted_at TEXT NOT NULL,
            archived_at TEXT NOT NULL
        )");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_conv_archive_visitor ON conversions_archive(visitor_id)");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_conv_archive_type ON conversions_archive(conversion_type)");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_conv_archive_date ON conversions_archive(converted_at)");

        $db->exec("INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', '1')");
    }

    if ($current < 2) {
        // Add the AI-assistant traffic source column to existing archives.
        $has_source_ai = false;
        $cols = $db->query("PRAGMA table_info(daily_aggregates)");
        if ($cols) {
            while ($c = $cols->fetchArray(SQLITE3_ASSOC)) {
                if ($c['name'] === 'source_ai') { $has_source_ai = true; break; }
            }
        }
        if (!$has_source_ai) {
            $db->exec("ALTER TABLE daily_aggregates ADD COLUMN source_ai INTEGER DEFAULT 0");
        }
        $db->exec("INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', '2')");
    }
}

/**
 * Read a meta key/value. Returns null if not set.
 */
function kantan_sqlite_get_meta($key) {
    $db = kantan_sqlite_db();
    if (!$db) return null;

    $stmt = $db->prepare("SELECT value FROM meta WHERE key = :key");
    $stmt->bindValue(':key', $key, SQLITE3_TEXT);
    $result = $stmt->execute();
    $row = $result ? $result->fetchArray(SQLITE3_ASSOC) : null;
    return $row ? $row['value'] : null;
}

/**
 * Write a meta key/value.
 */
function kantan_sqlite_set_meta($key, $value) {
    $db = kantan_sqlite_db();
    if (!$db) return false;

    $stmt = $db->prepare("INSERT OR REPLACE INTO meta (key, value) VALUES (:key, :value)");
    $stmt->bindValue(':key', $key, SQLITE3_TEXT);
    $stmt->bindValue(':value', (string) $value, SQLITE3_TEXT);
    return (bool) $stmt->execute();
}

/**
 * Upsert a visitor row. $data keys map to column names; missing keys are
 * preserved on updates, and counters are added rather than overwritten.
 *
 * $data = array(
 *   'visitor_id'       => 'kv_xxx',          required
 *   'first_seen'       => 'YYYY-MM-DD HH:MM:SS',
 *   'last_seen'        => 'YYYY-MM-DD HH:MM:SS',
 *   'session_count'    => int  (increment)
 *   'pageview_count'   => int  (increment)
 *   'conversion_count' => int  (increment)
 *   'first_source'     => 'organic' etc. (only set on insert)
 *   'first_referrer'   => (only set on insert)
 *   'first_landing_url'=> (only set on insert)
 *   'last_source'      => (overwritten each call)
 *   'device_category'  => (overwritten each call)
 * );
 */
function kantan_sqlite_upsert_visitor(array $data) {
    $db = kantan_sqlite_db();
    if (!$db || empty($data['visitor_id'])) return false;

    $now = current_time('mysql');
    $visitor_id = $data['visitor_id'];

    $existing_stmt = $db->prepare("SELECT visitor_id FROM visitors WHERE visitor_id = :vid");
    $existing_stmt->bindValue(':vid', $visitor_id, SQLITE3_TEXT);
    $existing = $existing_stmt->execute()->fetchArray(SQLITE3_ASSOC);

    if ($existing) {
        $sql = "UPDATE visitors SET
            last_seen = :last_seen,
            session_count = session_count + :session_inc,
            pageview_count = pageview_count + :pv_inc,
            conversion_count = conversion_count + :conv_inc,
            last_source = COALESCE(:last_source, last_source),
            device_category = COALESCE(:device_category, device_category),
            updated_at = :updated_at
            WHERE visitor_id = :visitor_id";
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':last_seen', $data['last_seen'] ?? $now, SQLITE3_TEXT);
        $stmt->bindValue(':session_inc', intval($data['session_count'] ?? 0), SQLITE3_INTEGER);
        $stmt->bindValue(':pv_inc', intval($data['pageview_count'] ?? 0), SQLITE3_INTEGER);
        $stmt->bindValue(':conv_inc', intval($data['conversion_count'] ?? 0), SQLITE3_INTEGER);
        $stmt->bindValue(':last_source', $data['last_source'] ?? null, SQLITE3_TEXT);
        $stmt->bindValue(':device_category', $data['device_category'] ?? null, SQLITE3_TEXT);
        $stmt->bindValue(':updated_at', $now, SQLITE3_TEXT);
        $stmt->bindValue(':visitor_id', $visitor_id, SQLITE3_TEXT);
        return (bool) $stmt->execute();
    }

    $sql = "INSERT INTO visitors (
        visitor_id, first_seen, last_seen,
        session_count, pageview_count, conversion_count,
        first_source, first_referrer, first_landing_url, last_source,
        device_category, updated_at
    ) VALUES (
        :visitor_id, :first_seen, :last_seen,
        :session_count, :pageview_count, :conversion_count,
        :first_source, :first_referrer, :first_landing_url, :last_source,
        :device_category, :updated_at
    )";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(':visitor_id', $visitor_id, SQLITE3_TEXT);
    $stmt->bindValue(':first_seen', $data['first_seen'] ?? $now, SQLITE3_TEXT);
    $stmt->bindValue(':last_seen', $data['last_seen'] ?? $now, SQLITE3_TEXT);
    $stmt->bindValue(':session_count', intval($data['session_count'] ?? 1), SQLITE3_INTEGER);
    $stmt->bindValue(':pageview_count', intval($data['pageview_count'] ?? 0), SQLITE3_INTEGER);
    $stmt->bindValue(':conversion_count', intval($data['conversion_count'] ?? 0), SQLITE3_INTEGER);
    $stmt->bindValue(':first_source', $data['first_source'] ?? null, SQLITE3_TEXT);
    $stmt->bindValue(':first_referrer', $data['first_referrer'] ?? null, SQLITE3_TEXT);
    $stmt->bindValue(':first_landing_url', $data['first_landing_url'] ?? null, SQLITE3_TEXT);
    $stmt->bindValue(':last_source', $data['last_source'] ?? null, SQLITE3_TEXT);
    $stmt->bindValue(':device_category', $data['device_category'] ?? null, SQLITE3_TEXT);
    $stmt->bindValue(':updated_at', $now, SQLITE3_TEXT);
    return (bool) $stmt->execute();
}

/**
 * Insert or replace a daily_aggregates row for a given date.
 *
 * $data keys map directly to column names. Missing keys default to 0.
 */
function kantan_sqlite_upsert_daily_aggregate($date, array $data) {
    $db = kantan_sqlite_db();
    if (!$db || empty($date)) return false;

    $now = current_time('mysql');
    $cols = array(
        'sessions', 'unique_visitors', 'new_visitors', 'returning_visitors',
        'pageviews', 'bounces', 'total_duration', 'total_scroll_depth', 'conversions',
        'source_direct', 'source_organic', 'source_social', 'source_referral', 'source_ai',
        'device_mobile', 'device_tablet', 'device_laptop', 'device_desktop',
    );

    $sql = "INSERT INTO daily_aggregates (date, " . implode(', ', $cols) . ", created_at, updated_at)
            VALUES (:date, " . implode(', ', array_map(fn($c) => ':' . $c, $cols)) . ", :created_at, :updated_at)
            ON CONFLICT(date) DO UPDATE SET " .
            implode(', ', array_map(fn($c) => "$c = excluded.$c", $cols)) .
            ", updated_at = excluded.updated_at";

    $stmt = $db->prepare($sql);
    $stmt->bindValue(':date', $date, SQLITE3_TEXT);
    foreach ($cols as $col) {
        $stmt->bindValue(':' . $col, intval($data[$col] ?? 0), SQLITE3_INTEGER);
    }
    $stmt->bindValue(':created_at', $now, SQLITE3_TEXT);
    $stmt->bindValue(':updated_at', $now, SQLITE3_TEXT);
    return (bool) $stmt->execute();
}

/**
 * Archive a single MySQL conversion row. Idempotent — duplicate mysql_id
 * values are ignored via the UNIQUE constraint.
 */
function kantan_sqlite_archive_conversion(array $row) {
    $db = kantan_sqlite_db();
    if (!$db) return false;

    $stmt = $db->prepare("INSERT OR IGNORE INTO conversions_archive (
        mysql_id, session_id, visitor_id, conversion_type, conversion_label,
        conversion_value, page_url, converted_at, archived_at
    ) VALUES (
        :mysql_id, :session_id, :visitor_id, :conversion_type, :conversion_label,
        :conversion_value, :page_url, :converted_at, :archived_at
    )");
    $stmt->bindValue(':mysql_id', intval($row['id'] ?? 0), SQLITE3_INTEGER);
    $stmt->bindValue(':session_id', $row['session_id'] ?? '', SQLITE3_TEXT);
    $stmt->bindValue(':visitor_id', $row['visitor_id'] ?? '', SQLITE3_TEXT);
    $stmt->bindValue(':conversion_type', $row['conversion_type'] ?? '', SQLITE3_TEXT);
    $stmt->bindValue(':conversion_label', $row['conversion_label'] ?? '', SQLITE3_TEXT);
    $stmt->bindValue(':conversion_value', $row['conversion_value'] ?? '', SQLITE3_TEXT);
    $stmt->bindValue(':page_url', $row['page_url'] ?? '', SQLITE3_TEXT);
    $stmt->bindValue(':converted_at', $row['converted_at'] ?? current_time('mysql'), SQLITE3_TEXT);
    $stmt->bindValue(':archived_at', current_time('mysql'), SQLITE3_TEXT);
    return (bool) $stmt->execute();
}

/**
 * Fetch daily_aggregates rows between two dates (inclusive, YYYY-MM-DD).
 * Returns an array of associative rows ordered by date ascending.
 */
function kantan_sqlite_get_aggregates($start_date, $end_date) {
    $db = kantan_sqlite_db();
    if (!$db) return array();

    $stmt = $db->prepare("SELECT * FROM daily_aggregates
        WHERE date >= :start AND date <= :end
        ORDER BY date ASC");
    $stmt->bindValue(':start', $start_date, SQLITE3_TEXT);
    $stmt->bindValue(':end', $end_date, SQLITE3_TEXT);

    $result = $stmt->execute();
    $rows = array();
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $rows[] = $row;
    }
    return $rows;
}

/**
 * Fetch a single visitor profile by UUID.
 */
function kantan_sqlite_get_visitor($visitor_id) {
    $db = kantan_sqlite_db();
    if (!$db) return null;

    $stmt = $db->prepare("SELECT * FROM visitors WHERE visitor_id = :vid");
    $stmt->bindValue(':vid', $visitor_id, SQLITE3_TEXT);
    $result = $stmt->execute();
    return $result ? $result->fetchArray(SQLITE3_ASSOC) : null;
}

/**
 * Health check for the dashboard settings tab. Returns an associative array
 * describing the state of the SQLite layer.
 */
function kantan_sqlite_health() {
    $out = array(
        'available' => kantan_sqlite_available(),
        'path'      => null,
        'writable'  => false,
        'size'      => 0,
        'schema'    => null,
        'last_aggregated_date' => null,
        'visitor_count' => 0,
    );
    if (!$out['available']) return $out;

    $path = kantan_sqlite_path();
    $out['path'] = $path;
    if (!$path) return $out;

    $out['writable'] = wp_is_writable(dirname($path));
    $out['size'] = file_exists($path) ? filesize($path) : 0;

    $db = kantan_sqlite_db();
    if ($db) {
        $out['schema'] = kantan_sqlite_get_meta('schema_version');
        $out['last_aggregated_date'] = kantan_sqlite_get_meta('last_aggregated_date');
        $row = $db->querySingle("SELECT COUNT(*) AS c FROM visitors", true);
        $out['visitor_count'] = intval($row['c'] ?? 0);
    }
    return $out;
}
