medreport_mrb2b/supabase/migrations/20250602164242_create_medipost_tables.sql

create table "public"."analyses" (
    "id" bigint generated by default as identity not null,
    "analysis_id_oid" text not null,
    "analysis_id_original" text not null,
    "tehik_short_loinc" text,
    "tehik_loinc_name" text,
    "analysis_name_lab" text,
    "order" smallint,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone default now(),
    "parent_analysis_element_id" bigint not null
);


alter table "public"."analyses" enable row level security;

create table "public"."analysis_elements" (
    "id" bigint generated by default as identity not null,
    "analysis_id_oid" text,
    "analysis_id_original" text not null,
    "tehik_short_loinc" text,
    "tehik_loinc_name" text,
    "analysis_name_lab" text,
    "order" smallint,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone default now(),
    "parent_analysis_group_id" bigint not null,
    "material_groups" jsonb[]
);


alter table "public"."analysis_elements" enable row level security;

create table "public"."analysis_groups" (
    "id" bigint generated by default as identity not null,
    "original_id" text not null,
    "name" text,
    "order" smallint,
    "created_at" timestamp with time zone not null default now(),
    "updated_at" timestamp with time zone default now()
);


alter table "public"."analysis_groups" enable row level security;

create table "public"."codes" (
    "id" bigint generated by default as identity not null,
    "hk_code" text not null,
    "hk_code_multiplier" bigint not null,
    "coefficient" double precision not null,
    "price" double precision not null,
    "analysis_group_id" bigint,
    "analysis_element_id" bigint,
    "analysis_id" bigint,
    "updated_at" timestamp with time zone default now(),
    "created_at" timestamp with time zone not null default now()
);


alter table "public"."codes" enable row level security;

CREATE UNIQUE INDEX analysis_elements_pkey ON public.analysis_elements USING btree (id);

CREATE UNIQUE INDEX analysis_elements_original_id_key ON public.analysis_elements USING btree (analysis_id_original);

CREATE UNIQUE INDEX analysis_group_original_id_key ON public.analysis_groups USING btree (original_id);

CREATE UNIQUE INDEX analysis_group_pkey ON public.analysis_groups USING btree (id);

CREATE UNIQUE INDEX analysis_pkey ON public.analyses USING btree (id);

CREATE UNIQUE INDEX analysis_original_id_key ON public.analysis_elements USING btree (analysis_id_original);

CREATE UNIQUE INDEX codes_pkey ON public.codes USING btree (id);

CREATE UNIQUE INDEX analyses_analysis_id_original_key ON public.analyses USING btree (analysis_id_original);

CREATE UNIQUE INDEX analysis_elements_analysis_id_original_key ON public.analysis_elements USING btree (analysis_id_original);

alter table "public"."analyses" add constraint "analyses_analysis_id_original_key" UNIQUE using index "analyses_analysis_id_original_key";

alter table "public"."analysis_elements" add constraint "analysis_elements_analysis_id_original_key" UNIQUE using index "analysis_elements_analysis_id_original_key";

alter table "public"."analyses" add constraint "analysis_pkey" PRIMARY KEY using index "analysis_pkey";

alter table "public"."analysis_elements" add constraint "analysis_elements_pkey" PRIMARY KEY using index "analysis_elements_pkey";

alter table "public"."analysis_groups" add constraint "analysis_group_pkey" PRIMARY KEY using index "analysis_group_pkey";

alter table "public"."codes" add constraint "codes_pkey" PRIMARY KEY using index "codes_pkey";

alter table "public"."analyses" add constraint "analyses_parent_analysis_element_id_fkey" FOREIGN KEY (parent_analysis_element_id) REFERENCES analysis_elements(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;

alter table "public"."analyses" validate constraint "analyses_parent_analysis_element_id_fkey";

alter table "public"."analysis_elements" add constraint "analysis_elements_parent_analysis_group_id_fkey" FOREIGN KEY (parent_analysis_group_id) REFERENCES analysis_groups(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;

alter table "public"."analysis_elements" validate constraint "analysis_elements_parent_analysis_group_id_fkey";

alter table "public"."analysis_groups" add constraint "analysis_group_original_id_key" UNIQUE using index "analysis_group_original_id_key";

alter table "public"."codes" add constraint "codes_analysis_element_id_fkey" FOREIGN KEY (analysis_element_id) REFERENCES analysis_elements(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;

alter table "public"."codes" validate constraint "codes_analysis_element_id_fkey";

alter table "public"."codes" add constraint "codes_analysis_group_id_fkey" FOREIGN KEY (analysis_group_id) REFERENCES analysis_groups(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;

alter table "public"."codes" validate constraint "codes_analysis_group_id_fkey";

alter table "public"."codes" add constraint "codes_analysis_id_fkey" FOREIGN KEY (analysis_id) REFERENCES analyses(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;

alter table "public"."codes" validate constraint "codes_analysis_id_fkey";

grant delete on table "public"."analyses" to "service_role";

grant insert on table "public"."analyses" to "service_role";

grant references on table "public"."analyses" to "service_role";

grant select on table "public"."analyses" to "service_role";

grant trigger on table "public"."analyses" to "service_role";

grant truncate on table "public"."analyses" to "service_role";

grant update on table "public"."analyses" to "service_role";

grant delete on table "public"."analysis_elements" to "service_role";

grant insert on table "public"."analysis_elements" to "service_role";

grant references on table "public"."analysis_elements" to "service_role";

grant select on table "public"."analysis_elements" to "service_role";

grant trigger on table "public"."analysis_elements" to "service_role";

grant truncate on table "public"."analysis_elements" to "service_role";

grant update on table "public"."analysis_elements" to "service_role";

grant delete on table "public"."analysis_groups" to "service_role";

grant insert on table "public"."analysis_groups" to "service_role";

grant references on table "public"."analysis_groups" to "service_role";

grant select on table "public"."analysis_groups" to "service_role";

grant trigger on table "public"."analysis_groups" to "service_role";

grant truncate on table "public"."analysis_groups" to "service_role";

grant update on table "public"."analysis_groups" to "service_role";

grant delete on table "public"."codes" to "service_role";

grant insert on table "public"."codes" to "service_role";

grant references on table "public"."codes" to "service_role";

grant select on table "public"."codes" to "service_role";

grant trigger on table "public"."codes" to "service_role";

grant truncate on table "public"."codes" to "service_role";

grant update on table "public"."codes" to "service_role";

create policy "analysis_all"
on "public"."analyses"
as permissive
for all
to service_role
using (true);


create policy "analysis_elements_all"
on "public"."analysis_elements"
as permissive
for all
to service_role
using (true);


create policy "analysis_groups_all"
on "public"."analysis_groups"
as permissive
for all
to service_role
using (true);


create policy "codes_all"
on "public"."codes"
as permissive
for all
to service_role
using (true);


CREATE TRIGGER analysis_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON public.analyses FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamps();
CREATE TRIGGER analysis_elements_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON public.analysis_elements FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamps();
CREATE TRIGGER analysis_groups_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON public.analysis_groups FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamps();
CREATE TRIGGER codes_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON public.codes FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamps();