-- Active: 1775489350473@@ep-shy-rain-agbgaros-pooler.c-2.eu-central-1.aws.neon.tech@5432@neondb
-- 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)
--   - http_request_profiles: src/api/http_profiles.rs + src/http_profile.rs (CRUD + client config)
--   - data_source_provider_requests: src/data/provider_requests.rs + src/api/provider_requests.rs
--   - scraper_overrides: src/data/scraper_overrides.rs + src/api/scraper_overrides.rs (JSONB ScraperConfig)
--   - http_request_log: src/audit/mod.rs (batched HTTP audit inserts)
--   - db_operation_log: src/audit/mod.rs + src/scrape/athena.rs (batched gateway insert audit)
--
-- 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).

-- Needed for uuid defaults (gen_random_uuid()).
CREATE EXTENSION IF NOT EXISTS pgcrypto;

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

-- ---------------------------------------------------------------------------
-- http_request_profiles — named HTTP client settings (proxy SOCKS5/HTTP, timeouts, headers).
-- Payload keys match Dexter inserts from src/http_profile.rs / API handlers.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.http_request_profiles (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    proxy_url TEXT,
    timeout_secs INT NOT NULL DEFAULT 30,
    connect_timeout_secs INT,
    follow_redirects BOOLEAN NOT NULL DEFAULT TRUE,
    cookie_store BOOLEAN NOT NULL DEFAULT FALSE,
    default_headers JSONB,
    user_agent TEXT,
    danger_accept_invalid_certs BOOLEAN NOT NULL DEFAULT FALSE,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT http_request_profiles_name_key UNIQUE (name)
);

CREATE INDEX IF NOT EXISTS http_request_profiles_enabled_idx ON public.http_request_profiles (enabled);

-- ---------------------------------------------------------------------------
-- data_source_provider_requests — backlog of candidate data sources (URLs, notes, screenshot).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.data_source_provider_requests (
    id BIGSERIAL PRIMARY KEY,
    example_url TEXT NOT NULL,
    structure_notes TEXT,
    harvestable_fields JSONB,
    reliability TEXT,
    status TEXT NOT NULL DEFAULT 'pending',
    screenshot_s3_key TEXT,
    implementation_notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS data_source_provider_requests_status_idx
    ON public.data_source_provider_requests (status);

CREATE INDEX IF NOT EXISTS data_source_provider_requests_example_url_idx
    ON public.data_source_provider_requests (example_url);

-- ---------------------------------------------------------------------------
-- scraper_overrides — one row per scraper name; config JSONB mirrors YAML ScraperConfig (Rust serde).
-- Read/write: src/data/scraper_overrides.rs; cache refresh: sync_scraper_overrides (lib + reload API + CLI).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.scraper_overrides (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    config JSONB NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT scraper_overrides_name_key UNIQUE (name)
);

CREATE INDEX IF NOT EXISTS scraper_overrides_enabled_idx ON public.scraper_overrides (enabled);

CREATE INDEX IF NOT EXISTS scraper_overrides_updated_at_idx ON public.scraper_overrides (updated_at);

-- ---------------------------------------------------------------------------
-- http_request_log — outbound HTTP audit (batched async inserts; does not block scrape path).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.http_request_log (
    id BIGSERIAL PRIMARY KEY,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    source TEXT NOT NULL,
    method TEXT NOT NULL,
    url TEXT NOT NULL,
    status_code INT,
    duration_ms BIGINT,
    error TEXT,
    profile_name TEXT,
    config_name TEXT,
    response_body_bytes BIGINT,
    extra JSONB
);

CREATE INDEX IF NOT EXISTS http_request_log_occurred_at_idx ON public.http_request_log (occurred_at);

CREATE INDEX IF NOT EXISTS http_request_log_source_idx ON public.http_request_log (source);

-- ---------------------------------------------------------------------------
-- db_operation_log — Athena gateway insert outcomes (batched).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.db_operation_log (
    id BIGSERIAL PRIMARY KEY,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    table_name TEXT NOT NULL,
    status TEXT NOT NULL,
    detail TEXT,
    payload_keys JSONB
);

CREATE INDEX IF NOT EXISTS db_operation_log_occurred_at_idx ON public.db_operation_log (occurred_at);

CREATE INDEX IF NOT EXISTS db_operation_log_table_idx ON public.db_operation_log (table_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;

-- Russian legal entities / sole proprietors from ФНС ЕГРЮЛ/ЕГРИП (dexter-scrape --search-engine egrul).
CREATE TABLE IF NOT EXISTS public.ru_companies (
    id bigint generated by default as identity not null,
    created_at timestamp
    with
        time zone not null default now(),
        inn text null,
        ogrn text null,
        kpp text null,
        name text null,
        name_short text null,
        legal_form text null,
        status text null,
        address text null,
        reg_date text null,
        source_url text null,
        constraint ru_companies_pkey primary key (id)
) tablespace pg_default;

CREATE UNIQUE INDEX IF NOT EXISTS ru_companies_ogrn_key ON public.ru_companies (ogrn)
WHERE
    ogrn IS NOT NULL
    AND btrim(ogrn) <> '';

CREATE UNIQUE INDEX IF NOT EXISTS ru_companies_inn_no_ogrn_key ON public.ru_companies (inn)
WHERE
    (ogrn IS NULL OR btrim(ogrn) = '')
    AND inn IS NOT NULL
    AND btrim(inn) <> '';

-- Poland — registries.io (dexter-scrape --search-engine registries-io --country-code PL).
CREATE TABLE IF NOT EXISTS public.pl_companies (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    registration_number text not null,
    name text null,
    legal_form text null,
    status text null,
    address text null,
    registration_date text null,
    slug text null,
    vat_id text null,
    extra_registration_numbers jsonb null,
    source_url text null,
    -- registries.io company profile: API UUID + lossless JSON (sections BASIC_INFO, ADDRESSES, …)
    registry_uuid text null,
    source_document jsonb null,
    registered_name text null,
    registration_numbers jsonb null,
    share_capital_value numeric(20, 4) null,
    share_capital_currency text null,
    websites jsonb null,
    addresses jsonb null,
    activities jsonb null,
    authorized_body_name text null,
    authorized_body_entities jsonb null,
    shareholders_entities jsonb null,
    constraint pl_companies_pkey primary key (id),
    constraint pl_companies_registration_number_key unique (registration_number)
) tablespace pg_default;

-- Czech Republic — registries.io (dexter-scrape --search-engine registries-io --country-code CZ).
CREATE TABLE IF NOT EXISTS public.cz_companies (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    registration_number text not null,
    name text null,
    legal_form text null,
    status text null,
    address text null,
    registration_date text null,
    slug text null,
    vat_id text null,
    extra_registration_numbers jsonb null,
    source_url text null,
    constraint cz_companies_pkey primary key (id),
    constraint cz_companies_registration_number_key unique (registration_number)
) 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
);

-- ---------------------------------------------------------------------------
-- tax_types / tax_rates / tax_due_dates
-- Country + year-gated tax model for CIT/PIT/VAT/WHT/CGT/net wealth/inheritance.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.tax_types (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    tax_type_id uuid not null default gen_random_uuid(),
    country_code char(2) not null,
    tax_year integer not null,
    tax_category text not null,
    obligation_code text not null,
    name text,
    description text,
    metadata jsonb,
    constraint tax_types_pkey primary key (id),
    constraint tax_types_tax_type_id_key unique (tax_type_id),
    constraint tax_types_country_year_obligation_key unique (country_code, tax_year, obligation_code),
    constraint tax_types_country_year_tax_type_id_key unique (country_code, tax_year, tax_type_id),
    constraint tax_types_country_code_check check (country_code ~ '^[A-Z]{2}$'),
    constraint tax_types_tax_year_check check (tax_year >= 1900 and tax_year <= 2500),
    constraint tax_types_tax_category_check check (
        tax_category in ('CIT', 'PIT', 'VAT', 'WHT', 'CGT', 'NET_WEALTH', 'INHERITANCE_GIFT')
    )
) tablespace pg_default;

CREATE INDEX IF NOT EXISTS tax_types_lookup_idx
    ON public.tax_types (country_code, tax_year, tax_category);

CREATE TABLE IF NOT EXISTS public.tax_rates (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    tax_rate_id uuid not null default gen_random_uuid(),
    country_code char(2) not null,
    tax_year integer not null,
    tax_type_id uuid not null,
    rate_kind text not null default 'flat',
    rate_percent numeric(9, 4),
    base_amount_min numeric(18, 2),
    base_amount_max numeric(18, 2),
    effective_from date,
    effective_to date,
    rate_brackets jsonb,
    source_name text,
    source_url text,
    harvested_at timestamp with time zone,
    constraint tax_rates_pkey primary key (id),
    constraint tax_rates_tax_rate_id_key unique (tax_rate_id),
    constraint tax_rates_country_code_check check (country_code ~ '^[A-Z]{2}$'),
    constraint tax_rates_tax_year_check check (tax_year >= 1900 and tax_year <= 2500),
    constraint tax_rates_rate_kind_check check (
        rate_kind in ('flat', 'bracket', 'progressive', 'exemption')
    ),
    constraint tax_rates_effective_range_check check (
        effective_to is null or effective_from is null or effective_to >= effective_from
    ),
    constraint tax_rates_amount_range_check check (
        base_amount_max is null or base_amount_min is null or base_amount_max >= base_amount_min
    ),
    constraint tax_rates_country_year_tax_type_fkey
        foreign key (country_code, tax_year, tax_type_id)
        references public.tax_types (country_code, tax_year, tax_type_id)
) tablespace pg_default;

CREATE INDEX IF NOT EXISTS tax_rates_lookup_idx
    ON public.tax_rates (country_code, tax_year, tax_type_id);

CREATE UNIQUE INDEX IF NOT EXISTS tax_rates_dedupe_key
    ON public.tax_rates (
        country_code,
        tax_year,
        tax_type_id,
        rate_kind,
        coalesce(effective_from::text, ''),
        coalesce(effective_to::text, ''),
        coalesce(base_amount_min::text, ''),
        coalesce(base_amount_max::text, ''),
        coalesce(rate_percent::text, '')
    );

CREATE INDEX IF NOT EXISTS tax_rates_rate_brackets_gin
    ON public.tax_rates USING gin (rate_brackets);

CREATE TABLE IF NOT EXISTS public.tax_due_dates (
    id bigint generated by default as identity not null,
    created_at timestamp with time zone not null default now(),
    tax_due_date_id uuid not null default gen_random_uuid(),
    country_code char(2) not null,
    tax_year integer not null,
    tax_type_id uuid not null,
    period_code text not null default 'annual',
    due_date date,
    due_date_rule text,
    notes text,
    source_name text,
    source_url text,
    harvested_at timestamp with time zone,
    constraint tax_due_dates_pkey primary key (id),
    constraint tax_due_dates_tax_due_date_id_key unique (tax_due_date_id),
    constraint tax_due_dates_country_code_check check (country_code ~ '^[A-Z]{2}$'),
    constraint tax_due_dates_tax_year_check check (tax_year >= 1900 and tax_year <= 2500),
    constraint tax_due_dates_due_value_check check (
        due_date is not null or due_date_rule is not null
    ),
    constraint tax_due_dates_country_year_tax_type_fkey
        foreign key (country_code, tax_year, tax_type_id)
        references public.tax_types (country_code, tax_year, tax_type_id)
) tablespace pg_default;

CREATE INDEX IF NOT EXISTS tax_due_dates_lookup_idx
    ON public.tax_due_dates (country_code, tax_year, due_date);

CREATE UNIQUE INDEX IF NOT EXISTS tax_due_dates_dedupe_key
    ON public.tax_due_dates (
        country_code,
        tax_year,
        tax_type_id,
        period_code,
        coalesce(due_date::text, due_date_rule)
    );