import { query } from './db.js';

export interface AnnouncementDetail {
  id: number;
  ref_number: string;
  type: string;
  title: string | null;

  authority_name: string | null;
  authority_cui: string | null;
  authority_siruta: string | null;
  authority_type: string | null;
  authority_main_activity: string | null;
  authority_address: string | null;
  authority_email: string | null;
  authority_phone: string | null;
  authority_url: string | null;
  authority_county: string | null;

  supplier_name: string | null;
  supplier_cui: string | null;
  supplier_address: string | null;
  supplier_is_sme: boolean | null;
  supplier_county: string | null;

  cpv_code: string | null;
  cpv_name_ro: string | null;
  cpv_division: string | null;
  cpv_division_name: string | null;
  cpv_emoji: string | null;

  procedure_type: string | null;
  procedure_state: string | null;
  legislation: string | null;

  publication_date: string | null;
  deadline_submission: string | null;
  opening_date: string | null;
  contract_date: string | null;

  estimated_value: number | null;
  awarded_value: number | null;
  currency: string | null;

  has_lots: boolean | null;
  contract_has_lots: boolean | null;
  lots_count: number | null;
  framework_agreement: boolean | null;
  joue: boolean | null;

  county_code: string | null;
  notice_state: string | null;
  notice_state_id: number | null;

  documents: any | null;
  award_criteria: any | null;
  lots: any | null;
  details: any | null;

  source: string;
  seap_url: string | null;
}


export async function getAnnouncementDetail(id: number | string): Promise<AnnouncementDetail | null> {
  const result = await query<AnnouncementDetail>(`
    SELECT
      a.id, a.ref_number, a.type, a.title,
      a.authority_name, a.authority_cui, a.authority_siruta,
      a.authority_type, a.authority_main_activity, a.authority_address,
      a.authority_email, a.authority_phone, a.authority_url,
      cl_a.county AS authority_county,
      a.supplier_name, a.supplier_cui, a.supplier_address, a.supplier_is_sme,
      cl_s.county AS supplier_county,
      a.cpv_code, a.cpv_name_ro, a.cpv_division,
      c.name_ro AS cpv_division_name, c.emoji AS cpv_emoji,
      a.procedure_type, a.procedure_state, a.legislation,
      a.publication_date, a.deadline_submission, a.opening_date, a.contract_date,
      a.estimated_value, a.awarded_value, a.currency,
      a.has_lots, a.contract_has_lots, a.lots_count, a.framework_agreement, a.joue,
      a.county_code, a.notice_state, a.notice_state_id,
      a.documents, a.award_criteria, a.lots, a.details,
      a.source, a.seap_url
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
    LEFT JOIN seap.cui_location cl_a ON cl_a.cui = a.authority_cui
    LEFT JOIN seap.cui_location cl_s ON cl_s.cui = regexp_replace(a.supplier_cui, '^RO\\s*', '', 'i')
    WHERE a.id = $1
    LIMIT 1
  `, [id]);
  return result.rows[0] || null;
}
