import "server-only";
import { formsExecute, formsTablesExist } from "./db";

// ──────────────────────────────────────────────────────────────────────────
// Tabellen der gemeinsamen Formular-Datenbank.
//
// Ausschließlich `CREATE TABLE IF NOT EXISTS` mit exakt dem vereinbarten
// Schema. Kein ALTER, kein DROP, kein TRUNCATE — die Tabellen gehören beiden
// Websites. `position_id` bleibt bewusst ohne Fremdschlüssel, damit gelöschte
// Stellen ihre Bewerbungen nicht mitreißen.
// ──────────────────────────────────────────────────────────────────────────

export const FORMS_TABLES = [
  "avaria_positions",
  "avaria_applications",
  "avaria_contact_messages",
] as const;

const CREATE_POSITIONS = `
CREATE TABLE IF NOT EXISTS avaria_positions (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug        VARCHAR(80) NOT NULL,
    category    VARCHAR(20) NOT NULL DEFAULT 'creator',
    title       VARCHAR(140) NOT NULL,
    summary     VARCHAR(255) NOT NULL DEFAULT '',
    description TEXT NOT NULL,
    fields_json LONGTEXT NOT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    is_active   TINYINT UNSIGNED NOT NULL DEFAULT 1,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

const CREATE_APPLICATIONS = `
CREATE TABLE IF NOT EXISTS avaria_applications (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    position_id     INT UNSIGNED NULL,
    position_title  VARCHAR(140) NOT NULL,
    category        VARCHAR(20) NOT NULL DEFAULT '',
    applicant_name  VARCHAR(120) NOT NULL,
    applicant_email VARCHAR(180) NOT NULL,
    answers_json    LONGTEXT NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'new',
    is_read         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_created (created_at),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

const CREATE_MESSAGES = `
CREATE TABLE IF NOT EXISTS avaria_contact_messages (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(120) NOT NULL,
    email      VARCHAR(180) NOT NULL,
    subject    VARCHAR(180) NOT NULL,
    message    TEXT NOT NULL,
    is_read    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

/**
 * Legt fehlende Tabellen an. Vorhandene bleiben unangetastet — auch wenn ihr
 * Aufbau abweichen sollte, wird nichts angepasst.
 */
export async function ensureFormsTables(): Promise<{ created: string[]; existing: string[] }> {
  const before = await formsTablesExist([...FORMS_TABLES]);

  await formsExecute(CREATE_POSITIONS);
  await formsExecute(CREATE_APPLICATIONS);
  await formsExecute(CREATE_MESSAGES);

  return {
    created: FORMS_TABLES.filter((t) => !before.has(t)),
    existing: FORMS_TABLES.filter((t) => before.has(t)),
  };
}

/** Welche der drei Tabellen sind vorhanden? (ohne etwas anzulegen) */
export async function checkFormsTables(): Promise<Record<string, boolean>> {
  const found = await formsTablesExist([...FORMS_TABLES]);
  return Object.fromEntries(FORMS_TABLES.map((t) => [t, found.has(t)]));
}