<?php
namespace Noesis\Forms;
defined("ABSPATH") || exit();
require_once __DIR__ . "/emails.php";

function install(): void
{
    global $wpdb;
    require_once ABSPATH . "wp-admin/includes/upgrade.php";
    $charset = $wpdb->get_charset_collate();
    $tables = [];
    $tables[] =
        "CREATE TABLE " .
        table("forms") .
        " (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 slug varchar(120) NOT NULL,
 name varchar(190) NOT NULL,
 status varchar(20) NOT NULL DEFAULT 'draft',
 schema_json longtext NOT NULL,
 settings_json longtext NOT NULL,
 version bigint(20) unsigned NOT NULL DEFAULT 1,
 created_at datetime NOT NULL,
 updated_at datetime NOT NULL,
 PRIMARY KEY  (id),
 UNIQUE KEY slug (slug),
 KEY status (status)
) ENGINE=InnoDB $charset;";
    $tables[] =
        "CREATE TABLE " .
        table("entries") .
        " (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 reference varchar(36) NOT NULL,
 form_id bigint(20) unsigned NOT NULL,
 status varchar(20) NOT NULL DEFAULT 'new',
 starred tinyint(1) NOT NULL DEFAULT 0,
 contact_name varchar(190) NOT NULL DEFAULT '',
 email varchar(190) NOT NULL DEFAULT '',
 company varchar(190) NOT NULL DEFAULT '',
 subject varchar(255) NOT NULL DEFAULT '',
 source_path varchar(500) NOT NULL DEFAULT '',
 schema_json longtext NOT NULL,
 submission_key varchar(64) NOT NULL,
 request_hash varchar(64) NOT NULL,
 legacy_post_id bigint(20) unsigned DEFAULT NULL,
 notification_status varchar(20) NOT NULL DEFAULT 'pending',
 created_at datetime NOT NULL,
 updated_at datetime NOT NULL,
 PRIMARY KEY  (id),
 UNIQUE KEY reference (reference),
 UNIQUE KEY submission_key (submission_key),
 UNIQUE KEY legacy_post_id (legacy_post_id),
 KEY form_created (form_id,created_at),
 KEY status_created (status,created_at),
 KEY email (email),
 KEY created_at (created_at)
) ENGINE=InnoDB $charset;";
    $tables[] =
        "CREATE TABLE " .
        table("values") .
        " (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 entry_id bigint(20) unsigned NOT NULL,
 field_id varchar(80) NOT NULL,
 field_type varchar(30) NOT NULL,
 label varchar(255) NOT NULL,
 value_json longtext NOT NULL,
 position int(11) NOT NULL DEFAULT 0,
 PRIMARY KEY  (id),
 UNIQUE KEY entry_field (entry_id,field_id),
 KEY entry_id (entry_id)
) ENGINE=InnoDB $charset;";
    $tables[] =
        "CREATE TABLE " .
        table("notes") .
        " (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 entry_id bigint(20) unsigned NOT NULL,
 user_id bigint(20) unsigned NOT NULL,
 body text NOT NULL,
 created_at datetime NOT NULL,
 PRIMARY KEY  (id),
 KEY entry_id (entry_id)
) ENGINE=InnoDB $charset;";
    $tables[] =
        "CREATE TABLE " .
        table("files") .
        " (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 entry_id bigint(20) unsigned NOT NULL,
 field_id varchar(80) NOT NULL,
 original_name varchar(255) NOT NULL,
 mime_type varchar(100) NOT NULL,
 bytes bigint(20) unsigned NOT NULL,
 content longblob NOT NULL,
 created_at datetime NOT NULL,
 PRIMARY KEY  (id),
 KEY entry_id (entry_id)
) ENGINE=InnoDB $charset;";
    $tables[] =
        "CREATE TABLE " .
        table("jobs") .
        " (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 entry_id bigint(20) unsigned NOT NULL,
 kind varchar(25) NOT NULL,
 payload_json longtext NOT NULL,
 status varchar(20) NOT NULL DEFAULT 'pending',
 attempts int(11) NOT NULL DEFAULT 0,
 due_at datetime NOT NULL,
 last_error varchar(500) NOT NULL DEFAULT '',
 created_at datetime NOT NULL,
 updated_at datetime NOT NULL,
 PRIMARY KEY  (id),
 UNIQUE KEY entry_kind (entry_id,kind),
 KEY due_jobs (status,due_at)
) ENGINE=InnoDB $charset;";
    $tables[] =
        "CREATE TABLE " .
        table("changes") .
        " (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 entry_id bigint(20) unsigned NOT NULL,
 form_id bigint(20) unsigned NOT NULL,
 operation varchar(15) NOT NULL DEFAULT 'upsert',
 created_at datetime NOT NULL,
 PRIMARY KEY  (id),
 KEY form_cursor (form_id,id),
 KEY entry_id (entry_id)
) ENGINE=InnoDB $charset;";
    foreach ($tables as $sql) {
        dbDelta($sql);
    }
    foreach (
        ["forms", "entries", "values", "notes", "files", "jobs", "changes"]
        as $name
    ) {
        // Activation must check the actual schema, rather than a cached result.
        $installed_table = $wpdb->get_var(
            $wpdb->prepare(
                "SHOW TABLES LIKE %s",
                $wpdb->esc_like(table($name)),
            ),
        );
        if ($installed_table !== table($name)) {
            throw new \RuntimeException(
                "Could not create Feather Forms tables.",
            );
        }
    }
    invalidate_form_cache();
    $admin = get_role("administrator");
    if ($admin) {
        foreach (
            [
                "manage_noesis_forms",
                "view_noesis_entries",
                "export_noesis_entries",
                "delete_noesis_entries",
                "read_noesis_leads",
            ]
            as $cap
        ) {
            $admin->add_cap($cap);
        }
    }
    add_role("noesis_leads_reader", "Feather Form Leads Reader", [
        "read" => true,
        "read_noesis_leads" => true,
    ]);
    update_option("noesis_forms_version", DB_VERSION, false);
    if (!wp_next_scheduled("noesis_forms_jobs")) {
        wp_schedule_event(time() + 60, "hourly", "noesis_forms_jobs");
    }
    if (!wp_next_scheduled("noesis_forms_maintenance")) {
        wp_schedule_event(
            time() + DAY_IN_SECONDS,
            "daily",
            "noesis_forms_maintenance",
        );
    }
    if (!get_option("noesis_forms_contact_id")) {
        $contact = create_form(contact_template());
        if (!is_wp_error($contact)) {
            update_option("noesis_forms_contact_id", $contact["id"], false);
        }
    }
    migrate_legacy();
    purge();
}

/** Rename the existing role without recreating it or altering its capabilities. */
function update_reader_role_label(): void
{
    $roles = wp_roles();
    $key = "noesis_leads_reader";
    if (($roles->roles[$key]["name"] ?? "") !== "Noesis Leads Reader") {
        return;
    }
    $roles->roles[$key]["name"] = "Feather Form Leads Reader";
    $roles->role_names[$key] = "Feather Form Leads Reader";
    if ($roles->use_db) {
        update_option($roles->role_key, $roles->roles);
    }
}

function decode_form(array $row): array
{
    $row["id"] = (int) $row["id"];
    $row["version"] = (int) $row["version"];
    $row["schema"] = json_decode($row["schema_json"], true) ?: ["pages" => []];
    $row["settings"] = json_decode($row["settings_json"], true) ?: [];
    $row["settings"]["email_templates"] = email_templates($row["settings"]);
    unset($row["schema_json"], $row["settings_json"]);
    return $row;
}
/**
 * Definitions use a site-scoped object-cache group, never rendered HTML or leads.
 * Capture the generation before reading so a delayed reader cannot populate the
 * new generation that a successful save has created with an older definition.
 */
function form_cache_key(string $item): string
{
    return VERSION .
        ":" .
        $item .
        ":" .
        wp_cache_get_last_changed("noesis_forms_definitions");
}

function invalidate_form_cache(): void
{
    wp_cache_set_last_changed("noesis_forms_definitions");
}

/** Submission, transaction and optimistic-save checks must request a fresh row. */
function get_form(int $id, bool $fresh = false): ?array
{
    global $wpdb;
    $key = $fresh ? "" : form_cache_key("form:" . $id);
    $found = false;
    $row = $fresh
        ? false
        : wp_cache_get($key, "noesis_forms_definitions", false, $found);
    if ($found) {
        // Decode per call so website email and branding defaults stay current.
        return $row ? decode_form($row) : null;
    }
    $row = $wpdb->get_row(
        $wpdb->prepare("SELECT * FROM %i WHERE id=%d", table("forms"), $id),
        ARRAY_A,
    );
    if (!$fresh && !$wpdb->last_error) {
        // A successful create rotates the generation, including cached misses.
        wp_cache_set(
            $key,
            $row ?: false,
            "noesis_forms_definitions",
            5 * MINUTE_IN_SECONDS,
        );
    }
    return $row ? decode_form($row) : null;
}

/** Non-sensitive picker metadata shared by the builder, inbox, exports and scope UI. */
function form_catalog(): array
{
    global $wpdb;
    $key = form_cache_key("catalog");
    $found = false;
    $rows = wp_cache_get($key, "noesis_forms_definitions", false, $found);
    if ($found) {
        return $rows;
    }
    $rows =
        $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id,name,status FROM %i ORDER BY name,id",
                table("forms"),
            ),
            ARRAY_A,
        ) ?? [];
    if (!$wpdb->last_error) {
        wp_cache_set(
            $key,
            $rows,
            "noesis_forms_definitions",
            5 * MINUTE_IN_SECONDS,
        );
    }
    return $rows;
}
function create_form(array $input)
{
    global $wpdb;
    $clean = clean_form($input);
    if (is_wp_error($clean)) {
        return $clean;
    }
    $slug = sanitize_title($clean["name"]) ?: "form";
    $base = substr($slug, 0, 95);
    $slug = $base;
    $suffix = 1;
    while (
        $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM %i WHERE slug=%s",
                table("forms"),
                $slug,
            ),
        )
    ) {
        $slug = $base . "-" . ++$suffix;
    }
    $ok = $wpdb->insert(table("forms"), [
        "name" => $clean["name"],
        "slug" => $slug,
        "status" => $clean["status"],
        "schema_json" => json($clean["schema"]),
        "settings_json" => json($clean["settings"]),
        "created_at" => now(),
        "updated_at" => now(),
    ]);
    if (!$ok) {
        return problem("The form could not be saved.", 500);
    }
    $id = (int) $wpdb->insert_id;
    invalidate_form_cache();
    purge();
    return get_form($id, true);
}
function save_form(int $id, array $input)
{
    global $wpdb;
    $old = get_form($id, true);
    if (!$old) {
        return problem("Form not found.", 404);
    }
    $clean = clean_form($input);
    if (is_wp_error($clean)) {
        return $clean;
    }
    if (
        (int) get_option("noesis_forms_contact_id") === $id &&
        $clean["status"] !== "active"
    ) {
        return problem(
            "Select a different active website contact form in Settings before archiving this one.",
        );
    }
    if ((int) ($input["version"] ?? 0) !== $old["version"]) {
        return problem(
            "This form changed in another window. Reload before saving.",
            409,
        );
    }
    // Webhook secrets are write-only and are never included in exports or builder responses.
    if (empty($clean["settings"]["webhook_secret"])) {
        $clean["settings"]["webhook_secret"] =
            $old["settings"]["webhook_secret"] ?? "";
    }
    $updated = $wpdb->query(
        $wpdb->prepare(
            "UPDATE %i SET name=%s,status=%s,schema_json=%s,settings_json=%s,version=version+1,updated_at=%s WHERE id=%d AND version=%d",
            table("forms"),
            $clean["name"],
            $clean["status"],
            json($clean["schema"]),
            json($clean["settings"]),
            now(),
            $id,
            $old["version"],
        ),
    );
    if (!$updated) {
        return problem(
            "The form changed before it could be saved. Reload and try again.",
            409,
        );
    }
    invalidate_form_cache();
    purge();
    return get_form($id, true);
}
function public_form(array $form): array
{
    $form["settings"]["webhook_secret_set"] = !empty(
        $form["settings"]["webhook_secret"]
    );
    unset($form["settings"]["webhook_secret"]);
    $form["preview_url"] = nonce_link(
        wp_make_link_relative(home_url("/?noesis_form_preview=" . $form["id"])),
        "nf_preview_" . $form["id"],
    );
    return $form;
}
function entry_values(int $id): array
{
    global $wpdb;
    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT field_id,field_type,label,value_json FROM %i WHERE entry_id=%d ORDER BY position,id",
            table("values"),
            $id,
        ),
        ARRAY_A,
    );
    foreach ($rows as &$row) {
        $row["value"] = json_decode($row["value_json"], true);
        unset($row["value_json"]);
    }
    return $rows;
}
function value_text($value): string
{
    if (is_array($value)) {
        return implode(
            "\n",
            array_map(
                static fn($v) => is_array($v)
                    ? implode(" · ", array_map("strval", $v))
                    : (string) $v,
                $value,
            ),
        );
    }
    return is_bool($value) ? ($value ? "Yes" : "No") : (string) $value;
}
function migrate_legacy(): array
{
    global $wpdb;
    $form_id = (int) get_option("noesis_forms_contact_id");
    $form = get_form($form_id);
    $count = 0;
    if (!$form) {
        return ["migrated" => 0];
    }
    $posts = $wpdb->get_results(
        $wpdb->prepare(
            'SELECT ID,post_content,post_date_gmt,post_date FROM %i WHERE post_type=\'enquiry\' AND post_status<>\'trash\' ORDER BY ID',
            $wpdb->posts,
        ),
    );
    foreach ($posts as $post) {
        if (
            $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM %i WHERE legacy_post_id=%d",
                    table("entries"),
                    $post->ID,
                ),
            )
        ) {
            continue;
        }
        $raw = $post->post_content;
        $values = [];
        foreach (
            [
                "name" => "Name",
                "email" => "Email",
                "company" => "Company",
                "service" => "Interest",
            ]
            as $key => $label
        ) {
            if (preg_match("/^" . $label . ':\s*(.*)$/m', $raw, $match)) {
                $values[$key] = sanitize_text_field($match[1]);
            }
        }
        $message = preg_replace(
            '/\A.*?^Interest:[^\r\n]*\r?\n\r?\n/ms',
            "",
            $raw,
        );
        $message = preg_replace(
            '/\r?\n\r?\nConsent:[^\r\n]*\s*\z/s',
            "",
            $message,
        );
        $values["message"] = sanitize_textarea_field($message);
        $values["consent"] = str_contains($raw, "Consent: provided")
            ? "yes"
            : "";
        $date =
            $post->post_date_gmt !== "0000-00-00 00:00:00"
                ? $post->post_date_gmt
                : get_gmt_from_date($post->post_date);
        $wpdb->query("START TRANSACTION");
        $ok = $wpdb->insert(table("entries"), [
            "reference" => wp_generate_uuid4(),
            "form_id" => $form_id,
            "contact_name" => $values["name"] ?? "",
            "email" => $values["email"] ?? "",
            "company" => $values["company"] ?? "",
            "subject" => $values["service"] ?? "",
            "schema_json" => json($form["schema"]),
            "submission_key" => hash("sha256", "legacy:" . $post->ID),
            "request_hash" => hash("sha256", $raw),
            "legacy_post_id" => $post->ID,
            "notification_status" => get_post_meta(
                $post->ID,
                "_notification_sent",
                true,
            )
                ? "sent"
                : "unknown",
            "created_at" => $date,
            "updated_at" => $date,
        ]);
        $entry_id = (int) $wpdb->insert_id;
        foreach (flatten($form["schema"]) as $i => $field) {
            if (!array_key_exists($field["id"], $values)) {
                continue;
            }
            $ok =
                $ok &&
                (bool) $wpdb->insert(table("values"), [
                    "entry_id" => $entry_id,
                    "field_id" => $field["id"],
                    "field_type" => $field["type"],
                    "label" => $field["label"],
                    "value_json" => json($values[$field["id"]]),
                    "position" => $i,
                ]);
        }
        if ($ok) {
            $ok = record_change($entry_id, "upsert", true);
        }
        if ($ok) {
            $wpdb->query("COMMIT");
            $count++;
        } else {
            $wpdb->query("ROLLBACK");
        }
    }
    return ["migrated" => $count, "originals_preserved" => count($posts)];
}

function remove_entry_data(int $id, bool $anonymise = false): void
{
    global $wpdb;
    $wpdb->query("START TRANSACTION");
    if (
        !$wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM %i WHERE id=%d FOR UPDATE",
                table("entries"),
                $id,
            ),
        )
    ) {
        $wpdb->query("ROLLBACK");
        return;
    }
    $ok = record_change($id, "delete", true);
    foreach (["values", "notes", "files", "jobs"] as $name) {
        $ok = $ok && $wpdb->delete(table($name), ["entry_id" => $id]) !== false;
    }
    if ($anonymise) {
        $ok =
            $ok &&
            $wpdb->update(
                table("entries"),
                [
                    "contact_name" => "",
                    "email" => "",
                    "company" => "",
                    "subject" => "",
                    "source_path" => "",
                    "status" => "anonymised",
                    "schema_json" => "{}",
                    "notification_status" => "removed",
                    "updated_at" => now(),
                ],
                ["id" => $id],
            ) !== false;
    } else {
        $ok = $ok && $wpdb->delete(table("entries"), ["id" => $id]) !== false;
    }
    if ($ok) {
        $wpdb->query("COMMIT");
    } else {
        $wpdb->query("ROLLBACK");
    }
    if (!$ok) {
        throw new \RuntimeException(
            "The enquiry could not be removed. Its data has been retained. Please try again.",
        );
    }
}
function maintenance(): void
{
    global $wpdb;
    foreach (
        $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM %i", table("forms")),
            ARRAY_A,
        )
        as $row
    ) {
        $form = decode_form($row);
        $days = (int) ($form["settings"]["retention_days"] ?? 0);
        if (!$days) {
            continue;
        }
        $ids = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM %i WHERE form_id=%d AND created_at<%s AND legacy_post_id IS NULL LIMIT 200",
                table("entries"),
                $form["id"],
                gmdate("Y-m-d H:i:s", time() - $days * DAY_IN_SECONDS),
            ),
        );
        foreach ($ids as $id) {
            remove_entry_data((int) $id);
        }
    }
    $wpdb->query(
        $wpdb->prepare(
            'DELETE FROM %i WHERE status=\'sent\' AND updated_at<%s',
            table("jobs"),
            gmdate("Y-m-d H:i:s", time() - 30 * DAY_IN_SECONDS),
        ),
    );
    // A request interrupted during delivery may be retried after its short lease.
    $wpdb->query(
        $wpdb->prepare(
            'UPDATE %i SET status=\'pending\' WHERE status=\'processing\' AND updated_at<%s',
            table("jobs"),
            gmdate("Y-m-d H:i:s", time() - 15 * MINUTE_IN_SECONDS),
        ),
    );
}