--
-- PostgreSQL database dump
--

-- Dumped from database version 14.13 (Debian 14.13-1.pgdg120+1)
-- Dumped by pg_dump version 14.13 (Debian 14.13-1.pgdg120+1)

CREATE SCHEMA IF NOT EXISTS ray;

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: ray; Type: SCHEMA; Schema: -; Owner: gw-user
--

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: connection; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.connection (
    connection_id uuid DEFAULT gen_random_uuid() NOT NULL,
    connection_type character varying(255) NOT NULL,
    label character varying(255) NOT NULL,
    description text NOT NULL,
    params jsonb NOT NULL,
    update_metadata_schedule text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'processed' CHECK (status IN ('deleted', 'updated', 'processed'))
);


--
-- Name: connection_domain; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.connection_domain (
    connection_id uuid NOT NULL,
    domain_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);



--
-- Name: dc_integration; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.dc_integration (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name character varying(255) NOT NULL,
    type character varying(20) NOT NULL,
    params jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT dc_integration_name_check CHECK ((TRIM(BOTH FROM name) <> ''::text)),
    CONSTRAINT dc_integration_type_check CHECK ((TRIM(BOTH FROM type) <> ''::text))
);



--
-- Name: domain; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.domain (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    name character varying(255) NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);


--
-- Name: query; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.query (
    query_id uuid DEFAULT gen_random_uuid() NOT NULL,
    query text NOT NULL,
    query_template_code character varying(255) NOT NULL,
    schedule character varying(255),
    name character varying(255) NOT NULL,
    parameters jsonb DEFAULT '{"sources": [], "parameters": []}'::jsonb NOT NULL,
    expected_result jsonb DEFAULT '{"type": "boolean", "value": true}'::jsonb NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'processed' CHECK (status IN ('deleted', 'updated', 'processed'))
);



--
-- Name: query_domain; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.query_domain (
    query_id uuid NOT NULL,
    domain_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);



--
-- Name: query_run; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.query_run (
    query_run_id uuid DEFAULT gen_random_uuid() NOT NULL,
    query_id uuid NOT NULL,
    query_run_status character varying(255) NOT NULL,
    result_status character varying(255),
    result boolean NOT NULL,
    error_msg text,
    return_value jsonb DEFAULT '{"value": null}'::jsonb NOT NULL,
    executed_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    executor_id uuid,
    CONSTRAINT query_run_query_run_status_check CHECK (((query_run_status)::text = ANY ((ARRAY['queued'::character varying, 'running'::character varying, 'success'::character varying, 'failed'::character varying])::text[])))
);



--
-- Таблица связей теста (Query) с подключениями и объектами для графа lineage
--

CREATE TABLE ray.query_connection_link (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    query_id uuid NOT NULL,
    connection_id uuid NOT NULL,
    source_key varchar(64) NOT NULL,
    schema_name varchar(255) NOT NULL,
    table_name varchar(255) NOT NULL,
    columns jsonb NOT NULL DEFAULT '[]',
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_query_connection_link_query ON ray.query_connection_link(query_id);
CREATE INDEX idx_query_connection_link_connection ON ray.query_connection_link(connection_id);


--
-- Таблица связей выборки (Selection) с подключениями и объектами для графа lineage
--

CREATE TABLE ray.selection_connection_link (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    selection_id uuid NOT NULL,
    connection_id uuid NOT NULL,
    source_key varchar(64) NOT NULL,
    schema_name varchar(255) NOT NULL,
    table_name varchar(255) NOT NULL,
    columns jsonb NOT NULL DEFAULT '[]'::jsonb,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_selection_connection_link_selection ON ray.selection_connection_link(selection_id);
CREATE INDEX idx_selection_connection_link_connection ON ray.selection_connection_link(connection_id);



--
-- Name: source_metadata; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.source_metadata (
    connection_id uuid NOT NULL,
    source_table_name character varying(255) NOT NULL,
    columns jsonb,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    is_active boolean DEFAULT false NOT NULL
);



--
-- Name: user; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray."user" (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    username character varying(255) NOT NULL,
    password bytea NOT NULL,
    full_name character varying(255) NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    is_superuser boolean DEFAULT false NOT NULL
);



--
-- Name: user_domain; Type: TABLE; Schema: ray; Owner: gw-user
--

CREATE TABLE ray.user_domain (
    user_id uuid NOT NULL,
    domain_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);



--
-- Name: connection_domain connection_domain_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.connection_domain
    ADD CONSTRAINT connection_domain_pkey PRIMARY KEY (connection_id, domain_id);


--
-- Name: connection connection_label_key; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.connection
    ADD CONSTRAINT connection_label_key UNIQUE (label);


--
-- Name: connection connection_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.connection
    ADD CONSTRAINT connection_pkey PRIMARY KEY (connection_id);


--
-- Name: domain domain_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.domain
    ADD CONSTRAINT domain_pkey PRIMARY KEY (id);


--
-- Name: query_domain query_domain_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.query_domain
    ADD CONSTRAINT query_domain_pkey PRIMARY KEY (query_id, domain_id);


--
-- Name: query query_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.query
    ADD CONSTRAINT query_pkey PRIMARY KEY (query_id);


--
-- Name: query_connection_link FKs; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.query_connection_link
    ADD CONSTRAINT query_connection_link_query_id_fkey FOREIGN KEY (query_id) REFERENCES ray.query(query_id) ON DELETE CASCADE;

ALTER TABLE ONLY ray.query_connection_link
    ADD CONSTRAINT query_connection_link_connection_id_fkey FOREIGN KEY (connection_id) REFERENCES ray.connection(connection_id) ON DELETE CASCADE;

--
-- Name: query_run query_run_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.query_run
    ADD CONSTRAINT query_run_pkey PRIMARY KEY (query_run_id);


--
-- Name: source_metadata source_metadata_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.source_metadata
    ADD CONSTRAINT source_metadata_pkey PRIMARY KEY (connection_id, source_table_name);

--
-- Name: source_metadata uniq_source_table_name_connection_id; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.source_metadata
    ADD CONSTRAINT uniq_source_table_name_connection_id UNIQUE (source_table_name, connection_id);

--
-- Name: user_domain user_domain_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.user_domain
    ADD CONSTRAINT user_domain_pkey PRIMARY KEY (user_id, domain_id);


--
-- Name: user user_pkey; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray."user"
    ADD CONSTRAINT user_pkey PRIMARY KEY (id);


--
-- Name: user user_username_key; Type: CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray."user"
    ADD CONSTRAINT user_username_key UNIQUE (username);


--
-- Name: connection_params; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX connection_params ON ray.connection USING gin (params);


--
-- Name: connectiondomain_connection_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX connectiondomain_connection_id ON ray.connection_domain USING btree (connection_id);


--
-- Name: connectiondomain_domain_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX connectiondomain_domain_id ON ray.connection_domain USING btree (domain_id);


--
-- Name: query_expected_result; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX query_expected_result ON ray.query USING gin (expected_result);


--
-- Name: query_parameters; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX query_parameters ON ray.query USING gin (parameters);


--
-- Name: querydomain_domain_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX querydomain_domain_id ON ray.query_domain USING btree (domain_id);


--
-- Name: querydomain_query_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX querydomain_query_id ON ray.query_domain USING btree (query_id);


--
-- Name: queryrun_query_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX queryrun_query_id ON ray.query_run USING btree (query_id);
CREATE INDEX queryrun_executor_id ON ray.query_run USING btree (executor_id);


--
-- Name: queryrun_return_value; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX queryrun_return_value ON ray.query_run USING gin (return_value);


--
-- Name: sourcemetadata_columns; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX sourcemetadata_columns ON ray.source_metadata USING gin (columns);


--
-- Name: sourcemetadata_connection_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX sourcemetadata_connection_id ON ray.source_metadata USING btree (connection_id);


--
-- Name: userdomain_domain_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX userdomain_domain_id ON ray.user_domain USING btree (domain_id);


--
-- Name: userdomain_user_id; Type: INDEX; Schema: ray; Owner: gw-user
--

CREATE INDEX userdomain_user_id ON ray.user_domain USING btree (user_id);


--
-- Name: connection_domain connection_domain_connection_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.connection_domain
    ADD CONSTRAINT connection_domain_connection_id_fkey FOREIGN KEY (connection_id) REFERENCES ray.connection(connection_id) ON DELETE CASCADE;


--
-- Name: connection_domain connection_domain_domain_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.connection_domain
    ADD CONSTRAINT connection_domain_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES ray.domain(id) ON DELETE CASCADE;


--
-- Name: query_domain query_domain_domain_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.query_domain
    ADD CONSTRAINT query_domain_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES ray.domain(id) ON DELETE CASCADE;


--
-- Name: query_domain query_domain_query_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.query_domain
    ADD CONSTRAINT query_domain_query_id_fkey FOREIGN KEY (query_id) REFERENCES ray.query(query_id) ON DELETE CASCADE;


--
-- Name: query_run query_run_query_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.query_run
    ADD CONSTRAINT query_run_query_id_fkey FOREIGN KEY (query_id) REFERENCES ray.query(query_id) ON DELETE CASCADE;

ALTER TABLE ONLY ray.query_run
    ADD CONSTRAINT query_run_executor_id_fkey FOREIGN KEY (executor_id) REFERENCES ray."user"(id) ON DELETE SET NULL;


--
-- Name: source_metadata source_metadata_connection_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.source_metadata
    ADD CONSTRAINT source_metadata_connection_id_fkey FOREIGN KEY (connection_id) REFERENCES ray.connection(connection_id) ON DELETE CASCADE;


--
-- Name: user_domain user_domain_domain_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.user_domain
    ADD CONSTRAINT user_domain_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES ray.domain(id) ON DELETE CASCADE;


--
-- Name: user_domain user_domain_user_id_fkey; Type: FK CONSTRAINT; Schema: ray; Owner: gw-user
--

ALTER TABLE ONLY ray.user_domain
    ADD CONSTRAINT user_domain_user_id_fkey FOREIGN KEY (user_id) REFERENCES ray."user"(id) ON DELETE CASCADE;


--
-- Role tables
--
CREATE TABLE IF NOT EXISTS ray.role (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    name VARCHAR NOT NULL CHECK (trim(name) != ''),
    domain_id UUID,
    permissions TEXT[] NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (domain_id) REFERENCES ray."domain"(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ray.user_role (
    user_id UUID NOT NULL,
    role_id UUID NOT NULL,
    domain_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES ray."user"(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES ray."role"(id) ON DELETE CASCADE,
    FOREIGN KEY (domain_id) REFERENCES ray."domain"(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_user_role_user_id ON ray.user_role(user_id);
CREATE INDEX IF NOT EXISTS idx_user_role_role_id ON ray.user_role(role_id);
CREATE INDEX IF NOT EXISTS idx_user_role_domain_id ON ray.user_role(domain_id);


--
-- Requirement tables
--
CREATE TABLE IF NOT EXISTS ray.requirement (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    name VARCHAR NOT NULL UNIQUE CHECK (trim(name) != ''),
    status VARCHAR NOT NULL,
    description TEXT NOT NULL CHECK (trim(description) != ''),
    data_owner_id UUID,
    data_officer_id UUID,
    data_user_id UUID,
    data_quality_expert_id UUID,
    urgency SMALLINT NOT NULL,
    incident_rule_type VARCHAR(64),
    incident_rule_params JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT chk_status_values CHECK (status IN ('DRAFT', 'REVIEW', 'SUSPENDED', 'IN_PROGRESS', 'COMPLETED', 'ARCHIVE')),
    CONSTRAINT chk_urgency_range CHECK (urgency BETWEEN 1 AND 3),
    CONSTRAINT chk_incident_rule_type_values CHECK (
        incident_rule_type IS NULL
        OR incident_rule_type IN ('ON_EXECUTION_ERROR', 'ON_NEGATIVE_RESULT')
    )
);


CREATE TABLE IF NOT EXISTS ray.selection (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    requirement_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL CHECK (trim(name) != ''),
    description TEXT NOT NULL CHECK (trim(description) != ''),
    query JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requirement_id) REFERENCES ray."requirement"(id) ON DELETE CASCADE
);

CREATE INDEX idx_selection_requirement_id ON ray.selection(requirement_id);
CREATE INDEX idx_selection_name ON ray.selection(name);

ALTER TABLE ONLY ray.selection_connection_link
    ADD CONSTRAINT selection_connection_link_selection_id_fkey FOREIGN KEY (selection_id) REFERENCES ray.selection(id) ON DELETE CASCADE;

ALTER TABLE ONLY ray.selection_connection_link
    ADD CONSTRAINT selection_connection_link_connection_id_fkey FOREIGN KEY (connection_id) REFERENCES ray.connection(connection_id) ON DELETE CASCADE;


CREATE TABLE IF NOT EXISTS ray.requirement_domain (
    requirement_id UUID NOT NULL,
    domain_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (requirement_id, domain_id),
    FOREIGN KEY (requirement_id) REFERENCES ray."requirement"(id) ON DELETE CASCADE,
    FOREIGN KEY (domain_id) REFERENCES ray."domain"(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ray.requirement_query (
    requirement_id UUID NOT NULL,
    query_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (requirement_id, query_id),
    FOREIGN KEY (requirement_id) REFERENCES ray."requirement"(id) ON DELETE CASCADE,
    FOREIGN KEY (query_id) REFERENCES ray."query"(query_id) ON DELETE CASCADE
);

--
-- Tag table
--
CREATE TABLE IF NOT EXISTS ray.tag (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    name VARCHAR(255) NOT NULL UNIQUE CHECK (trim(name) != ''),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_tag_name ON ray.tag(name);

--
-- Requirement Tag junction table
--
CREATE TABLE IF NOT EXISTS ray.requirement_tag (
    requirement_id UUID NOT NULL,
    tag_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (requirement_id, tag_id),
    FOREIGN KEY (requirement_id) REFERENCES ray."requirement"(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES ray."tag"(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_requirement_tag_requirement_id ON ray.requirement_tag(requirement_id);
CREATE INDEX IF NOT EXISTS idx_requirement_tag_tag_id ON ray.requirement_tag(tag_id);


ALTER TABLE ray.requirement
ADD CONSTRAINT fk_requirement_data_owner 
    FOREIGN KEY (data_owner_id) 
    REFERENCES ray."user"(id);

ALTER TABLE ray.requirement
ADD CONSTRAINT fk_requirement_data_officer 
    FOREIGN KEY (data_officer_id) 
    REFERENCES ray."user"(id);

ALTER TABLE ray.requirement
ADD CONSTRAINT fk_requirement_data_user 
    FOREIGN KEY (data_user_id) 
    REFERENCES ray."user"(id);

ALTER TABLE ray.requirement
ADD CONSTRAINT fk_requirement_data_quality_expert 
    FOREIGN KEY (data_quality_expert_id) 
    REFERENCES ray."user"(id);

CREATE INDEX idx_requirement_data_owner_id ON ray.requirement(data_owner_id);
CREATE INDEX idx_requirement_data_officer_id ON ray.requirement(data_officer_id);
CREATE INDEX idx_requirement_data_user_id ON ray.requirement(data_user_id);
CREATE INDEX idx_requirement_data_quality_expert_id ON ray.requirement(data_quality_expert_id);

CREATE INDEX idx_requirement_name ON ray.requirement(name);
CREATE INDEX idx_requirement_status ON ray.requirement(status);
CREATE INDEX idx_requirement_created_at ON ray.requirement(created_at);

CREATE INDEX idx_requirement_domain_requirement_id ON ray.requirement_domain(requirement_id);
CREATE INDEX idx_requirement_domain_domain_id ON ray.requirement_domain(domain_id);

CREATE INDEX idx_requirement_query_requirement_id ON ray.requirement_query(requirement_id);
CREATE INDEX idx_requirement_query_query_id ON ray.requirement_query(query_id);


--
-- Comment tables
--
CREATE TABLE IF NOT EXISTS ray."comment" (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    requirement_id UUID NOT NULL,
    user_id UUID NOT NULL,
    message TEXT NOT NULL CHECK (trim(message) != ''),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requirement_id) REFERENCES ray."requirement"(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES ray."user"(id) ON DELETE CASCADE
);

CREATE INDEX idx_comment_requirement_user ON ray."comment"(requirement_id, user_id);
-- Дополнительные индексы для оптимизации запросов
CREATE INDEX idx_comment_requirement_id ON ray."comment"(requirement_id);
CREATE INDEX idx_comment_user_id ON ray."comment"(user_id);


--
-- Incident tables
--
CREATE TABLE IF NOT EXISTS ray.incident (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    requirement_id UUID NOT NULL,
    query_id UUID,
    query_run_id UUID,
    test_started_at TIMESTAMPTZ NOT NULL,
    incident_created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data_owner_id UUID,
    data_officer_id UUID,
    data_user_id UUID,
    data_quality_expert_id UUID,
    assignee_id UUID,
    created_by_id UUID,
    status VARCHAR(32) NOT NULL CHECK (
        status IN ('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED', 'SUPPRESSED')
    ),
    severity SMALLINT NOT NULL CHECK (severity BETWEEN 1 AND 3),
    incident_type VARCHAR(64) NOT NULL,
    source VARCHAR(64),
    is_automatic BOOLEAN NOT NULL DEFAULT TRUE,
    failure_count INTEGER NOT NULL DEFAULT 1,
    title VARCHAR NOT NULL CHECK (trim(title) != ''),
    description TEXT,
    error_message TEXT,
    external_reference VARCHAR(255),
    domain_id UUID,
    resolved_at TIMESTAMPTZ,
    FOREIGN KEY (requirement_id) REFERENCES ray."requirement"(id) ON DELETE CASCADE,
    FOREIGN KEY (query_id) REFERENCES ray."query"(query_id) ON DELETE SET NULL,
    FOREIGN KEY (query_run_id) REFERENCES ray."query_run"(query_run_id) ON DELETE SET NULL,
    FOREIGN KEY (data_owner_id) REFERENCES ray."user"(id) ON DELETE SET NULL,
    FOREIGN KEY (data_officer_id) REFERENCES ray."user"(id) ON DELETE SET NULL,
    FOREIGN KEY (data_user_id) REFERENCES ray."user"(id) ON DELETE SET NULL,
    FOREIGN KEY (data_quality_expert_id) REFERENCES ray."user"(id) ON DELETE SET NULL,
    FOREIGN KEY (assignee_id) REFERENCES ray."user"(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by_id) REFERENCES ray."user"(id) ON DELETE SET NULL,
    FOREIGN KEY (domain_id) REFERENCES ray."domain"(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_incident_requirement_id ON ray.incident(requirement_id);
CREATE INDEX IF NOT EXISTS idx_incident_status ON ray.incident(status);
CREATE INDEX IF NOT EXISTS idx_incident_severity ON ray.incident(severity);
CREATE INDEX IF NOT EXISTS idx_incident_incident_created_at ON ray.incident(incident_created_at);
CREATE INDEX IF NOT EXISTS idx_incident_assignee_id ON ray.incident(assignee_id);
CREATE INDEX IF NOT EXISTS idx_incident_domain_id ON ray.incident(domain_id);


--
-- Query saved check tables
--
CREATE TABLE IF NOT EXISTS ray."query_saved_check" (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL CHECK (trim(name) != ''),
    template TEXT NOT NULL,
    author_id UUID NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES ray."user"(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_query_saved_check_author 
    ON ray."query_saved_check"(author_id);


--
-- Chart tables
--
CREATE TABLE IF NOT EXISTS ray.chart (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    domain_id UUID NOT NULL,
    chart_type VARCHAR NOT NULL CHECK (trim(chart_type) != ''),
    parameters jsonb DEFAULT '{}'::jsonb NOT NULL,
    name VARCHAR NOT NULL CHECK (trim(name) != ''),
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (domain_id) REFERENCES ray."domain"(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_chart_domain_id ON ray.chart(domain_id);
CREATE INDEX IF NOT EXISTS idx_chart_name ON ray.chart(name);


--
-- Dashboard tables
--
CREATE TABLE IF NOT EXISTS ray.dashboard (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    name VARCHAR NOT NULL CHECK (trim(name) != ''),
    description TEXT,
    is_main BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_dashboard_name ON ray.dashboard(name);
CREATE INDEX IF NOT EXISTS idx_dashboard_created_at ON ray.dashboard(created_at);
CREATE UNIQUE INDEX IF NOT EXISTS idx_dashboard_single_main ON ray.dashboard ((1)) WHERE is_main = true;

CREATE TABLE IF NOT EXISTS ray.dashboard_chart (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    dashboard_id UUID NOT NULL,
    chart_id UUID NOT NULL,
    placement INTEGER[],
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (dashboard_id) REFERENCES ray."dashboard"(id) ON DELETE CASCADE,
    FOREIGN KEY (chart_id) REFERENCES ray."chart"(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_dashboard_chart_dashboard_id ON ray.dashboard_chart(dashboard_id);
CREATE INDEX IF NOT EXISTS idx_dashboard_chart_chart_id ON ray.dashboard_chart(chart_id);


--
-- Reports schema and tables
--

CREATE SCHEMA IF NOT EXISTS reports;

--
-- Таблица reports.last_update
--
CREATE TABLE IF NOT EXISTS reports.last_update (
    process_name VARCHAR(50) PRIMARY KEY,
    last_run_timestamp TIMESTAMP NOT NULL
);

--
-- Таблица reports.dim_date
--
CREATE TABLE IF NOT EXISTS reports.dim_date (
    date_key serial PRIMARY KEY,
    full_date date NOT NULL,
    full_datetime timestamp NOT NULL UNIQUE,
    day integer NOT NULL,
    month integer NOT NULL,
    year integer NOT NULL,
    quarter integer NOT NULL,
    week integer NOT NULL,
    day_of_week integer NOT NULL,
    day_name VARCHAR(20) NOT NULL,
    month_name VARCHAR(20) NOT NULL,
    is_weekend boolean NOT NULL,
    is_holiday boolean DEFAULT false NOT NULL,
    hour_24 integer NOT NULL,
    hour_12 integer NOT NULL,
    hour_minute VARCHAR(5) NOT NULL,
    time_period VARCHAR(20) NOT NULL,
    is_business_hours boolean DEFAULT false NOT NULL
);

-- Индексы для календарной таблицы
CREATE INDEX IF NOT EXISTS idx_dim_date_full_date ON reports.dim_date(full_date);
CREATE INDEX IF NOT EXISTS idx_dim_date_full_datetime ON reports.dim_date(full_datetime);
CREATE INDEX IF NOT EXISTS idx_dim_date_year_month ON reports.dim_date(year, month);
CREATE INDEX IF NOT EXISTS idx_dim_date_hour ON reports.dim_date(hour_24);
CREATE INDEX IF NOT EXISTS idx_dim_date_business_hours ON reports.dim_date(is_business_hours);

--
-- Первичное заполнение таблицы reports.dim_date
-- Генерация временных интервалов по часам: от now() - 2 года до now() + 8 лет (итого 10 лет диапазона)
--
WITH params AS (
    SELECT
        TIMESTAMP '2024-01-01 00:00:00' AS start_date,
        TIMESTAMP '2036-01-01 00:00:00' AS end_date
),
date_series AS (
    SELECT
        GENERATE_SERIES(start_date, end_date, INTERVAL '1 hour')::timestamp AS full_datetime
    FROM params
)
INSERT INTO reports.dim_date (
    full_date,
    full_datetime,
    day,
    month,
    year,
    quarter,
    week,
    day_of_week,
    day_name,
    month_name,
    is_weekend,
    is_holiday,
    hour_24,
    hour_12,
    hour_minute,
    time_period,
    is_business_hours
)
SELECT
    ds.full_datetime::date AS full_date,
    ds.full_datetime,
    EXTRACT(DAY FROM ds.full_datetime)::int,
    EXTRACT(MONTH FROM ds.full_datetime)::int,
    EXTRACT(YEAR FROM ds.full_datetime)::int,
    EXTRACT(QUARTER FROM ds.full_datetime)::int,
    EXTRACT(WEEK FROM ds.full_datetime)::int,
    EXTRACT(ISODOW FROM ds.full_datetime)::int,
    TO_CHAR(ds.full_datetime, 'FMDay'),
    TO_CHAR(ds.full_datetime, 'FMMONTH'),
    (EXTRACT(ISODOW FROM ds.full_datetime)::int IN (6, 7)) AS is_weekend,
    false,
    EXTRACT(HOUR FROM ds.full_datetime)::int AS hour_24,
    CASE
        WHEN EXTRACT(HOUR FROM ds.full_datetime)::int = 0 THEN 12
        WHEN EXTRACT(HOUR FROM ds.full_datetime)::int <= 12
            THEN EXTRACT(HOUR FROM ds.full_datetime)::int
        ELSE EXTRACT(HOUR FROM ds.full_datetime)::int - 12
    END AS hour_12,
    TO_CHAR(ds.full_datetime, 'HH24:MI') AS hour_minute,
    CASE
        WHEN EXTRACT(HOUR FROM ds.full_datetime)::int >= 6
             AND EXTRACT(HOUR FROM ds.full_datetime)::int < 19 THEN 'Day'
        ELSE 'Night'
    END AS time_period,
    false AS is_business_hours
FROM date_series ds
ON CONFLICT (full_datetime) DO NOTHING;

--
-- Таблица reports.dim_user
--
CREATE TABLE IF NOT EXISTS reports.dim_user (
    user_key serial PRIMARY KEY,
    user_id uuid NOT NULL,
    username VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    is_superuser boolean NOT NULL,
    start_date date NOT NULL,
    end_date date,
    is_current boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_at_date_key integer NOT NULL,
    updated_at_date_key integer NOT NULL,
    CONSTRAINT fk_dim_user_created_at_date FOREIGN KEY (created_at_date_key)
        REFERENCES reports.dim_date(date_key),
    CONSTRAINT fk_dim_user_updated_at_date FOREIGN KEY (updated_at_date_key)
        REFERENCES reports.dim_date(date_key)
);

-- Индексы для оптимизации запросов
CREATE INDEX IF NOT EXISTS idx_dim_user_user_id ON reports.dim_user(user_id);
CREATE INDEX IF NOT EXISTS idx_dim_user_username ON reports.dim_user(username);
CREATE INDEX IF NOT EXISTS idx_dim_user_current ON reports.dim_user(is_current);
CREATE INDEX IF NOT EXISTS idx_dim_user_dates ON reports.dim_user(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_dim_user_created_at_date_key ON reports.dim_user(created_at_date_key);
CREATE INDEX IF NOT EXISTS idx_dim_user_updated_at_date_key ON reports.dim_user(updated_at_date_key);

--
-- Таблица reports.dim_domain
--
CREATE TABLE IF NOT EXISTS reports.dim_domain (
    domain_key serial PRIMARY KEY,
    domain_id uuid NOT NULL,
    name VARCHAR(255) NOT NULL,
    start_date date NOT NULL,
    end_date date,
    is_current boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_at_date_key integer NOT NULL,
    updated_at_date_key integer NOT NULL,
    CONSTRAINT fk_dim_domain_created_at_date FOREIGN KEY (created_at_date_key)
        REFERENCES reports.dim_date(date_key),
    CONSTRAINT fk_dim_domain_updated_at_date FOREIGN KEY (updated_at_date_key)
        REFERENCES reports.dim_date(date_key)
);

-- Индексы для оптимизации запросов
CREATE INDEX IF NOT EXISTS idx_dim_domain_domain_id ON reports.dim_domain(domain_id);
CREATE INDEX IF NOT EXISTS idx_dim_domain_name ON reports.dim_domain(name);
CREATE INDEX IF NOT EXISTS idx_dim_domain_current ON reports.dim_domain(is_current);
CREATE INDEX IF NOT EXISTS idx_dim_domain_dates ON reports.dim_domain(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_dim_domain_created_at_date_key ON reports.dim_domain(created_at_date_key);
CREATE INDEX IF NOT EXISTS idx_dim_domain_updated_at_date_key ON reports.dim_domain(updated_at_date_key);

--
-- Таблица reports.dim_connection
--
CREATE TABLE IF NOT EXISTS reports.dim_connection (
    connection_key serial PRIMARY KEY,
    connection_id uuid NOT NULL,
    connection_type VARCHAR(255) NOT NULL,
    label VARCHAR(255) NOT NULL,
    description text NOT NULL,
    params jsonb NOT NULL,
    update_metadata_schedule text,
    status VARCHAR(32) NOT NULL DEFAULT 'processed' CHECK (status IN ('deleted', 'updated', 'processed')),
    start_date date NOT NULL,
    end_date date,
    is_current boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_at_date_key integer NOT NULL,
    updated_at_date_key integer NOT NULL,
    CONSTRAINT fk_dim_connection_created_at_date FOREIGN KEY (created_at_date_key)
        REFERENCES reports.dim_date(date_key),
    CONSTRAINT fk_dim_connection_updated_at_date FOREIGN KEY (updated_at_date_key)
        REFERENCES reports.dim_date(date_key)
);

-- Индексы для оптимизации запросов
CREATE INDEX IF NOT EXISTS idx_dim_connection_connection_id ON reports.dim_connection(connection_id);
CREATE INDEX IF NOT EXISTS idx_dim_connection_connection_type ON reports.dim_connection(connection_type);
CREATE INDEX IF NOT EXISTS idx_dim_connection_label ON reports.dim_connection(label);
CREATE INDEX IF NOT EXISTS idx_dim_connection_status ON reports.dim_connection(status);
CREATE INDEX IF NOT EXISTS idx_dim_connection_current ON reports.dim_connection(is_current);
CREATE INDEX IF NOT EXISTS idx_dim_connection_dates ON reports.dim_connection(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_dim_connection_params ON reports.dim_connection USING gin(params);
CREATE INDEX IF NOT EXISTS idx_dim_connection_created_at_date_key ON reports.dim_connection(created_at_date_key);
CREATE INDEX IF NOT EXISTS idx_dim_connection_updated_at_date_key ON reports.dim_connection(updated_at_date_key);

--
-- Таблица reports.dim_requirement
--
CREATE TABLE IF NOT EXISTS reports.dim_requirement (
    requirement_key serial PRIMARY KEY,
    requirement_id uuid NOT NULL,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(32) NOT NULL CHECK (
        status IN ('DRAFT', 'REVIEW', 'SUSPENDED', 'IN_PROGRESS', 'COMPLETED', 'ARCHIVE')
    ),
    description text NOT NULL,
    urgency smallint NOT NULL CHECK (urgency BETWEEN 1 AND 3),

    -- Суррогатные ключи для измерений
    data_owner_key integer,
    data_officer_key integer,
    data_user_key integer,
    data_quality_expert_key integer,
    created_date_key integer,

    -- Атрибуты изменений (Type 2 SCD)
    start_date date NOT NULL,
    end_date date,
    is_current boolean DEFAULT true NOT NULL,

    -- Технические поля
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_at_date_key integer NOT NULL,
    updated_at_date_key integer NOT NULL,

    -- Ограничения внешних ключей
    CONSTRAINT fk_dim_requirement_data_owner FOREIGN KEY (data_owner_key)
        REFERENCES reports.dim_user(user_key),
    CONSTRAINT fk_dim_requirement_data_officer FOREIGN KEY (data_officer_key)
        REFERENCES reports.dim_user(user_key),
    CONSTRAINT fk_dim_requirement_data_user FOREIGN KEY (data_user_key)
        REFERENCES reports.dim_user(user_key),
    CONSTRAINT fk_dim_requirement_data_quality_expert FOREIGN KEY (data_quality_expert_key)
        REFERENCES reports.dim_user(user_key),
    CONSTRAINT fk_dim_requirement_created_date FOREIGN KEY (created_date_key)
        REFERENCES reports.dim_date(date_key),
    CONSTRAINT fk_dim_requirement_created_at_date FOREIGN KEY (created_at_date_key)
        REFERENCES reports.dim_date(date_key),
    CONSTRAINT fk_dim_requirement_updated_at_date FOREIGN KEY (updated_at_date_key)
        REFERENCES reports.dim_date(date_key)
);

-- Индексы для оптимизации запросов
CREATE INDEX IF NOT EXISTS idx_dim_requirement_requirement_id ON reports.dim_requirement(requirement_id);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_name ON reports.dim_requirement(name);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_status ON reports.dim_requirement(status);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_urgency ON reports.dim_requirement(urgency);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_current ON reports.dim_requirement(is_current);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_dates ON reports.dim_requirement(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_created_date ON reports.dim_requirement(created_date_key);

-- Индексы для внешних ключей
CREATE INDEX IF NOT EXISTS idx_dim_requirement_data_owner ON reports.dim_requirement(data_owner_key);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_data_officer ON reports.dim_requirement(data_officer_key);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_data_user ON reports.dim_requirement(data_user_key);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_data_quality_expert
    ON reports.dim_requirement(data_quality_expert_key);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_created_at_date_key ON reports.dim_requirement(created_at_date_key);
CREATE INDEX IF NOT EXISTS idx_dim_requirement_updated_at_date_key ON reports.dim_requirement(updated_at_date_key);

-- Уникальный индекс для обеспечения целостности текущих версий
CREATE UNIQUE INDEX IF NOT EXISTS idx_dim_requirement_current_unique
ON reports.dim_requirement(requirement_id)
WHERE is_current = true;

--
-- Таблица reports.dim_query
--
CREATE TABLE IF NOT EXISTS reports.dim_query (
    query_key serial PRIMARY KEY,
    query_id uuid NOT NULL,
    query text NOT NULL,
    query_template_code VARCHAR(255) NOT NULL,
    schedule VARCHAR(255),
    name VARCHAR(255) NOT NULL,
    parameters jsonb DEFAULT '{"sources": [], "parameters": []}'::jsonb NOT NULL,
    expected_result jsonb DEFAULT '{"type": "boolean", "value": true}'::jsonb NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'processed' CHECK (status IN ('deleted', 'updated', 'processed')),
    start_date date NOT NULL,
    end_date date,
    is_current boolean DEFAULT true NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_at_date_key integer NOT NULL,
    updated_at_date_key integer NOT NULL,
    CONSTRAINT fk_dim_query_created_at_date FOREIGN KEY (created_at_date_key)
        REFERENCES reports.dim_date(date_key),
    CONSTRAINT fk_dim_query_updated_at_date FOREIGN KEY (updated_at_date_key)
        REFERENCES reports.dim_date(date_key)
);

-- Индексы для оптимизации запросов
CREATE INDEX IF NOT EXISTS idx_dim_query_query_id ON reports.dim_query(query_id);
CREATE INDEX IF NOT EXISTS idx_dim_query_query_template_code ON reports.dim_query(query_template_code);
CREATE INDEX IF NOT EXISTS idx_dim_query_name ON reports.dim_query(name);
CREATE INDEX IF NOT EXISTS idx_dim_query_status ON reports.dim_query(status);
CREATE INDEX IF NOT EXISTS idx_dim_query_current ON reports.dim_query(is_current);
CREATE INDEX IF NOT EXISTS idx_dim_query_dates ON reports.dim_query(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_dim_query_parameters ON reports.dim_query USING gin(parameters);
CREATE INDEX IF NOT EXISTS idx_dim_query_expected_result ON reports.dim_query USING gin(expected_result);
CREATE INDEX IF NOT EXISTS idx_dim_query_created_at ON reports.dim_query(created_at);
CREATE INDEX IF NOT EXISTS idx_dim_query_created_at_date_key ON reports.dim_query(created_at_date_key);
CREATE INDEX IF NOT EXISTS idx_dim_query_updated_at_date_key ON reports.dim_query(updated_at_date_key);

-- Комментарии к таблице и колонкам
COMMENT ON TABLE reports.dim_query IS 'Измерение запросов для системы отчетности';
COMMENT ON COLUMN reports.dim_query.query_key IS 'Суррогатный ключ запроса';
COMMENT ON COLUMN reports.dim_query.query_id IS 'Идентификатор запроса из исходной системы';
COMMENT ON COLUMN reports.dim_query.query IS 'Текст SQL запроса';
COMMENT ON COLUMN reports.dim_query.query_template_code IS 'Код шаблона запроса';
COMMENT ON COLUMN reports.dim_query.schedule IS 'Расписание выполнения запроса';
COMMENT ON COLUMN reports.dim_query.name IS 'Наименование запроса';
COMMENT ON COLUMN reports.dim_query.parameters IS 'Параметры запроса в формате JSON';
COMMENT ON COLUMN reports.dim_query.expected_result IS 'Ожидаемый результат выполнения';
COMMENT ON COLUMN reports.dim_query.status IS 'Статус запроса';
COMMENT ON COLUMN reports.dim_query.start_date IS 'Дата начала действия версии';
COMMENT ON COLUMN reports.dim_query.end_date IS 'Дата окончания действия версии';
COMMENT ON COLUMN reports.dim_query.is_current IS 'Признак текущей версии';
COMMENT ON COLUMN reports.dim_query.created_at IS 'Метка времени создания записи';
COMMENT ON COLUMN reports.dim_query.updated_at IS 'Метка времени обновления записи';

--
-- Таблица reports.query_run
--
CREATE TABLE IF NOT EXISTS reports.query_run (
    query_run_id uuid PRIMARY KEY,
    query_key integer NOT NULL,
    executed_date_key integer NOT NULL,
    query_run_status VARCHAR(255) NOT NULL CHECK (
        query_run_status IN ('queued', 'running', 'success', 'failed')
    ),
    result_status VARCHAR(255),
    result boolean NOT NULL,
    error_msg text,
    return_value jsonb DEFAULT '{"value": null}'::jsonb NOT NULL,
    executed_at timestamp with time zone NOT NULL,
    original_query_id uuid NOT NULL,
    CONSTRAINT fk_query_run_query FOREIGN KEY (query_key)
        REFERENCES reports.dim_query(query_key),
    CONSTRAINT fk_query_run_executed_date FOREIGN KEY (executed_date_key)
        REFERENCES reports.dim_date(date_key)
);

-- Индексы для оптимизации запросов
CREATE INDEX IF NOT EXISTS idx_query_run_query_key ON reports.query_run(query_key);
CREATE INDEX IF NOT EXISTS idx_query_run_executed_date_key ON reports.query_run(executed_date_key);
CREATE INDEX IF NOT EXISTS idx_query_run_query_run_status ON reports.query_run(query_run_status);
CREATE INDEX IF NOT EXISTS idx_query_run_result_status ON reports.query_run(result_status);
CREATE INDEX IF NOT EXISTS idx_query_run_result ON reports.query_run(result);
CREATE INDEX IF NOT EXISTS idx_query_run_executed_at ON reports.query_run(executed_at);
CREATE INDEX IF NOT EXISTS idx_query_run_original_query_id ON reports.query_run(original_query_id);


--
-- PostgreSQL database dump complete
--

