import { query, queryOne } from "../../lib/db.js";

type CompanySettingsRow = {
  value: {
    addressInput?: string;
    addressLabel?: string;
    lat?: number;
    lon?: number;
  };
  updated_at: string;
};

type InspectionEmailSettingsRow = {
  value: {
    recipients?: string[];
  };
  updated_at: string;
};

export type CompanySettings = {
  addressInput: string;
  addressLabel: string;
  lat: number;
  lon: number;
  updatedAt: string;
};

export type InspectionEmailSettings = {
  recipients: string[];
  updatedAt: string | null;
};

export const ensureSettingsStorage = async () => {
  await query(`
    create table if not exists system_settings (
      key text primary key,
      value jsonb not null default '{}'::jsonb,
      updated_at timestamptz not null default now()
    )
  `);

  await query(`
    create table if not exists vehicle_inspection_email_events (
      vehicle_id text not null,
      inspection_due_date date not null,
      notification_type text not null,
      recipients text[] not null default '{}'::text[],
      sent_at timestamptz not null default now(),
      primary key (vehicle_id, inspection_due_date, notification_type)
    )
  `);

  await query(`
    insert into system_settings (key, value, updated_at)
    values (
      'inspection-email',
      jsonb_build_object('recipients', to_jsonb(array['ag@notebooking.pl']::text[])),
      now()
    )
    on conflict (key) do nothing
  `);
};

export const getCompanySettings = async (): Promise<CompanySettings | null> => {
  const row = await queryOne<CompanySettingsRow>(
    `
      select value, updated_at
      from system_settings
      where key = 'company'
    `,
  );

  if (!row) {
    return null;
  }

  const addressInput = row.value?.addressInput?.trim();
  const addressLabel = row.value?.addressLabel?.trim();
  const lat = Number(row.value?.lat);
  const lon = Number(row.value?.lon);

  if (!addressInput || !addressLabel || !Number.isFinite(lat) || !Number.isFinite(lon)) {
    return null;
  }

  return {
    addressInput,
    addressLabel,
    lat,
    lon,
    updatedAt: row.updated_at,
  };
};

export const saveCompanySettings = async (input: {
  addressInput: string;
  addressLabel: string;
  lat: number;
  lon: number;
}) => {
  const saved = await queryOne<CompanySettingsRow>(
    `
      insert into system_settings (key, value, updated_at)
      values (
        'company',
        jsonb_build_object(
          'addressInput', to_jsonb($1::text),
          'addressLabel', to_jsonb($2::text),
          'lat', to_jsonb($3::double precision),
          'lon', to_jsonb($4::double precision)
        ),
        now()
      )
      on conflict (key) do update
      set
        value = excluded.value,
        updated_at = now()
      returning value, updated_at
    `,
    [input.addressInput, input.addressLabel, input.lat, input.lon],
  );

  if (!saved) {
    return null;
  }

  return getCompanySettings();
};

export const getInspectionEmailSettings = async (): Promise<InspectionEmailSettings> => {
  const row = await queryOne<InspectionEmailSettingsRow>(
    `
      select value, updated_at
      from system_settings
      where key = 'inspection-email'
    `,
  );

  const recipients = Array.isArray(row?.value?.recipients)
    ? row!.value.recipients
        .map((entry) => entry?.trim())
        .filter((entry): entry is string => Boolean(entry))
    : [];

  return {
    recipients,
    updatedAt: row?.updated_at ?? null,
  };
};

export const saveInspectionEmailSettings = async (input: {
  recipients: string[];
}) => {
  await queryOne<InspectionEmailSettingsRow>(
    `
      insert into system_settings (key, value, updated_at)
      values (
        'inspection-email',
        jsonb_build_object('recipients', to_jsonb($1::text[])),
        now()
      )
      on conflict (key) do update
      set
        value = excluded.value,
        updated_at = now()
      returning value, updated_at
    `,
    [input.recipients],
  );

  return getInspectionEmailSettings();
};