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

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

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

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()
    )
  `);
};

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();
};