import { eq } from 'drizzle-orm';
import { db } from './client';
import { workspaces, appSettings, type Workspace, type WorkspaceInsert } from './schema';
import { DEFAULT_WORKSPACES, DEFAULT_SETTINGS, DEFAULT_M365_SENDERS, DEFAULT_SHOP_DOMAINS_BY_SLUG, type M365Sender } from './defaults';

// ---- Auto-Seed bei leerer DB ------------------------------------------------

let seedDone = false;
let shopDomainHealDone = false;

/**
 * Self-Healing für `workspaces.shop_domain` nach Migration 0007.
 *
 * Bei bestehenden Mailforge-Installationen sind die System-Workspace-Rows (its/ksm/spartarif)
 * nach `db:migrate` mit `shop_domain = NULL` befüllt — die Migration kann pro Slug
 * nicht backfillen. Diese Funktion füllt nur LEERE Werte und nur für die System-Slugs
 * (Map in defaults.ts). User-eigene Workspaces oder bereits gesetzte URLs werden niemals
 * überschrieben.
 *
 * Idempotent — UPDATE-Where-Filter verhindert Doppel-Schreiben. Pattern analog zum
 * `getM365Senders()`-Self-Heal: einmal pro Prozess prüfen reicht (in seedIfEmpty
 * gerufen → bei jedem Repository-Aufruf einmal billig idempotent geprüft).
 */
function ensureWorkspaceShopDomains(): void {
  if (shopDomainHealDone) return;
  for (const [slug, domain] of Object.entries(DEFAULT_SHOP_DOMAINS_BY_SLUG)) {
    const existing = db.select().from(workspaces).where(eq(workspaces.slug, slug)).get();
    if (existing && !existing.shopDomain) {
      db.update(workspaces)
        .set({ shopDomain: domain, updatedAt: new Date() })
        .where(eq(workspaces.slug, slug))
        .run();
    }
  }
  shopDomainHealDone = true;
}

/** Idempotent: schreibt Defaults falls Tabellen leer sind. Wird beim ersten DB-Lookup gerufen. */
export function seedIfEmpty(): void {
  if (seedDone) {
    // Self-Heal trotzdem prüfen — beim ersten Repo-Call NACH einer Migration läuft
    // seedIfEmpty zwar mit seedDone=true durch, aber shopDomainHealDone ist initial false.
    ensureWorkspaceShopDomains();
    return;
  }
  // better-sqlite3 ist sync — Empty-Check via limit(1).get()
  const anyBrand = db.select().from(workspaces).limit(1).get();
  if (!anyBrand) {
    for (const b of DEFAULT_WORKSPACES) db.insert(workspaces).values(b).run();
  }
  const anySetting = db.select().from(appSettings).limit(1).get();
  if (!anySetting) {
    for (const [key, value] of Object.entries(DEFAULT_SETTINGS)) {
      db.insert(appSettings).values({ key, value: value as object }).run();
    }
  }
  seedDone = true;
  ensureWorkspaceShopDomains();
}

// ---- Workspace-CRUD -------------------------------------------------------------

export function getAllWorkspaces(): Workspace[] {
  seedIfEmpty();
  return db.select().from(workspaces).orderBy(workspaces.name).all();
}

export function getWorkspaceBySlug(slug: string): Workspace | undefined {
  seedIfEmpty();
  return db.select().from(workspaces).where(eq(workspaces.slug, slug)).get();
}

export function getWorkspaceById(id: string): Workspace | undefined {
  seedIfEmpty();
  return db.select().from(workspaces).where(eq(workspaces.id, id)).get();
}

export function upsertWorkspace(input: WorkspaceInsert): Workspace {
  seedIfEmpty();
  const existing = db.select().from(workspaces).where(eq(workspaces.slug, input.slug)).get();
  if (existing) {
    db.update(workspaces)
      .set({ ...input, updatedAt: new Date() })
      .where(eq(workspaces.slug, input.slug))
      .run();
    return db.select().from(workspaces).where(eq(workspaces.slug, input.slug)).get()!;
  }
  db.insert(workspaces).values(input).run();
  return db.select().from(workspaces).where(eq(workspaces.slug, input.slug)).get()!;
}

export function deleteWorkspace(slug: string): boolean {
  seedIfEmpty();
  const result = db.delete(workspaces).where(eq(workspaces.slug, slug)).run();
  return result.changes > 0;
}

// ---- Settings-CRUD (typed Helpers) ------------------------------------------

function getSettingRaw<T = unknown>(key: string): T | undefined {
  seedIfEmpty();
  const row = db.select().from(appSettings).where(eq(appSettings.key, key)).get();
  return row?.value as T | undefined;
}

function setSettingRaw(key: string, value: unknown): void {
  seedIfEmpty();
  const existing = db.select().from(appSettings).where(eq(appSettings.key, key)).get();
  if (existing) {
    db.update(appSettings)
      .set({ value: value as object, updatedAt: new Date() })
      .where(eq(appSettings.key, key))
      .run();
  } else {
    db.insert(appSettings).values({ key, value: value as object }).run();
  }
}

/**
 * Public-Aliase für `getSettingRaw`/`setSettingRaw` — werden vom Conversion-Poller
 * (Phase 3b) und potentiell anderen generischen Settings-Konsumenten genutzt.
 * Die typed Bundle-Versionen `getAllSettings`/`updateSettings` bleiben für die
 * UI/Settings-API der Wahrheit für strukturierte Settings.
 */
export function getAppSetting<T = unknown>(key: string): T | undefined {
  return getSettingRaw<T>(key);
}

export function setAppSetting(key: string, value: unknown): void {
  setSettingRaw(key, value);
}

export function getSystemPromptGenerate(): string {
  return getSettingRaw<string>('system_prompt_generate') ?? '';
}

export function setSystemPromptGenerate(prompt: string): void {
  setSettingRaw('system_prompt_generate', prompt);
}

export function getSystemPromptSuggest(): string {
  return getSettingRaw<string>('system_prompt_suggest') ?? '';
}

export function setSystemPromptSuggest(prompt: string): void {
  setSettingRaw('system_prompt_suggest', prompt);
}

export function getSystemPromptSuggestUtm(): string {
  return getSettingRaw<string>('system_prompt_suggest_utm') ?? '';
}

export function setSystemPromptSuggestUtm(prompt: string): void {
  setSettingRaw('system_prompt_suggest_utm', prompt);
}

export function getSystemPromptImage(): string {
  return getSettingRaw<string>('system_prompt_image') ?? '';
}

export function setSystemPromptImage(prompt: string): void {
  setSettingRaw('system_prompt_image', prompt);
}

// ---- M365-Sender-Pool -------------------------------------------------------

/**
 * Liefert alle konfigurierten Sender-Adressen (inkl. inaktive).
 * Self-Healing: Wenn die DB schon einen Pool hat, aber neue DEFAULT_M365_SENDERS
 * (z.B. nach Code-Update mit zusätzlichen Adressen) noch nicht enthält, werden sie
 * idempotent gemerged — so muss kein manueller Migrations-Step gefahren werden.
 * Vom User explizit deaktivierte oder umbenannte Sender bleiben unverändert (Match per Email).
 */
export function getM365Senders(): M365Sender[] {
  const stored = getSettingRaw<M365Sender[]>('m365_senders');
  if (!stored || stored.length === 0) {
    setM365Senders(DEFAULT_M365_SENDERS);
    return DEFAULT_M365_SENDERS;
  }
  const storedEmails = new Set(stored.map((s) => s.email.toLowerCase()));
  const missing = DEFAULT_M365_SENDERS.filter((d) => !storedEmails.has(d.email.toLowerCase()));
  if (missing.length === 0) return stored;
  const merged = [...stored, ...missing];
  setM365Senders(merged);
  return merged;
}

/** Liefert nur aktive Sender — für UI-Dropdown. */
export function getActiveM365Senders(): M365Sender[] {
  return getM365Senders().filter((s) => s.active);
}

/** Lookup nach Email — Case-insensitive. */
export function findM365Sender(email: string): M365Sender | undefined {
  const target = email.trim().toLowerCase();
  return getM365Senders().find((s) => s.email.toLowerCase() === target);
}

/** Resolve: explizite Email > companyId im Pool > Workspace-Sender-Email im Pool > genau ein aktiver Pool-Sender (Single-Mailbox-Betrieb). */
export function resolveM365Sender(opts: {
  explicitEmail?: string;
  companyId?: number | null;
  /** Workspace `sender_email` — greift, wenn dieselbe Adresse im Pool (aktiv) existiert. */
  workspaceDefaultEmail?: string | null;
}): M365Sender | undefined {
  const explicit = opts.explicitEmail?.trim();
  if (explicit) {
    const found = findM365Sender(explicit);
    if (found?.active) return found;
    return undefined;
  }
  if (opts.companyId != null) {
    const byCompany = getActiveM365Senders().find((s) => s.companyId === opts.companyId);
    if (byCompany) return byCompany;
  }
  const wsEmail = opts.workspaceDefaultEmail?.trim();
  if (wsEmail) {
    const found = findM365Sender(wsEmail);
    if (found?.active) return found;
  }
  const actives = getActiveM365Senders();
  if (actives.length === 1) return actives[0];
  return undefined;
}

export function setM365Senders(senders: M365Sender[]): void {
  setSettingRaw('m365_senders', senders);
}

// ---- Komplettes Settings-Bundle (für UI laden) -----------------------------

export interface SettingsBundle {
  systemPromptGenerate: string;
  systemPromptSuggest: string;
  systemPromptImage: string;
}

export function getAllSettings(): SettingsBundle {
  return {
    systemPromptGenerate: getSystemPromptGenerate(),
    systemPromptSuggest: getSystemPromptSuggest(),
    systemPromptImage: getSystemPromptImage(),
  };
}

export function updateSettings(patch: Partial<SettingsBundle>): SettingsBundle {
  if (patch.systemPromptGenerate !== undefined) setSystemPromptGenerate(patch.systemPromptGenerate);
  if (patch.systemPromptSuggest !== undefined) setSystemPromptSuggest(patch.systemPromptSuggest);
  if (patch.systemPromptImage !== undefined) setSystemPromptImage(patch.systemPromptImage);
  return getAllSettings();
}
