import "server-only";
import { formsQuery, isFormsConfigured, formsHealth } from "./db";
import { parseAnswers, parseFields, type Answer, type PositionField } from "./contract";

// ──────────────────────────────────────────────────────────────────────────
// Lesezugriffe auf die gemeinsame Formular-Datenbank.
// Alle Funktionen sind ausfallsicher: Ist die Datenbank nicht erreichbar,
// kommen leere Listen zurück und das Team OS läuft normal weiter.
// ──────────────────────────────────────────────────────────────────────────

export type PositionDTO = {
  id: number;
  slug: string;
  category: string;
  title: string;
  summary: string;
  description: string;
  fields: PositionField[];
  sortOrder: number;
  isActive: boolean;
  createdAt: string;
  updatedAt: string;
  applicationCount: number;
};

export type ApplicationDTO = {
  id: number;
  positionId: number | null;
  positionTitle: string;
  category: string;
  applicantName: string;
  applicantEmail: string;
  answers: Answer[];
  status: string;
  isRead: boolean;
  createdAt: string;
};

export type MessageDTO = {
  id: number;
  name: string;
  email: string;
  subject: string;
  message: string;
  isRead: boolean;
  createdAt: string;
};

type PositionRow = {
  id: number;
  slug: string;
  category: string;
  title: string;
  summary: string;
  description: string;
  fields_json: string;
  sort_order: number;
  is_active: number;
  created_at: string;
  updated_at: string;
  application_count: number | string;
};

export async function getPositions(): Promise<PositionDTO[]> {
  const rows = await formsQuery<PositionRow>(
    `SELECT p.*,
            (SELECT COUNT(*) FROM avaria_applications a WHERE a.position_id = p.id) AS application_count
       FROM avaria_positions p
      ORDER BY p.sort_order ASC, p.id ASC`,
    [],
    [],
  );

  return rows.map((r) => ({
    id: Number(r.id),
    slug: r.slug,
    category: r.category,
    title: r.title,
    summary: r.summary,
    description: r.description,
    fields: parseFields(r.fields_json),
    sortOrder: Number(r.sort_order),
    isActive: Number(r.is_active) === 1,
    createdAt: String(r.created_at),
    updatedAt: String(r.updated_at),
    applicationCount: Number(r.application_count ?? 0),
  }));
}

type ApplicationRow = {
  id: number;
  position_id: number | null;
  position_title: string;
  category: string;
  applicant_name: string;
  applicant_email: string;
  answers_json: string;
  status: string;
  is_read: number;
  created_at: string;
};

export async function getApplications(status?: string | null): Promise<ApplicationDTO[]> {
  const where = status ? "WHERE status = ?" : "";
  const rows = await formsQuery<ApplicationRow>(
    `SELECT * FROM avaria_applications ${where} ORDER BY created_at DESC, id DESC LIMIT 500`,
    status ? [status] : [],
    [],
  );

  return rows.map((r) => ({
    id: Number(r.id),
    positionId: r.position_id === null ? null : Number(r.position_id),
    positionTitle: r.position_title,
    category: r.category,
    applicantName: r.applicant_name,
    applicantEmail: r.applicant_email,
    answers: parseAnswers(r.answers_json),
    status: r.status,
    isRead: Number(r.is_read) === 1,
    createdAt: String(r.created_at),
  }));
}

type MessageRow = {
  id: number;
  name: string;
  email: string;
  subject: string;
  message: string;
  is_read: number;
  created_at: string;
};

export async function getMessages(): Promise<MessageDTO[]> {
  const rows = await formsQuery<MessageRow>(
    `SELECT * FROM avaria_contact_messages ORDER BY created_at DESC, id DESC LIMIT 500`,
    [],
    [],
  );

  return rows.map((r) => ({
    id: Number(r.id),
    name: r.name,
    email: r.email,
    subject: r.subject,
    message: r.message,
    isRead: Number(r.is_read) === 1,
    createdAt: String(r.created_at),
  }));
}

export type FormsOverview = {
  configured: boolean;
  online: boolean;
  error?: string;
  unreadApplications: number;
  unreadMessages: number;
  activePositions: number;
};

/** Kennzahlen für Reiter und Ungelesen-Zähler. */
export async function getFormsOverview(): Promise<FormsOverview> {
  if (!isFormsConfigured()) {
    return {
      configured: false,
      online: false,
      unreadApplications: 0,
      unreadMessages: 0,
      activePositions: 0,
    };
  }

  const health = await formsHealth();
  if (!health.ok) {
    return {
      configured: true,
      online: false,
      error: health.error,
      unreadApplications: 0,
      unreadMessages: 0,
      activePositions: 0,
    };
  }

  const [apps, msgs, pos] = await Promise.all([
    formsQuery<{ n: number }>(
      "SELECT COUNT(*) AS n FROM avaria_applications WHERE is_read = 0",
      [],
      [{ n: 0 }],
    ),
    formsQuery<{ n: number }>(
      "SELECT COUNT(*) AS n FROM avaria_contact_messages WHERE is_read = 0",
      [],
      [{ n: 0 }],
    ),
    formsQuery<{ n: number }>(
      "SELECT COUNT(*) AS n FROM avaria_positions WHERE is_active = 1",
      [],
      [{ n: 0 }],
    ),
  ]);

  return {
    configured: true,
    online: true,
    unreadApplications: Number(apps[0]?.n ?? 0),
    unreadMessages: Number(msgs[0]?.n ?? 0),
    activePositions: Number(pos[0]?.n ?? 0),
  };
}

/** Freier Slug — bei Namensgleichheit -2, -3 … (eigene ID ausgenommen). */
export async function freeSlug(base: string, selfId?: number): Promise<string> {
  let slug = base;
  for (let n = 2; n < 200; n++) {
    const rows = await formsQuery<{ id: number }>(
      selfId
        ? "SELECT id FROM avaria_positions WHERE slug = ? AND id <> ? LIMIT 1"
        : "SELECT id FROM avaria_positions WHERE slug = ? LIMIT 1",
      selfId ? [slug, selfId] : [slug],
      [],
    );
    if (rows.length === 0) return slug;
    slug = `${base.slice(0, 66)}-${n}`;
  }
  return `${base.slice(0, 60)}-${Date.now().toString(36)}`;
}