<?php
namespace Zadex\Forms;

/**
 * Repository for stored form submissions: table `{$wpdb->prefix}zadex_submissions`.
 *
 *   id, form_id (element id), form_name, post_id, data (JSON: field id => {label, type, value}),
 *   meta (JSON: ip, user_agent, referrer, user_id, page), status (new | read), created_at (UTC)
 *
 * The table is created with dbDelta on activation and whenever the `zadex_db_version` option is older than
 * DB_VERSION (checked on plugins_loaded, so updates without re-activation migrate too).
 * Everything read from here is visitor input: callers must escape on output.
 */
final class Submissions
{
    public const TABLE = 'zadex_submissions';
    public const DB_VERSION = '1';
    public const OPTION_DB = 'zadex_db_version';
    public const STATUSES = ['new', 'read'];
    public const MAX_PER_PAGE = 200;

    /** @var \wpdb|object */
    private $db;

    /** @param \wpdb|object|null $wpdb */
    public function __construct($wpdb = null)
    {
        $this->db = $wpdb ?? ($GLOBALS['wpdb'] ?? null);
    }

    public function table(): string
    {
        return (string) ($this->db->prefix ?? 'wp_') . self::TABLE;
    }

    public function schema(): string
    {
        $collate = method_exists($this->db, 'get_charset_collate') ? (string) $this->db->get_charset_collate() : '';
        return "CREATE TABLE {$this->table()} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  form_id varchar(64) NOT NULL DEFAULT '',
  form_name varchar(191) NOT NULL DEFAULT '',
  post_id bigint(20) unsigned NOT NULL DEFAULT 0,
  data longtext NOT NULL,
  meta longtext NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'new',
  created_at datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY  (id),
  KEY form_id (form_id),
  KEY status (status),
  KEY post_id (post_id)
) {$collate};";
    }

    /** Creates / migrates the table (dbDelta) and records the schema version. */
    public function install(): void
    {
        if (!function_exists('dbDelta') && defined('ABSPATH') && is_file(ABSPATH . 'wp-admin/includes/upgrade.php')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        if (function_exists('dbDelta')) {
            dbDelta($this->schema());
        }
        update_option(self::OPTION_DB, self::DB_VERSION);
    }

    /** plugins_loaded: install when the stored schema version is missing or older. */
    public function maybe_upgrade(): bool
    {
        if ((string) get_option(self::OPTION_DB, '') === self::DB_VERSION) {
            return false;
        }
        $this->install();
        return true;
    }

    public function insert(Submission $s): int
    {
        return $this->insert_row([
            'form_id'   => $s->form_id,
            'form_name' => $s->form_name,
            'post_id'   => $s->post_id,
            'data'      => $s->data(),
            'meta'      => $s->meta,
        ]);
    }

    /** @param array<string, mixed> $row */
    public function insert_row(array $row): int
    {
        $ok = $this->db->insert($this->table(), [
            'form_id'    => substr(Fields::sanitize_id($row['form_id'] ?? ''), 0, 64),
            'form_name'  => mb_substr(sanitize_text_field((string) ($row['form_name'] ?? '')), 0, 191),
            'post_id'    => max(0, (int) ($row['post_id'] ?? 0)),
            'data'       => (string) wp_json_encode($row['data'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            'meta'       => (string) wp_json_encode($row['meta'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            'status'     => 'new',
            'created_at' => (string) ($row['created_at'] ?? current_time('mysql', true)),
        ], ['%s', '%s', '%d', '%s', '%s', '%s', '%s']);
        return $ok ? (int) $this->db->insert_id : 0;
    }

    /**
     * @param array{form_id?: string, status?: string, post_id?: int, search?: string, per_page?: int, page?: int, order?: string} $args
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function query(array $args = []): array
    {
        [$where, $params] = $this->where($args);
        $per_page = max(1, min(self::MAX_PER_PAGE, (int) ($args['per_page'] ?? 20)));
        $page = max(1, (int) ($args['page'] ?? 1));
        $order = strtoupper((string) ($args['order'] ?? 'DESC')) === 'ASC' ? 'ASC' : 'DESC';
        $table = $this->table();
        $count_sql = "SELECT COUNT(*) FROM {$table}{$where}";
        $total = (int) $this->db->get_var($params ? $this->db->prepare($count_sql, ...$params) : $count_sql);
        $sql = "SELECT * FROM {$table}{$where} ORDER BY created_at {$order}, id {$order} LIMIT %d OFFSET %d";
        $rows = $this->db->get_results($this->db->prepare($sql, ...array_merge($params, [$per_page, ($page - 1) * $per_page])), ARRAY_A);
        return ['items' => array_map([$this, 'decode'], is_array($rows) ? $rows : []), 'total' => $total];
    }

    /** @return array{0: string, 1: array<int, mixed>} */
    private function where(array $args): array
    {
        $clauses = [];
        $params = [];
        if (!empty($args['form_id']) && is_string($args['form_id'])) {
            $clauses[] = 'form_id = %s';
            $params[] = Fields::sanitize_id($args['form_id']);
        }
        if (!empty($args['status']) && in_array($args['status'], self::STATUSES, true)) {
            $clauses[] = 'status = %s';
            $params[] = $args['status'];
        }
        if (!empty($args['post_id'])) {
            $clauses[] = 'post_id = %d';
            $params[] = (int) $args['post_id'];
        }
        if (!empty($args['search']) && is_string($args['search'])) {
            $clauses[] = 'data LIKE %s';
            $params[] = '%' . $this->db->esc_like(trim($args['search'])) . '%';
        }
        if (!empty($args['ids']) && is_array($args['ids'])) {
            $ids = array_values(array_filter(array_map('intval', $args['ids'])));
            if ($ids) {
                $clauses[] = 'id IN (' . implode(',', array_fill(0, count($ids), '%d')) . ')';
                $params = array_merge($params, $ids);
            }
        }
        return [$clauses ? ' WHERE ' . implode(' AND ', $clauses) : '', $params];
    }

    public function get(int $id): ?array
    {
        if ($id <= 0) {
            return null;
        }
        $row = $this->db->get_row($this->db->prepare("SELECT * FROM {$this->table()} WHERE id = %d", $id), ARRAY_A);
        return is_array($row) ? $this->decode($row) : null;
    }

    /** @param array<int, int> $ids */
    public function mark(array $ids, string $status): int
    {
        if (!in_array($status, self::STATUSES, true)) {
            return 0;
        }
        $n = 0;
        foreach (array_unique(array_filter(array_map('intval', $ids))) as $id) {
            $n += (int) $this->db->update($this->table(), ['status' => $status], ['id' => $id], ['%s'], ['%d']);
        }
        return $n;
    }

    /**
     * Records the delivery status of an integration in the row's meta (`meta.integrations.<name>`), e.g. the
     * webhook retry queue: {status: delivered|retrying|failed, attempts, error?, time}.
     *
     * @param array<string, mixed> $status
     */
    public function set_integration_status(int $id, string $name, array $status): bool
    {
        $row = $this->get($id);
        if ($row === null) {
            return false;
        }
        $meta = $row['meta'];
        $meta['integrations'] = is_array($meta['integrations'] ?? null) ? $meta['integrations'] : [];
        $meta['integrations'][sanitize_key($name)] = $status;
        $json = (string) wp_json_encode($meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        return (bool) $this->db->update($this->table(), ['meta' => $json], ['id' => $id], ['%s'], ['%d']);
    }

    /** @param array<int, int> $ids */
    public function mark_read(array $ids): int
    {
        return $this->mark($ids, 'read');
    }

    /** @param array<int, int> $ids */
    public function delete(array $ids): int
    {
        $n = 0;
        foreach (array_unique(array_filter(array_map('intval', $ids))) as $id) {
            $n += (int) $this->db->delete($this->table(), ['id' => $id], ['%d']);
        }
        return $n;
    }

    public function count_unread(): int
    {
        return (int) $this->db->get_var($this->db->prepare("SELECT COUNT(*) FROM {$this->table()} WHERE status = %s", 'new'));
    }

    /** @return array<string, string> form id => latest form name */
    public function forms(): array
    {
        $rows = $this->db->get_results("SELECT form_id, MAX(form_name) AS form_name FROM {$this->table()} GROUP BY form_id ORDER BY form_name", ARRAY_A);
        $out = [];
        foreach (is_array($rows) ? $rows : [] as $row) {
            $out[(string) $row['form_id']] = (string) ($row['form_name'] ?? '');
        }
        return $out;
    }

    /** @param array<string, mixed> $row */
    public function decode(array $row): array
    {
        $data = json_decode((string) ($row['data'] ?? ''), true);
        $meta = json_decode((string) ($row['meta'] ?? ''), true);
        return [
            'id'         => (int) ($row['id'] ?? 0),
            'form_id'    => (string) ($row['form_id'] ?? ''),
            'form_name'  => (string) ($row['form_name'] ?? ''),
            'post_id'    => (int) ($row['post_id'] ?? 0),
            'data'       => is_array($data) ? $data : [],
            'meta'       => is_array($meta) ? $meta : [],
            'status'     => in_array($row['status'] ?? '', self::STATUSES, true) ? (string) $row['status'] : 'new',
            'created_at' => (string) ($row['created_at'] ?? ''),
        ];
    }

    /**
     * CSV export of every submission matching the filters (UTF-8 with BOM so Excel detects the encoding).
     * Columns: ID, Date, Form, Page, Status, one column per field (union over rows, first-seen order), IP,
     * User agent, Referrer.
     *
     * @param array<string, mixed> $filters same keys as query()
     */
    public function csv(array $filters = []): string
    {
        $rows = [];
        $page = 1;
        do {
            $batch = $this->query(array_merge($filters, ['per_page' => self::MAX_PER_PAGE, 'page' => $page]));
            $rows = array_merge($rows, $batch['items']);
            $page++;
        } while (count($batch['items']) === self::MAX_PER_PAGE && count($rows) < 50000);

        $columns = [];
        foreach ($rows as $row) {
            foreach ($row['data'] as $id => $field) {
                if (!isset($columns[$id])) {
                    $columns[$id] = is_array($field) && isset($field['label']) && $field['label'] !== '' ? (string) $field['label'] : (string) $id;
                }
            }
        }
        $lines = [];
        $lines[] = self::csv_line(array_merge(['ID', 'Date', 'Form', 'Page', 'Status'], array_values($columns), ['IP', 'User agent', 'Referrer']));
        foreach ($rows as $row) {
            $cells = [$row['id'], $row['created_at'], $row['form_name'] !== '' ? $row['form_name'] : $row['form_id'], (string) ($row['meta']['page'] ?? $row['post_id']), $row['status']];
            foreach (array_keys($columns) as $id) {
                $v = $row['data'][$id]['value'] ?? '';
                $cells[] = is_array($v) ? implode(', ', array_map('strval', $v)) : (string) $v;
            }
            $cells[] = (string) ($row['meta']['ip'] ?? '');
            $cells[] = (string) ($row['meta']['user_agent'] ?? '');
            $cells[] = (string) ($row['meta']['referrer'] ?? '');
            $lines[] = self::csv_line($cells);
        }
        return "\xEF\xBB\xBF" . implode("\r\n", $lines) . "\r\n";
    }

    /** @param array<int, mixed> $cells */
    public static function csv_line(array $cells): string
    {
        return implode(',', array_map([self::class, 'csv_cell'], $cells));
    }

    /**
     * One quoted CSV cell. Cells starting with = + - @ TAB or CR are prefixed with `'` so spreadsheet apps
     * never evaluate visitor input as a formula (CSV injection).
     *
     * @param mixed $value
     */
    public static function csv_cell($value): string
    {
        $value = is_scalar($value) ? (string) $value : '';
        if ($value !== '' && strpbrk($value[0], "=+-@\t\r") !== false) {
            $value = "'" . $value;
        }
        return '"' . str_replace('"', '""', $value) . '"';
    }
}