-- Tax harvesting schema for country/year-gated tax obligations, rates, and due dates.
-- Safe to re-run: CREATE EXTENSION/TABLE/INDEX IF NOT EXISTS.

BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

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

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

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

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

COMMIT;
