---

BEGIN;

CREATE SEQUENCE report.unv_annual_setting_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE TABLE report.unv_annual_setting (
    id integer NOT NULL
        DEFAULT nextval('report.unv_annual_setting_id_seq'::regclass),

    client_id integer NOT NULL,

    contract_id integer NOT NULL,

    annual_deduction_flag integer NOT NULL DEFAULT 0,

    selected_tax_period smallint NOT NULL,

    source character varying(50) NOT NULL DEFAULT 'LKK',

    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT unv_annual_setting_pkey
        PRIMARY KEY (id),

    CONSTRAINT uk_unv_annual_setting_client_contract
        UNIQUE (client_id, contract_id),

    CONSTRAINT chk_unv_annual_setting_flag
        CHECK (annual_deduction_flag IN (0, 1))
);

ALTER SEQUENCE report.unv_annual_setting_id_seq
    OWNED BY report.unv_annual_setting.id;

CREATE INDEX idx_unv_annual_setting_enabled
    ON report.unv_annual_setting (annual_deduction_flag)
    WHERE annual_deduction_flag = 1;

CREATE INDEX idx_unv_annual_setting_contract
    ON report.unv_annual_setting (contract_id);

ALTER TABLE report.unv_registry_item
    ALTER COLUMN annual_deduction_flag SET DEFAULT 0;

UPDATE report.unv_registry_item
SET annual_deduction_flag = 0
WHERE annual_deduction_flag IS NULL;

ALTER TABLE report.unv_registry_item
    ALTER COLUMN annual_deduction_flag SET NOT NULL;

ALTER TABLE report.unv_registry_item
    ADD CONSTRAINT chk_unv_registry_item_annual_flag
    CHECK (annual_deduction_flag IN (0, 1));

COMMIT;




SELECT
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'report'
  AND table_name = 'unv_annual_setting'
ORDER BY ordinal_position;





SELECT
    column_name,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'report'
  AND table_name = 'unv_registry_item'
  AND column_name = 'annual_deduction_flag';