-- Active: 1775183632804@@127.0.0.1@5432@postgres
-- Provision script for PostgreSQL behind Athena (dexter-sentinel).
-- Tables match what the app inserts/fetches via athena_rs:
--   - url_registry: src/data/mod.rs (insert + fetch by url)
--   - scraped_data: default output table (src/api/scrape.rs + config/scrapers/example.yaml)
--
-- If you add scrapers with output.table other than scraped_data, duplicate/adapt the
-- scraped_data definition for those names. If selectors change, add matching TEXT columns
-- (or migrate to a single jsonb column and change the app — not done today).

-- ---------------------------------------------------------------------------
-- url_registry
-- Payload: { "url", "config_name" } — see add_url_to_registry / check_url_in_registry_db
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.url_registry (
    id BIGSERIAL PRIMARY KEY,
    url TEXT NOT NULL,
    config_name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS url_registry_url_idx ON url_registry (url);

-- ---------------------------------------------------------------------------
-- scraped_data (default table name when output.table is unset or "scraped_data")
-- Payload: flat object — selector names + "url" + "config_name" (all string values).
-- Columns below match config/scrapers/example.yaml selectors.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.scraped_data (
    id BIGSERIAL PRIMARY KEY,
    url TEXT NOT NULL,
    config_name TEXT NOT NULL,
    title TEXT,
    price TEXT,
    image_url TEXT,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS scraped_data_url_idx ON scraped_data (url);

CREATE INDEX IF NOT EXISTS scraped_data_config_name_idx ON scraped_data (config_name);

-- Estonian legal entities (country EE).
-- Primary public identifier: 8-digit äriregistri kood (Business Register / RIK).
-- Typical Northdata suggest segment: "RIK 11283797" — store digits in registrikood, full segment optional in register_reference.
CREATE TABLE IF NOT EXISTS public.ee_companies (
    id bigint generated by default as identity not null,
    created_at timestamp
    with
        time zone not null default now(),
        registrikood text not null,
        name text null,
        legal_form text null,
        status text null,
        location text null,
        vat_id text null,
        register_reference text null,
        source_url text null,
        constraint ee_companies_pkey primary key (id),
        constraint ee_companies_registrikood_key unique (registrikood)
) tablespace pg_default;

CREATE TABLE IF NOT EXISTS public.us_companies (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        type text,
        entity_id text,
        state text,
        hash text,
        status text,
        registration_date text,
        expiration_date text,
        next_due_date text,
        street_address text,
        mailing_address text,
        name text,
        entity_type text,
        country_code text
);

CREATE TABLE IF NOT EXISTS public.uk_companies (
    company_name text NOT NULL,
    company_number text,
    company_status text,
    company_type text,
    company_subtype text,
    dissolution_date text,
    incorporation_date text,
    removed_date text,
    registered_date text,
    nature_of_business text,
    registered_office_address text,
    uk_company_id uuid NOT NULL DEFAULT gen_random_uuid (),
    id bigint NOT NULL
);

CREATE TABLE IF NOT EXISTS public.tr_companies (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        index text,
        name text,
        company_number text,
        tsm text,
        name_eng text
);

CREATE TABLE IF NOT EXISTS public.nl_companies (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone DEFAULT now(),
        kvk_number bigint,
        name text,
        postal_code text,
        active boolean,
        street text,
        area text,
        longitude double precision,
        latitude double precision,
        vat_id text,
        rsin text,
        establishment_number bigint,
        rights_form text,
        province text,
        municipality text,
        standard_business_layout text,
        rights_form_code text,
        establishment boolean,
        house_number bigint,
        neighborhood text,
        country text,
        activities jsonb,
        city text,
        main_activity text,
        main_activity_description text,
        main_sbi_code bigint
);

CREATE TABLE IF NOT EXISTS public.ir_companies (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        entity_number text,
        name text,
        type text,
        status text,
        register_date text,
        entity_type text
);

CREATE TABLE IF NOT EXISTS public.global_companies_registry (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        name text,
        company_number text,
        rights_form text,
        country_code text,
        status text,
        last_update text,
        description text,
        region text,
        rights_form_code text,
        state text,
        name_english text,
        language text,
        hash text,
        address text,
        global_company_id uuid DEFAULT gen_random_uuid (),
        start_date text,
        legal_persons text,
        contact_data text,
        activities text,
        activity_code_table text,
        establishment_date text,
        establishment_type text,
        entity_type text,
        vat_id text,
        email text,
        iban text,
        phone text,
        postal_code text,
        house_number text,
        province text,
        city text,
        street text,
        country text,
        website text
);

CREATE TABLE IF NOT EXISTS public.global_merchants_registry (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        name text NOT NULL,
        icon text,
        merchant_id uuid DEFAULT gen_random_uuid (),
        email text,
        website text,
        description text,
        billing_postal_code text,
        billing_address_line_1 text,
        billing_address_line_2 text,
        billing_city text,
        billing_state text,
        billing_province text,
        vat_id text,
        company_number text,
        company_number_type text,
        phone_number text,
        language text,
        industry text,
        slug text,
        enabled boolean DEFAULT true,
        public boolean DEFAULT true,
        verified boolean DEFAULT false,
        country_code text
);

CREATE TABLE IF NOT EXISTS public.hk_trademarks (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        sub_id numeric,
        trade_mark_number text,
        trade_mark_text text,
        status text,
        class_number text,
        applicant_name text,
        priority_date text,
        mark_type text
);

CREATE TABLE IF NOT EXISTS public.merchant_ibans (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        merchant_id uuid,
        iban text,
        country text,
        bank text,
        description text,
        bic text,
        merchant_iban_id uuid NOT NULL DEFAULT gen_random_uuid ()
);

CREATE TABLE IF NOT EXISTS public.merchants (
    id bigint NOT NULL,
    created_at timestamp
    with
        time zone NOT NULL DEFAULT now(),
        name text,
        icon text,
        merchant_id uuid DEFAULT gen_random_uuid (),
        email text,
        website text,
        description text,
        billing_postal_code text,
        billing_address_line_1 text,
        billing_address_line_2 text,
        billing_country text,
        language text,
        industry text,
        phone_number text,
        slug text,
        enabled boolean DEFAULT true,
        country text,
        public boolean DEFAULT true,
        vat_id text,
        billing_city text,
        billing_state text,
        billing_province text,
        company_number text,
        company_number_type text,
        verified boolean DEFAULT false
);

CREATE TABLE IF NOT EXISTS public.documents (
  id bigint NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  document_id uuid NOT NULL DEFAULT gen_random_uuid(),
  file_id uuid,
  file_url text,
  company_id uuid,
  merchant_id uuid,
  bank_account_id uuid,
  aurora_record_id uuid,
  aurora_request_id uuid,
  currency text DEFAULT 'EUR'::text,
  document_type text DEFAULT 'RECEIPT'::text,
  document_date text,
  receipt_number text,
  customer_info json,
  merchant_info json,
  earnings_details json,
  items json,
  vat_details json,
  totals_details json,
  system_checks json,
  reconciled boolean DEFAULT false,
  updated_at numeric,
  invoice_number text,
  misc json,
  payment_details json,
  total numeric,
  amount_due numeric,
  subtotal numeric,
  tax_amount numeric,
  total_paid numeric,
  booked boolean DEFAULT false,
  order_id text,
  debtor_number text,
  payment_term text,
  merchant_name text,
  test_mode boolean DEFAULT false
);