create schema diku_mod_source_record_storage_new3;

alter schema diku_mod_source_record_storage_new3 owner to folio_admin;

set search_path = "diku_mod_source_record_storage_new3", "public";

create type job_execution_status as
    enum ('PARENT',
        'NEW',
        'FILE_UPLOADED',
        'PARSING_IN_PROGRESS',
        'PARSING_FINISHED',
        'PROCESSING_IN_PROGRESS',
        'PROCESSING_FINISHED',
        'COMMIT_IN_PROGRESS',
        'COMMITTED',
        'ERROR',
        'DISCARDED');

create type record_type as enum ('MARC');

create type record_state_type as enum ('ACTUAL', 'DRAFT', 'OLD', 'DELETED');


create table snapshots
(
    id                      uuid                 not null
        constraint pk_snapshots
            primary key,
    status                  job_execution_status not null,
    processing_started_date timestamp            not null
);

create table records
(
    id                uuid              not null
        constraint pk_records
            primary key,
    snapshotid        uuid              not null
        constraint fk_records_snapshots
            references snapshots,
    matchedprofileid  uuid              not null,
    matchedid         uuid              not null,
    generation        integer           not null,
    recordtype        record_type       not null,
    instanceid        uuid,
    state             record_state_type not null,
    orderinfile       integer,
    suppressdiscovery boolean default false,
    createdbyuserid   uuid,
    createddate       timestamp,
    updatedbyuserid   uuid,
    updateddate       timestamp
);

create index idx_records_snapshotid
    on records (snapshotid);

create index idx_records_instanceid
    on records (instanceid);

create unique index idx_records_matchedid_gen
    on records (matchedid, generation);

create index idx_records_updateddate
    on records (updateddate);

create index idx_records_matchedid_state
    on records (matchedid, state);

create table raw_records
(
    id      uuid not null
        constraint pk_raw_record
            primary key
        constraint fk_raw_records_records
            references records,
    content text
);

create table marc_records
(
    id      uuid not null
        constraint pk_marc_records
            primary key
        constraint fk_marc_records_records
            references records,
    content jsonb
);

create table error_records
(
    id          uuid not null
        constraint pk_error_records
            primary key
        constraint fk_error_records_records
            references records,
    content     jsonb,
    description varchar(1024)
);

-- create view v_source_records_all(id, updateddate, jsonb) as
-- SELECT r.matchedid                                                        AS id,
--        r.updateddate,
--        json_build_object('id', r.matchedid, 'content', mr.content)::jsonb AS jsonb
-- FROM records r
--          JOIN marc_records mr ON r.id = mr.id
-- WHERE r.state = 'ACTUAL'::record_state_type;
--
-- alter table v_source_records_all
--     owner to folio_admin;

create function update_records_set_updated_date() returns trigger
    language plpgsql
as
$$
BEGIN
    NEW.updateddate := current_timestamp;
    RETURN NEW;
END;
$$;

create trigger update_records_set_updated_date
    before insert or update
    on records
    for each row
execute procedure update_records_set_updated_date();

create function insert_records_set_created_date() returns trigger
    language plpgsql
as
$$
BEGIN
    NEW.createddate := current_timestamp;
    NEW.updateddate := NEW.createddate;
    RETURN NEW;
END;
$$;

create trigger insert_records_set_created_date
    before insert or update
    on records
    for each row
execute procedure insert_records_set_created_date();

create function get_source_marc_record_by_id(p_id uuid)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (select rds.matchedid, rds.id
      from records rds
      where rds.matchedid = p_id
        and rds.state = 'ACTUAL') r
         inner join marc_records mr on r.id = mr.id;
$$;

create function get_source_marc_record_by_id_alt(p_id uuid)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (
         select rr.matchedid, rr.id
         from (
                  select id, matchedid, generation, max(generation) over (partition by matchedId) max_generation
                  from records rds
                  where rds.matchedid = p_id) rr
         where generation = max_generation) r
         inner join marc_records mr on r.id = mr.id;
$$;

create function get_source_marc_record_by_instance_id(p_instanceid uuid)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (select rds.matchedid, rds.id
      from records rds
      where rds.instanceId = p_instanceId
        and rds.state = 'ACTUAL') r
         inner join marc_records mr on r.id = mr.id;
$$;

create function get_source_marc_record_by_instance_id_alt(p_instanceid uuid)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (
         select rr.matchedid, rr.id
         from (
                  select id, matchedid, generation, max(generation) over (partition by matchedId) max_generation
                  from records rds
                  where rds.instanceid = p_instanceId) rr
         where generation = max_generation) r
         inner join marc_records mr on r.id = mr.id;
$$;

create function get_all_source_marc_records(p_offset integer, p_limit integer)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (select matchedid, id
      from (select id, matchedid
            from records
            where state = 'ACTUAL'
            order by matchedid) rr
          offset p_offset
      limit p_limit) r
         inner join marc_records mr on r.id = mr.id;
$$;

create function get_all_source_marc_records_alt(p_offset integer, p_limit integer)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (
         select matchedid, id
         from (
                  select id, matchedid, generation, max(generation) over (partition by matchedId) max_generation
                  from records
                  order by matchedid) rr
         where generation = max_generation
             offset p_offset
         limit p_limit) r
         inner join marc_records mr on r.id = mr.id;
$$;

create function get_source_marc_records_for_period(p_from timestamp without time zone, p_till timestamp without time zone, p_offset integer,
                                                   p_limit integer)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (
         select matchedid, id
         from records
         where updateddate between p_from and p_till
           and state = 'ACTUAL'
         order by updateddate
             offset p_offset
         limit p_limit) r
         inner join marc_records mr on r.id = mr.id;
$$;

create function get_source_marc_records_for_period_alt(p_from timestamp without time zone, p_till timestamp without time zone, p_offset integer,
                                                       p_limit integer)
    returns TABLE
            (
                id    uuid,
                jsonb jsonb
            )
    language sql
as
$$
select r.matchedId id, json_build_object('id', r.matchedid, 'content', mr.content)::jsonb jsonb
from (
         select matchedid, id
         from (
                  select id, matchedid, generation, max(generation) over (partition by matchedId) max_generation
                  from records
                  where updateddate between p_from and p_till
                  order by updateddate) r
         where generation = max_generation
             offset p_offset
         limit p_limit) r
         inner join marc_records mr on r.id = mr.id;
$$;