DatabaseCreation: ddl.sql

CREATE TABLE Status_Types (
    status_id BIGSERIAL PRIMARY KEY,
    status_name varchar(50) NOT NULL,
    type varchar(50) NOT NULL,
    CONSTRAINT uniques_status_per_type UNIQUE (status_name,type)
);

CREATE TABLE Categories (
    category_id BIGSERIAL PRIMARY KEY,
    name varchar(100) NOT NULL UNIQUE,
    description_category text
);

CREATE TABLE Site_Types (
    site_type_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE Regions (
    region_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE Protection_Status (
    protection_status_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE Roles (
    role_id BIGSERIAL PRIMARY KEY,
    role_name varchar(20) NOT NULL UNIQUE
);

CREATE TABLE Materials (
    material_id BIGSERIAL PRIMARY KEY,
    name varchar(50) NOT NULL UNIQUE,
    description_materials text
);

CREATE TABLE Municipalities (
    municipality_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Sites (
    site_id BIGSERIAL PRIMARY KEY,
    site_name varchar(100) NOT NULL,
    site_type_id BIGINT NOT NULL,
    region_id BIGINT NOT NULL,
    protection_status_id BIGINT NOT NULL,
    latitude decimal(9, 6) NOT NULL,
    longitude decimal(9, 6) NOT NULL,
    altitude decimal(7, 2),
    discovery_year int NOT NULL,
    description text,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active boolean DEFAULT true,
    municipality_id BIGINT,
    CONSTRAINT check_macedonia_coords
        CHECK (latitude BETWEEN 40.0 AND 43.0 AND longitude BETWEEN 20.0 AND 24.0),
    CONSTRAINT fk_site_type
        FOREIGN KEY (site_type_id)
        REFERENCES Site_Types(site_type_id),
    CONSTRAINT fk_region
        FOREIGN KEY (region_id)
        REFERENCES Regions(region_id),
    CONSTRAINT fk_protection
        FOREIGN KEY (protection_status_id)
        REFERENCES Protection_Status(protection_status_id),
    CONSTRAINT fk_site_municipality
        FOREIGN KEY (municipality_id)
        REFERENCES Municipalities(municipality_id),
    CONSTRAINT unique_site_name_region UNIQUE (site_name,region_id)
);

CREATE TABLE Culture (
    culture_id BIGSERIAL PRIMARY KEY,
    name varchar(100) NOT NULL UNIQUE,
    origin_region_id BIGINT NOT NULL,
    description text,
    period_from_year int,
    period_to_year int,
    CHECK (
    period_from_year IS NOT NULL AND
    (period_to_year IS NULL OR period_to_year >= period_from_year)),
    CONSTRAINT fk_culture_region FOREIGN KEY ( origin_region_id)
        REFERENCES Regions(region_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Institutions (
    institution_id BIGSERIAL PRIMARY KEY,
    name varchar(100) NOT NULL UNIQUE,
    address text,
    city varchar(50)
);

CREATE TABLE Users (
    user_id BIGSERIAL PRIMARY KEY,
    username varchar(30) NOT NULL UNIQUE,
    full_name varchar(50) NOT NULL,
    email varchar(100) NOT NULL UNIQUE
        CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    password_hash varchar(255) NOT NULL,
    role_id BIGINT NOT NULL,
    CONSTRAINT fk_user_role FOREIGN KEY (role_id)
        REFERENCES Roles(role_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE User_Details (
    user_id BIGINT PRIMARY KEY,
    institution_id BIGINT,
    specialization VARCHAR(100),

    CONSTRAINT fk_ud_user FOREIGN KEY (user_id)
        REFERENCES Users(user_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_ud_inst FOREIGN KEY (institution_id)
        REFERENCES Institutions(institution_id)
        ON DELETE SET NULL
);

CREATE TABLE Exhibitions (
    exhibition_id BIGSERIAL PRIMARY KEY,
    start_date date NOT NULL,
    end_date date,
    name varchar(100) NOT NULL,
    location_institution_id BIGINT,
    CONSTRAINT fk_exh_objex FOREIGN KEY (location_institution_id)
        REFERENCES Institutions(institution_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT chk_exhibition_dates
        CHECK (end_date IS NULL OR end_date >= start_date)
);

CREATE TABLE Authors (
    author_id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL
);

CREATE TABLE Publications (
    publication_id BIGSERIAL PRIMARY KEY,
    request_date date NOT NULL,
    main_author_id BIGINT,
    title varchar(255) NOT NULL,
    status_id BIGINT NOT NULL,
    publisher_location varchar(50),
    CONSTRAINT fk_pub_status FOREIGN KEY (status_id)
        REFERENCES Status_Types(status_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_pub_main_author FOREIGN KEY (main_author_id)
        REFERENCES Authors(author_id)
        ON DELETE SET NULL ON UPDATE cascade
);


CREATE TABLE Publication_Authors (
    publication_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,

    PRIMARY KEY (publication_id, author_id),

    CONSTRAINT fk_pa_pub FOREIGN KEY (publication_id)
        REFERENCES Publications(publication_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_pa_author FOREIGN KEY (author_id)
        REFERENCES Authors(author_id)
        ON DELETE CASCADE
);
CREATE TABLE Objects (
    object_id BIGSERIAL PRIMARY KEY,
    inventory_number varchar(50) UNIQUE NOT NULL,
    title varchar(150) NOT NULL,
    creation_year INT,
    acquisition_date date,
    description text,
    current_status_id BIGINT NOT NULL,
    site_id BIGINT NOT NULL,
    found_by_user_id BIGINT,
    CONSTRAINT fk_obj_site FOREIGN KEY (site_id)
        REFERENCES Sites(site_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_obj_user FOREIGN KEY (found_by_user_id)
        REFERENCES Users(user_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT chk_creation_year
       CHECK (creation_year IS NULL OR creation_year <= EXTRACT(YEAR FROM CURRENT_DATE)),
       CONSTRAINT fk_obj_status FOREIGN KEY (current_status_id)
        REFERENCES Status_Types(status_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Treatments (
    treatment_id BIGSERIAL PRIMARY KEY,
    object_id BIGINT NOT null,
    treatment_date DATE,
    description TEXT,
    CONSTRAINT fk_treatment_obj
        FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Conservation_Projects (
    project_id BIGSERIAL,
    project_name varchar(150) NOT NULL,
    start_date date NOT NULL,
    end_date date,
    budget decimal(15, 2),
    description_project text,
    institution_id BIGINT NOT NULL,
    object_id BIGINT NOT NULL,
    PRIMARY KEY (project_id),
    CONSTRAINT unique_proj_obj UNIQUE (object_id,project_name),
    CONSTRAINT fk_proj_inst FOREIGN KEY (institution_id)
        REFERENCES Institutions(institution_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_proj_dates
        CHECK (end_date IS NULL OR end_date >= start_date),
    CONSTRAINT fk_proj_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Researcher_Access (
    access_id BIGSERIAL PRIMARY KEY,
    access_date date NOT NULL,
    access_status_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    object_id BIGINT NOT NULL,
    institution_id BIGINT NOT NULL,
    conservation_project_id BIGINT, -- NOTE: conservation_project_id must refer to the same object_id
    CONSTRAINT fk_ra_user FOREIGN KEY (user_id)
        REFERENCES Users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ra_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_ra_inst FOREIGN KEY (institution_id)
        REFERENCES Institutions(institution_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_ra_proj FOREIGN KEY (conservation_project_id)
        REFERENCES Conservation_Projects(project_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_ra_status FOREIGN KEY (access_status_id)
        REFERENCES Status_Types(status_id)
        ON DELETE RESTRICT ON UPDATE cascade
);

CREATE TABLE Object_Publication (
    object_pub_id BIGSERIAL PRIMARY KEY,
    object_id BIGINT NOT NULL,
    publication_id BIGINT NOT NULL,
    page_reference varchar(50),
    notes text,
    CONSTRAINT fk_objpub_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_objpub_pub FOREIGN KEY (publication_id)
        REFERENCES Publications(publication_id)
        ON DELETE RESTRICT ON UPDATE cascade,
    CONSTRAINT unique_obj_pub UNIQUE (object_id,publication_id)
);

CREATE TABLE Object_Classification (
    classification_id BIGSERIAL PRIMARY KEY,
    object_id BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    culture_id BIGINT NOT NULL,
    period_descr varchar(100),
    style varchar(100),
    iconography text,
    CONSTRAINT fk_class_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_class_cat FOREIGN KEY (category_id)
        REFERENCES Categories(category_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_class_cult FOREIGN KEY (culture_id)
        REFERENCES Culture(culture_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE Object_Exhibition (
    object_ex_id BIGSERIAL PRIMARY KEY,
    object_id BIGINT NOT NULL,
    exhibition_id BIGINT NOT NULL,
    UNIQUE (object_id, exhibition_id),
    CONSTRAINT fk_objex_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_objex_exh FOREIGN KEY (exhibition_id)
        REFERENCES Exhibitions(exhibition_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Treatment_Step_Log (
    step_id BIGSERIAL PRIMARY KEY,
    treatment_id BIGINT NOT NULL,
    step_number int NOT NULL,
    step_description varchar(255) NOT NULL,
    observations text,
    timestamp_performed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    performed_by_user BIGINT NOT NULL,
    researcher_access_id BIGINT,
    CONSTRAINT fk_treat_user FOREIGN KEY (performed_by_user)
        REFERENCES Users(user_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_treat_ra FOREIGN KEY (researcher_access_id)
        REFERENCES Researcher_Access(access_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_treat_treatment FOREIGN KEY (treatment_id)
        REFERENCES Treatments(treatment_id)
        ON DELETE CASCADE ON UPDATE cascade,
    CONSTRAINT unique_treatment_step UNIQUE (treatment_id,step_number)
);

CREATE TABLE Object_Location_History (
    history_id BIGSERIAL PRIMARY KEY,
    object_id BIGINT NOT NULL,
    institution_id BIGINT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,

    CONSTRAINT fk_olh_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_olh_inst FOREIGN KEY (institution_id)
        REFERENCES Institutions(institution_id)
        ON DELETE RESTRICT,

    CONSTRAINT chk_olh_dates
        CHECK (end_date IS NULL OR end_date >= start_date)
);

CREATE TABLE Condition_Assessment (
    assessment_id BIGSERIAL PRIMARY KEY,
    assessment_date date NOT NULL,
    notes text,
    object_id BIGINT NOT NULL,
    status_id BIGINT NOT NULL,
    CONSTRAINT fk_ca_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_ca_status FOREIGN KEY (status_id)
        REFERENCES Status_Types(status_id)
        ON DELETE RESTRICT ON UPDATE cascade,
    CONSTRAINT unique_object_assessment_date UNIQUE (object_id,assessment_date)
);

CREATE TABLE Materials_Objects (
    object_id BIGINT NOT NULL,
    material_id BIGINT NOT NULL,
    PRIMARY KEY (object_id, material_id),
    CONSTRAINT fk_mo_mat FOREIGN KEY (material_id)
        REFERENCES Materials(material_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_mo_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Treatment_Materials (
    treatment_id BIGINT NOT NULL,
    material_id BIGINT NOT NULL,

    PRIMARY KEY (treatment_id, material_id),

    CONSTRAINT fk_tm_treatment FOREIGN KEY (treatment_id)
        REFERENCES Treatments(treatment_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_tm_material FOREIGN KEY (material_id)
        REFERENCES Materials(material_id)
        ON DELETE RESTRICT
);

CREATE TABLE Fragments (
    fragment_id BIGSERIAL PRIMARY KEY,
    found_by_user_id BIGINT,
    description text,
    status_id BIGINT,
    discovery_date date,
    site_id BIGINT NOT NULL,
    object_id BIGINT,
    parent_fragment_id BIGINT,
    CONSTRAINT fk_frag_site FOREIGN KEY (site_id)
        REFERENCES Sites(site_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_frag_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_frag_user FOREIGN KEY (found_by_user_id)
        REFERENCES Users(user_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_frag_status FOREIGN KEY (status_id)
        REFERENCES Status_Types(status_id)
        ON DELETE RESTRICT ON UPDATE cascade,
     CONSTRAINT fk_fragment_parent FOREIGN KEY (parent_fragment_id)
        REFERENCES Fragments(fragment_id)
        ON DELETE SET null,
     CONSTRAINT chk_no_self_parent
        CHECK (parent_fragment_id IS NULL OR parent_fragment_id <> fragment_id),
      CONSTRAINT chk_fragment_relation
                        CHECK (
                        NOT (object_id IS NOT NULL AND parent_fragment_id IS NOT NULL))
);

CREATE TABLE Object_Images (
    image_id BIGSERIAL PRIMARY KEY,
    image_url varchar(255) NOT NULL,
    description varchar(150),
    date_taken date,
    object_id BIGINT NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    CONSTRAINT fk_img_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Exhibition_Object_Details (
    exhibition_detail_id BIGSERIAL PRIMARY KEY,
    display_title varchar(255),
    exhibition_description text,
    language varchar(10),
    object_id BIGINT NOT NULL,
    exhibition_id BIGINT NOT NULL,
    CONSTRAINT fk_eod_obj FOREIGN KEY (object_id)
        REFERENCES Objects(object_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_eod_exh FOREIGN KEY (exhibition_id)
        REFERENCES Exhibitions(exhibition_id)
        ON DELETE RESTRICT ON UPDATE cascade,
    CONSTRAINT unique_eod_obj_exh UNIQUE (object_id,exhibition_id)
);

CREATE INDEX idx_objects_site ON Objects(site_id);
CREATE INDEX idx_sites_type_id ON Sites(site_type_id);
CREATE INDEX idx_fragments_object ON Fragments(object_id);


-- ФУНКЦИЈА 1: UPDATE
CREATE OR REPLACE FUNCTION update_object_title(
    obj_id BIGINT,
    new_title VARCHAR(150)
)
RETURNS VOID AS $$
BEGIN
    IF new_title IS NULL OR LENGTH(TRIM(new_title)) = 0 THEN
        RAISE EXCEPTION 'Насловот не смее да биде празен.';
    END IF;

    UPDATE Objects
    SET title = new_title
    WHERE object_id = obj_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Објект со ID % не е пронајден.', obj_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- ФУНКЦИЈА 2: INSERT
CREATE OR REPLACE FUNCTION add_site(
    p_name VARCHAR,
    p_site_type_id BIGINT,
    p_region_id BIGINT,
    p_year INT,
    p_protection_status_id BIGINT,
    p_latitude decimal(9,6),
    p_longitude decimal(9,6),
    p_municipality_id BIGINT,
    p_description VARCHAR DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
    INSERT INTO Sites(
        site_name,
        site_type_id,
        region_id,
        discovery_year,
        protection_status_id,
        latitude,
        longitude,
        municipality_id,
        description
    )
    VALUES (
        p_name,
        p_site_type_id,
        p_region_id,
        p_year,
        p_protection_status_id,
        p_latitude,
        p_longitude,
        p_municipality_id,
        p_description
    );
END;
$$ LANGUAGE plpgsql;



SET synchronous_commit = OFF;

INSERT INTO Regions (name) VALUES
('Скопски'), ('Пелагониски'), ('Вардарски'), ('Источен'),
('Североисточен'), ('Југозападен'), ('Полошки'), ('Југоисточен');

INSERT INTO Site_Types (name) VALUES
('Антички локалитет'), ('Опсерваторија'),
('Средновековен локалитет'), ('Археолошки парк'),('Тумба (Неолитска населба)'),('Наколна населба'),
('Мегалитска опсерваторија'),('Антички град'),('Некропола'),('Римски терми'),('Аквадукт'),('Вила Рустика'),
('Тврдина (Кале)'),('Ранохристијанска базилика'),('Средновековен манастир'),('Антички рудник');

INSERT INTO Protection_Status (name) VALUES
('Заштитен'), ('Делумно заштитен'), ('Незаштитен');
SELECT * FROM Protection_Status;

INSERT INTO Roles (role_name) VALUES
('Admin'), ('Researcher'), ('Conservator'), ('Visitor');

INSERT INTO Status_Types (status_name, type) VALUES
('Изложен', 'object'),        
('Депониран', 'object'),       
('На конзервација', 'object'),
('Објавено', 'publication'), 
('Во подготовка', 'publication'),
('Одобрено', 'access'),   
('Во обработка', 'access'),   
('Одбиено', 'access');      

INSERT INTO Materials (name) VALUES
('Керамика'), ('Бронза'), ('Железо'),
('Камен'), ('Стакло'), ('Злато'),
('Сребро'), ('Коска'), ('Дрво');

UPDATE Materials
SET description_materials = CASE 
    WHEN material_id % 4 = 0 THEN 'Природен материјал со широка употреба во изработка на предмети.'
    WHEN material_id % 4 = 1 THEN 'Често користен материјал во традиционални и модерни техники.'
    WHEN material_id % 4 = 2 THEN 'Материјал со специфични физички и хемиски својства.'
    ELSE 'Историски значаен материјал со културна вредност.'
END;

INSERT INTO Categories (name, description_category) VALUES
('Орудија и оружје', 'Предмети користени за работа, лов и војување, изработени од камен, коска или метал.'),
('Накит', 'Декоративни предмети (фибули, обетки, прстени) изработени од злато, сребро, бронза или стакло.'),
('Садови и керамика', 'Предмети за секојдневна употреба, складирање храна (амфори, питоси) и обредни цели.'),
('Монети', 'Метални парични единици од различни периоди (пајонски, македонски, римски, византиски).'),
('Скулптури и пластика', 'Уметнички дела од мермер, камен или бронза, вклучувајќи статуи, бисти и релјефи.'),
('Теракотни предмети', 'Предмети од печена земја, вклучувајќи ги уникатните Винички икони и фигурини.'),
('Епиграфски споменици', 'Камени натписи, надгробни стели и почесни плочи со историски записи.'),
('Мозаици', 'Подни и ѕидни декорации изработени од мали камчиња (тесери), карактеристични за базиликите.'),
('Архитектонски елементи', 'Делови од градби како капители, столбови и мермерни прагови.'),
('Опрема за секојдневие', 'Светилки (луцерни), коскени игли, огледала и други предмети за домаќинството.');

ALTER TABLE Categories 
ALTER COLUMN description_category SET DEFAULT 'Нема опис';


INSERT INTO Municipalities (name) VALUES
('Скопје'), ('Битола'), ('Охрид'), ('Виница'),('Штип'),('Кавадарци'),('Прилеп'), ('Гевгелија'), ('Струмица'), ('Куманово');

INSERT INTO Institutions (name, address,city) VALUES
('Археолошки музеј на Македонија', 'Кеј Димитар Влахов бб', 'Скопје'),
('Музеј на Македонија', 'Куршумли Ан бб', 'Скопје'), -- Ова е националниот музеј во Старата Чаршија
('Музеј на град Скопје', 'Улица Македонија бб', 'Скопје'),
('Меморијален центар на Холокаустот на Евреите од Македонија', 'Улица Тетовска бб', 'Скопје'),
('Природонаучен музеј на Македонија', 'Булевар Илинден 86', 'Скопје'),

('НУ Завод и музеј Битола', 'Улица Климент Охридски бб', 'Битола'),
('НУ Завод и музеј Прилеп', 'Улица Александар Македонски 142', 'Прилеп'),
('НУ Завод и музеј Охрид', 'Улица Цар Самоил 62', 'Охрид'), -- Куќата на Робевци
('Национален парк Галичица - Музејски центар', 'Улица Наум Охридски 124', 'Охрид'),
('НУ Завод и музеј Струга', 'Улица Партизанска бб', 'Струга'),

('НУ Завод и музеј Струмица', 'Улица 27-ми Март бб', 'Струмица'),
('НУ Завод и музеј Штип', 'Улица Тошо Арсов 10', 'Штип'),
('Народен музеј Велес', 'Улица 8-ми Септември бб', 'Велес'),
('НУ Музеј на град Гевгелија', 'Улица Маршал Тито бб', 'Гевгелија'),
('НУ Музеј Радовиш', 'Улица 22-ри Октомври бб', 'Радовиш'),
('НУ Музеј на град Кочани', 'Улица Маршал Тито бб', 'Кочани'),

('НУ Музеј Куманово', 'Улица 11-ти Октомври бб', 'Куманово'),
('Музеј на Тетовскиот крај', 'Улица Иво Лола Рибар бб', 'Тетово'),
('Музеј на град Кратово', 'Улица Гоце Делчев бб', 'Кратово'),
('Музеј "Западна Македонија во НОВ"', 'Улица Ослободување бб', 'Кичево'),
('Музеј на град Дебар', 'Улица 8-ми Септември бб', 'Дебар');


WITH first_names AS (
  SELECT ARRAY['Ivan','Ana','Marko','Elena','Stefan','Marija'] AS fn_arr
),
last_names AS (
  SELECT ARRAY['Petrov','Stojanovska','Trajkov','Dimitrova','Nikolov','Ivanova'] AS ln_arr
)
INSERT INTO Users (username, full_name, email, password_hash, role_id)
SELECT 
    'user_' || i,
    fn_arr[ceil(random()*array_length(fn_arr,1))] || ' ' ||
    ln_arr[ceil(random()*array_length(ln_arr,1))],
    'user' || i || '@mail.com',
    'pass',
    floor(random()*4 + 1)
FROM generate_series(1, 2000) s(i), first_names, last_names;


INSERT INTO User_Details (user_id,institution_id,specialization)
SELECT
        user_id,
        floor(random()*21+1),
        CASE floor(random()*7)
                WHEN 0 THEN 'Aрхеологија'
                WHEN 1 THEN 'Конзервација'
                WHEN 2 THEN 'Нумизматика'
                WHEN 3 THEN 'Етнологија'
                WHEN 4 THEN 'Историја на уметност'
                WHEN 5 THEN 'Подводна археологија'
                WHEN 6 THEN 'Емиграфика'
        END
        FROM Users u
        JOIN Roles r ON u.role_id=r.role_id
        WHERE r.role_name != 'Visitor';
        
INSERT INTO Sites(site_name,site_type_id,region_id,
                                        protection_status_id,latitude,longitude,altitude,discovery_year,
                                        description,municipality_id,is_active,created_at)
SELECT
        s.name || ' - ' || s.loc,
        s.type_id,
        floor(random()*8+1),
        floor(random()*3+1),
        40.8+(random()*1.5),
        20.5+(random()*2.5),
        (150+(random()*1100))::int,
        1850+(random()*175)::int,
        
        CASE
                WHEN s.name IN ('Градиште', 'Кале', 'Кула') THEN
                        'Стратешко утврдување со остатоци од дефанзивни ѕидови и кули, датира од ' ||
                        (ARRAY['антиката', 'доцната антика', 'средниот век'])[floor(random()*3+1)] || '.'
                WHEN s.name IN ('Тумба', 'Могила') THEN
                'Повеќеслојна праисториска населба со богат инвентар на керамички наоди и остатоци од ' ||
                        (ARRAY['неолитски', 'енеолитски', 'бронзени'])[floor(random()*2+1)]
                ELSE
                        'Археолошки комплекс со значајни архитектонски остатоци и фрагментирана подвижна керамика.'
        END,
        
        floor(random()*10+1),
        (random()>0.1),
        NOW()-(random()*interval '500 days')
        
        FROM(
                SELECT 
                        s.i,
                        (ARRAY['Градиште', 'Тумба', 'Кале', 'Црквиште', 'Висока', 'Могила', 'Бања', 'Манастир', 'Чука', 'Кула'])
                        [floor(random()*10+1)] as name,
                        (ARRAY['с. Злокуќани', 'с. Моноспитово', 'с. Таор', 'с. Кожле', 'Битола', 'Прилеп', 'с. Возарци', 'с. Радолишта', 'с. Вевчани', 'с. Смолани'])
                        [floor(random()*10+1)] || ' (' || s.i || ')' as loc,
                        floor(random()*4+1) as type_id
                FROM generate_series(1,6000) s(i)
        )s;

        
INSERT INTO Objects (
    inventory_number, title, current_status_id, site_id, 
    creation_year, acquisition_date, found_by_user_id, description
)
SELECT 
    'INV-' || i, 
    CASE 
        WHEN random() < 0.3 THEN 'Керамички сад'
        WHEN random() < 0.6 THEN 'Бронзен предмет'
        WHEN random() < 0.8 THEN 'Монета'
        ELSE 'Фрагмент'
    END,
    (SELECT status_id FROM Status_Types WHERE type = 'object' LIMIT 1),
    floor(random() * 6000 + 1), 
    (random() * 2000 + 1)::int, 
    NOW() - (random() * interval '95 years'),
    floor(random() * 2000 + 1), 
    (ARRAY[
        'Одлично зачуван примерок.',
        'Потребна е итна конзервација.',
        'Видливи траги од горење.',
        'Пронајден во станбен објект.',
        'Специфична декорација.',
        NULL
    ])[floor(random() * 6 + 1)]
FROM generate_series(1, 2000000) AS i;
select * from objects limit 20;



INSERT INTO Fragments (
    description, site_id, object_id, status_id, found_by_user_id, discovery_date, parent_fragment_id
)
SELECT 
    CASE 
        WHEN random() < 0.15 THEN 'Керамички фрагмент од сад'
        WHEN random() < 0.30 THEN 'Фрагмент од амфора'
        WHEN random() < 0.45 THEN 'Бронзен украсен дел'
        WHEN random() < 0.60 THEN 'Фрагмент од монета'
        WHEN random() < 0.75 THEN 'Камен архитектонски дел'
        WHEN random() < 0.90 THEN 'Фрагмент од римска керамика'
        ELSE 'Фрагмент од алат од железно време'
    END,
    st_site.ids[floor(random() * array_length(st_site.ids, 1) + 1)],
    st_object.ids[floor(random() * array_length(st_object.ids, 1) + 1)],
    st_status.ids[floor(random() * array_length(st_status.ids, 1) + 1)],
    st_user.ids[floor(random() * array_length(st_user.ids, 1) + 1)],
    NOW() - (floor(random() * 2000) * interval '1 day'),
    NULL
FROM generate_series(1, 500000)
CROSS JOIN (SELECT array_agg(site_id) AS ids FROM Sites) st_site
CROSS JOIN (SELECT array_agg(object_id) AS ids FROM Objects) st_object
CROSS JOIN (SELECT array_agg(status_id) AS ids FROM Status_Types WHERE type='object') st_status
CROSS JOIN (SELECT array_agg(user_id) AS ids FROM Users) st_user;


INSERT INTO Fragments (description, site_id, object_id, status_id, found_by_user_id, discovery_date, parent_fragment_id)
SELECT 
    'Под-фрагмент',
    st_sites.ids[floor(random()*array_length(st_sites.ids,1)+1)],
    NULL,
    st_status.ids[floor(random()*array_length(st_status.ids,1)+1)],
    st_user.ids[floor(random()*array_length(st_user.ids,1)+1)],
    NOW() - (random()*500)*interval '1 day',
    st_parents.ids[floor(random()*array_length(st_parents.ids,1)+1)]
FROM generate_series(1, 500000)
CROSS JOIN (SELECT array_agg(fragment_id) as ids FROM (SELECT fragment_id FROM Fragments LIMIT 200000) p) st_parents
CROSS JOIN (SELECT array_agg(site_id) as ids FROM Sites) st_sites
CROSS JOIN (SELECT array_agg(status_id) as ids FROM Status_Types) st_status
CROSS JOIN (SELECT array_agg(user_id) as ids FROM Users) st_user;


INSERT INTO Fragments (description, site_id, object_id, status_id, found_by_user_id, discovery_date, parent_fragment_id)
SELECT 
    case
        WHEN random() < 0.15 THEN 'Генерален фрагмент'
                WHEN random() < 0.30 THEN 'Фрагмент од амфора'
                WHEN random() < 0.45 THEN 'Генерален фрагмент'
                WHEN random() < 0.60 THEN 'Фрагмент од монета'
                WHEN random() < 0.75 THEN 'Генерален фрагмент'
                WHEN random() < 0.90 THEN 'Фрагмент од римска керамика'
    end,
    st_sites.ids[floor(random()*array_length(st_sites.ids,1)+1)],
    CASE WHEN random() < 0.8 THEN st_objects.ids[floor(random()*array_length(st_objects.ids,1)+1)] ELSE NULL END,
    st_status.ids[floor(random()*array_length(st_status.ids,1)+1)],
    st_user.ids[floor(random()*array_length(st_user.ids,1)+1)],
    NOW() - (random()*1000)*interval '1 day',
    NULL
FROM generate_series(1, 500000)
CROSS JOIN (SELECT array_agg(site_id) as ids FROM Sites) st_sites
CROSS JOIN (SELECT array_agg(object_id) as ids FROM Objects) st_objects
CROSS JOIN (SELECT array_agg(status_id) as ids FROM Status_Types) st_status
CROSS JOIN (SELECT array_agg(user_id) as ids FROM Users) st_user;

INSERT INTO Authors (full_name)
SELECT 
    first_names.name || ' ' || last_names.surname
FROM 
    (SELECT unnest(ARRAY[
        'Иван','Петар','Гоце','Марко','Никола','Александар',
        'Елена','Марија','Даниела','Снежана','Тамара','Кристина',
        'Влатко','Бојан','Љупчо','Стефан','Зоран','Кирил'
    ]) AS name) first_names,
    
    (SELECT unnest(ARRAY[
        'Микулчиќ','Кузман','Манев','Алексов','Стојанов','Петров',
        'Илиев','Георгиев','Марков','Тодоров','Димитров','Павлов',
        'Костов','Ристов','Наумов','Спасов','Стојков','Цветков'
    ]) AS surname) last_names,
    
    generate_series(1, 50000) g(i)
LIMIT 50000;

INSERT INTO Publications (
    request_date,
    main_author_id,
    title,
    status_id,
    publisher_location
)
SELECT 
    CURRENT_DATE - (random()*3000)::int,

    st_author.ids[floor(random()*array_length(st_author.ids,1)+1)],
    CASE 
        WHEN random() < 0.2 THEN 'Археолошки истражувања во Вардарска долина'
        WHEN random() < 0.4 THEN 'Римски период на Балканот: анализа на наоди'
        WHEN random() < 0.6 THEN 'Средновековни утврдувања во Македонија'
        WHEN random() < 0.8 THEN 'Културно наследство на античка Македонија'
        ELSE 'Конзервација и реставрација на археолошки објекти'
    END,
        st_status.ids[floor(random()*array_length(st_status.ids,1)+1)],
    CASE 
        WHEN random() < 0.3 THEN 'Skopje'
        WHEN random() < 0.5 THEN 'Bitola'
        WHEN random() < 0.7 THEN 'Ohrid'
        ELSE 'Strumica'
    END
FROM generate_series(1, 500000)
CROSS JOIN(SELECT array_agg(author_id) as ids FROM Authors)st_author
CROSS JOIN(SELECT array_agg(status_id) as ids FROM Status_Types)st_status;

INSERT INTO Culture (name, origin_region_id, description, period_from_year, period_to_year)
VALUES
-- PRAISTORIJA
('Палеолитска култура', 1, 'Рани човечки заедници и ловци-собирачи', -100000, -10000),
('Неолитска култура', 1, 'Први земјоделски заедници и села', -7000, -3000),
('Бронзено време', 2, 'Развој на металургија и племенски општества', -3000, -1200),
('Железно време', 3, 'Племенски култури пред антика', -1200, -500),

-- ANTIKA
('Пајонска култура', 4, 'Античко племе во регионот на Вардар', -1000, -200),
('Античка македонска култура', 1, 'Кралството на Македонија', -800, -168),
('Хеленистичка култура', 1, 'Период по Александар Велики', -323, -31),

-- RIMSKI PERIOD
('Римска провинциска култура', 2, 'Римска администрација и урбан развој', -168, 300),
('Доцноримска култура', 3, 'Криза и трансформација на Римската империја', 300, 600),

-- VIZANTIJA
('Рановизантиска култура', 4, 'Рано христијанство и византиска уметност', 330, 800),
('Средновизантиска култура', 5, 'Развој на црква и феудализам', 800, 1200),
('Доцновизантиска култура', 5, 'Ослабување на Византија', 1200, 1453),

-- SREDEN VEK
('Словенска култура', 6, 'Доаѓање на словенските племиња', 600, 1000),
('Средновековна балканска култура', 6, 'Феудални држави на Балканот', 1000, 1400),

-- OSMANSKI PERIOD
('Османлиска култура', 7, 'Османлиско владеење на Балканот', 1400, 1912),

-- MODERNO
('Современа култура', 8, 'Модерен период по ослободувањето', 1912, 2020);

WITH obj AS (
    SELECT object_id FROM Objects
),
mat AS (
    SELECT material_id FROM Materials
),
pairs AS (
    SELECT 
        obj.object_id,
        mat.material_id,
        row_number() OVER () AS rn
    FROM obj
    CROSS JOIN mat
)

INSERT INTO Materials_Objects (object_id, material_id)
SELECT
        st_object_id.ids[floor(random()*array_length(st_object_id.ids,1)+1)],
        st_material_id.ids[floor(random()*array_length(st_material_id.ids,1)+1)]
FROM generate_series(1,500000)
CROSS JOIN(SELECT array_agg(object_id) as ids FROM Objects)st_object_id
CROSS JOIN(SELECT array_agg(material_id) as ids FROM Materials)st_material_id
on conflict do nothing;


WITH obj AS (
    SELECT array_agg(object_id) AS oids FROM Objects
),
inst AS (
    SELECT array_agg(institution_id) AS insts FROM Institutions
)

INSERT INTO Conservation_Projects (
    project_name,
    start_date,
    end_date,
    budget,
    description_project,
    institution_id,
    object_id
)
SELECT 
    name || ' ' || i,
        t.start_d,
    t.start_d + (1 + floor(random()*1000)) * interval '1 day',
    (1000 + random()*99000)::numeric(10,2),
    name || ' - детално истражување и конзервација на артефактот.',
    inst.insts[1 + floor(random()*array_length(inst.insts,1))::int],
    obj.oids[1 + floor(random()*array_length(obj.oids,1))::int]

FROM generate_series(1, 200000) i,
     obj, inst,
     LATERAL (
        SELECT 
            CASE 
                WHEN random() < 0.25 THEN 'Проект за конзервација на керамика'
                WHEN random() < 0.50 THEN 'Реставрација на антички објекти'
                WHEN random() < 0.75 THEN 'Археолошко истражување на локалитет'
                ELSE 'Дигитализација на културно наследство'
            END AS name,
                        NOW() - (floor(random()*2000+i*0))*interval '30  days' AS start_d
     ) t;


WITH obj AS (
    SELECT array_agg(object_id) AS oids FROM Objects
)
INSERT INTO Treatments (object_id, treatment_date,description)
SELECT 
    obj.oids[1 + floor(random()*array_length(obj.oids,1))::int],
    CURRENT_DATE - (floor(random()*1000))::int,
        CASE 
            WHEN random() < 0.25 THEN 'Механичко чистење на површински наслоги и земја'
            WHEN random() < 0.40 THEN 'Хемиска стабилизација на кородирани метални делови'
            WHEN random() < 0.55 THEN 'Консолидација на кршлива керамика со паралоид B-72'
            WHEN random() < 0.70 THEN 'Превентивна десалинизација во дејонизирана вода'
            WHEN random() < 0.80 THEN 'Лепење на фрагменти со реверзибилен лепак'
            WHEN random() < 0.90 THEN 'Реставрација на недостатоци со гипс и ретуширање'
            ELSE 'Нанесување на заштитен микрокристален восок'
END
FROM obj, generate_series(1, 500000);

WITH o AS (
    SELECT array_agg(object_id) AS oids FROM Objects
),
s AS (
    SELECT array_agg(status_id) as ids FROM Status_Types WHERE type='object'
)
INSERT INTO Condition_Assessment (
    assessment_date,
    notes,
    object_id,
    status_id
)
SELECT 
    CURRENT_DATE - (floor(random()*2000))::int,
    'Condition note ' || i,
    o.oids[i],
    s.ids[floor(random()*array_length(s.ids,1)+1)]
FROM o, s,
generate_series(1, array_length((SELECT array_agg(object_id) FROM Objects),1)) i;


INSERT INTO Treatment_Step_Log (
    treatment_id, step_number, step_description, observations, 
    timestamp_performed, performed_by_user, researcher_access_id
)
SELECT 
    st_treatment.ids[floor(random()*array_length(st_treatment.ids,1)+1)],
    floor(random()*5+1),
    (ARRAY['Инспекција', 'Чистење', 'Конзервација', 'Документација', 'Проверка'])[floor(random()*5+1)], -- Сигурен избор без NULL
    CASE WHEN random() < 0.5 THEN 'Процесот помина во најдобар ред.' ELSE 'Забележани ситни нечистотии.' END,
    NOW() - (random() * interval '30 days'),
    u.ids[floor(random() * array_length(u.ids, 1) + 1)],
    ra.ids[floor(random() * array_length(ra.ids, 1) + 1)]
FROM generate_series(1, 7000000)
CROSS JOIN (SELECT array_agg(user_id) AS ids FROM Users) u
CROSS JOIN (SELECT array_agg(access_id) AS ids FROM Researcher_Access) ra
CROSS JOIN (SELECT array_agg(treatment_id) AS ids FROM Treatments) st_treatment
on conflict do nothing;

select count(*) from treatment_step_log;



WITH obj AS (
    SELECT array_agg(object_id) AS oids FROM Objects
)
INSERT INTO Object_Images (
    image_url, 
    object_id, 
    description, 
    date_taken, 
    is_primary
)
SELECT 
    'http://img/object_' || selected_id || '_' || i, 
    selected_id,
    CASE 
        WHEN i % 3 = 0 THEN 'Поглед од предна страна'
        WHEN i % 3 = 1 THEN 'Детален приказ на текстура'
        ELSE 'Профилна снимка на предметот'
    END,
    CURRENT_DATE - (random() * 730)::int,
    (random() < 0.15) 
FROM (
    SELECT 
        obj.oids[floor(random() * array_length(obj.oids, 1) + 1)] AS selected_id,
        i
    FROM obj, generate_series(1, 1000000) i
) subquery;


INSERT INTO Researcher_Access (
    access_date,
    access_status_id,
    user_id,
    object_id,
    institution_id,
    conservation_project_id
)
SELECT 
    CURRENT_DATE - (floor(random()*1200))::int,
    st_arr.ids[floor(random() * array_length(st_arr.ids, 1) + 1)],
    u_arr.ids[floor(random() * array_length(u_arr.ids, 1) + 1)],
    o_arr.ids[floor(random() * array_length(o_arr.ids, 1) + 1)],
    i_arr.ids[floor(random() * array_length(i_arr.ids, 1) + 1)],
    CASE 
        WHEN random() < 0.6 THEN p_arr.ids[floor(random() * array_length(p_arr.ids, 1) + 1)]
        ELSE NULL 
    END
FROM 
    (SELECT array_agg(status_id) AS ids FROM Status_Types WHERE type = 'access') st_arr,
    (SELECT array_agg(user_id) AS ids FROM Users) u_arr,
    (SELECT array_agg(object_id) AS ids FROM Objects) o_arr,
    (SELECT array_agg(institution_id) AS ids FROM Institutions) i_arr,
    (SELECT array_agg(project_id) AS ids FROM Conservation_Projects) p_arr,
    generate_series(1, 500000) AS s;



INSERT INTO Exhibitions (start_date, end_date, name, location_institution_id)
SELECT 
    base_date,
    CASE 
        WHEN random() < 0.7 
        THEN base_date + (floor(random()*1500))::int 
        ELSE NULL 
    END,
    CASE (i % 8)
        WHEN 0 THEN 'Праисториска Македонија'
        WHEN 1 THEN 'Античка Македонија'
        WHEN 2 THEN 'Римски период'
        WHEN 3 THEN 'Византија'
        WHEN 4 THEN 'Среден век'
        WHEN 5 THEN 'Османлиски период'
        WHEN 6 THEN 'Археологија'
        ELSE 'Модерна уметност'
    END || ' - Изложба ' || i,
    inst_arr.ids[floor(random() * array_length(inst_arr.ids, 1) + 1)]
FROM generate_series(1, 5000) i
CROSS JOIN (
    SELECT array_agg(institution_id) AS ids FROM Institutions
) inst_arr
CROSS JOIN LATERAL (
    SELECT CURRENT_DATE - (floor(random()*4000+i*0))::int AS base_date
) d;


INSERT INTO Exhibition_Object_Details (
    display_title,
    exhibition_description,
    language,
    object_id,
    exhibition_id
)
SELECT 
    'Објект во изложба ' || gs,

    CASE 
        WHEN random() < 0.3 THEN 'Археолошки наод'
        WHEN random() < 0.6 THEN 'Музејски предмет'
        ELSE 'Културно наследство'
    END,

    CASE WHEN random() < 0.5 THEN 'mk' ELSE 'en' END,

    o.object_id,
    e.exhibition_id

FROM generate_series(1, 10000) gs

JOIN LATERAL (
    SELECT object_id
    FROM Objects
    ORDER BY object_id
    OFFSET (gs % (SELECT COUNT(*) FROM Objects))
    LIMIT 1
) o ON true

JOIN LATERAL (
    SELECT exhibition_id
    FROM Exhibitions
    ORDER BY exhibition_id
    OFFSET (gs % (SELECT COUNT(*) FROM Exhibitions))
    LIMIT 1
) e ON true;

INSERT INTO Object_Classification (
    object_id, 
    category_id, 
    culture_id, 
    period_descr, 
    style, 
    iconography
)
SELECT 
    o.object_id,
    cat.ids[floor(random() * array_length(cat.ids, 1) + 1)],
    cul.ids[floor(random() * array_length(cul.ids, 1) + 1)],
    (ARRAY['Ран неолит', 'Доцна антика', 'Среден век', 'Бронзено доба', 'Хеленистички период'])[floor(random() * 5 + 1)],
    (ARRAY['Доричен', 'Јонски', 'Византиски', 'Локален стил', 'Барок'])[floor(random() * 5 + 1)],
    (ARRAY['Религиозни мотиви', 'Геометриски фигури', 'Животински приказ', 'Растителни орнаменти'])[floor(random() * 4 + 1)]
FROM Objects o
CROSS JOIN (SELECT array_agg(category_id) AS ids FROM Categories) cat
CROSS JOIN (SELECT array_agg(culture_id) AS ids FROM Culture) cul
ON CONFLICT DO NOTHING;


INSERT INTO Object_Exhibition (object_id, exhibition_id)
SELECT 
    o.object_id,
    ex.ids[floor(random() * array_length(ex.ids, 1) + 1)]
FROM (
    SELECT object_id FROM Objects 
    LIMIT 500000
) o
CROSS JOIN (
    SELECT array_agg(exhibition_id) AS ids FROM Exhibitions
) ex
ON CONFLICT DO NOTHING;


INSERT INTO Object_Location_History (
    object_id, 
    institution_id, 
    start_date, 
    end_date
)
SELECT 
    o.object_id,
    inst.ids[floor(random() * array_length(inst.ids, 1) + 1)],
    start_d,
    CASE 
        WHEN random() < 0.7 
        THEN start_d + (random() * 500)::int
        ELSE NULL
    END
FROM (
    SELECT 
        object_id, 
        CURRENT_DATE - (random() * 3000)::int AS start_d 
    FROM Objects
) o
CROSS JOIN (
    SELECT array_agg(institution_id) AS ids FROM Institutions
) inst
ON CONFLICT DO NOTHING;



INSERT INTO Object_Publication (
    object_id,
    publication_id,
    page_reference,
    notes
)
SELECT 
    o.object_id,
    p.ids[floor(random() * array_length(p.ids, 1) + 1)],
    'p.' || floor(random() * 500 + 1)::text,
    'Археолошка публикација'
FROM (
    SELECT object_id FROM Objects LIMIT 200000
) o
CROSS JOIN (
    SELECT array_agg(publication_id) AS ids FROM Publications
) p
ON CONFLICT DO NOTHING;


INSERT INTO Publication_Authors (publication_id, author_id)
SELECT 
    p.publication_id,
    a.ids[floor(random()*array_length(a.ids,1)+1)]
FROM Publications p
CROSS JOIN(SELECT array_agg(author_id) as ids FROM Authors)a
LIMIT 200000;

INSERT INTO Treatment_Materials (treatment_id, material_id)
SELECT 
    t.treatment_id,
    COALESCE(
        CASE 
            WHEN o.title ILIKE '%керами%' OR o.title ILIKE '%сад%' 
                 THEN (SELECT material_id FROM Materials WHERE name = 'Керамика' LIMIT 1)
            WHEN o.title ILIKE '%бронз%' OR o.title ILIKE '%фибул%' 
                 THEN (SELECT material_id FROM Materials WHERE name = 'Бронза' LIMIT 1)
            WHEN o.title ILIKE '%желез%' OR o.title ILIKE '%меч%' OR o.title ILIKE '%нож%' 
                 THEN (SELECT material_id FROM Materials WHERE name = 'Железо' LIMIT 1)
            WHEN o.title ILIKE '%монета%' OR o.title ILIKE '%пари%' 
                 THEN (SELECT material_id FROM Materials WHERE name IN ('Сребро', 'Злато', 'Бронза') ORDER BY random() LIMIT 1)
            WHEN o.title ILIKE '%камен%' OR o.title ILIKE '%стату%' 
                 THEN (SELECT material_id FROM Materials WHERE name = 'Камен' LIMIT 1)
        END,
        m_list.ids[floor(random() * array_length(m_list.ids, 1) + 1)]
    )
FROM (
    SELECT treatment_id, object_id FROM Treatments LIMIT 1000000
) t
JOIN Objects o ON o.object_id = t.object_id
CROSS JOIN (
    SELECT array_agg(material_id) AS ids FROM Materials
) m_list
ON CONFLICT DO NOTHING;

select count(*) from treatment_materials;


CREATE INDEX IF NOT EXISTS idx_objects_site_id ON Objects(site_id);
CREATE INDEX IF NOT EXISTS idx_fragments_site_id ON Fragments(site_id);

CREATE OR REPLACE VIEW Heritage_Full_Overview AS
SELECT 
    f.fragment_id,
    f.description AS fragment_description,
    o.title AS object_title,
    s.site_id,
    s.site_name,
    r.name AS region,
    ps.name AS protection_status
FROM Fragments f
JOIN Objects o ON f.object_id = o.object_id
JOIN Sites s ON f.site_id = s.site_id
JOIN Regions r ON s.region_id = r.region_id
JOIN Protection_Status ps ON s.protection_status_id = ps.protection_status_id;

SELECT * FROM Heritage_Full_Overview LIMIT 100;

-- INSERT
INSERT INTO Fragments (
    description,
    site_id,
    object_id,
    status_id,
    found_by_user_id,
    discovery_date
)
SELECT
    'Нов фрагмент',
    1,
    object_id,
    1,
    1,
    CURRENT_DATE
FROM Objects
LIMIT 1;

-- UPDATE
UPDATE Fragments
SET description = 'Ажуриран фрагмент'
WHERE fragment_id = (
    SELECT fragment_id
    FROM Fragments
    LIMIT 1
);

--podeleni spored lokalitet


create OR replace VIEW Site_Statistics AS
SELECT 
    s.site_id,
    s.site_name,
    r.name AS region,
    COALESCE(o_counts.total_objects, 0) AS total_objects,
    COALESCE(f_counts.total_fragments, 0) AS total_fragments,
    (CURRENT_DATE - (floor(random() * 3650) * interval '1 day'))::timestamp AS last_checked_at
FROM Sites s
JOIN Regions r ON s.region_id = r.region_id
LEFT JOIN (
    SELECT site_id, COUNT(*) AS total_objects 
    FROM Objects 
    GROUP BY site_id
) o_counts ON s.site_id = o_counts.site_id
LEFT JOIN (
    SELECT site_id, COUNT(*) AS total_fragments 
    FROM Fragments 
    GROUP BY site_id
) f_counts ON s.site_id = f_counts.site_id;

SELECT 
    relname AS table_name, 
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
WHERE relname IN ('fragments', 'objects', 'sites');

CREATE INDEX idx_objects_site_id
ON Objects(site_id);

CREATE INDEX idx_fragments_site_id
ON Fragments(site_id);

select * from site_statistics where site_id=39568;

SELECT * 
FROM Site_Statistics
limit 50;



-- INSERT
INSERT INTO Sites (site_name, site_type_id, region_id, protection_status_id, latitude, longitude, discovery_year)
VALUES ('Локалитет 11001', 1, 1, 1, 41.3, 21.7, 2001);

-- UPDATE
UPDATE Sites
SET discovery_year = 2002
WHERE site_id = 100;


--zastiteni lokaliteti
CREATE OR REPLACE VIEW Protected_Sites_Inventory AS
SELECT 
    s.site_id,
    s.site_name,
    r.name AS region,
    s.discovery_year,
    COALESCE(o.total_objects_count, 0) AS total_objects_count
FROM Sites s
JOIN Regions r 
    ON s.region_id = r.region_id
JOIN Protection_Status ps 
    ON s.protection_status_id = ps.protection_status_id
LEFT JOIN (
    SELECT site_id, COUNT(*) AS total_objects_count
    FROM Objects
    GROUP BY site_id
) o 
    ON s.site_id = o.site_id
WHERE ps.name = 'Заштитен'
ORDER BY s.discovery_year DESC;

SELECT * FROM Protected_Sites_Inventory limit 50;


--insert
INSERT INTO Objects (inventory_number, title, current_status_id, site_id)
VALUES ('INV-9999999', 'Test object', 1, 100);

-- UPDATE
UPDATE Objects
SET title = 'UPDATED TEST OBJECT'
WHERE inventory_number = 'INV-9999999';

--spored materijali se prebaruva
CREATE OR REPLACE VIEW Objects_with_Materials AS
SELECT 
    o.object_id,
    o.title,
    m.name AS material
FROM Objects o
LEFT JOIN Materials_Objects om 
    ON o.object_id = om.object_id
LEFT JOIN Materials m 
    ON om.material_id = m.material_id;

SELECT * FROM Objects_with_Materials;

-- UPDATE
UPDATE Materials_Objects
SET material_id = 2
WHERE object_id = 1000;


-- kade se naogaat vo koj lokalitet momentalno 
CREATE OR REPLACE VIEW Object_Current_Location AS
SELECT 
    o.object_id,
    o.title,
    i.name AS institution
FROM Objects o
JOIN Object_Location_History olh ON olh.object_id = o.object_id
JOIN Institutions i ON i.institution_id = olh.institution_id
WHERE olh.end_date IS NULL;


SELECT * FROM Object_Current_Location limit 50;



-- INSERT
INSERT INTO Object_Location_History (object_id, institution_id, start_date)
VALUES (809042, 1, CURRENT_DATE);

-- UPDATE
UPDATE Object_Location_History
SET end_date = CURRENT_DATE
WHERE object_id = 809042
  AND end_date IS NULL;


--na koja kultura pripaga
CREATE OR REPLACE VIEW Object_with_Culture AS
SELECT 
    o.object_id,
    o.title,
    c.name AS culture,
    cat.name AS category
FROM Objects o
JOIN Object_Classification oc ON oc.object_id = o.object_id
JOIN Culture c ON c.culture_id = oc.culture_id
JOIN Categories cat ON cat.category_id = oc.category_id;


SELECT * FROM Object_with_Culture limit 50;

-- INSERT
INSERT INTO Object_Classification (object_id, category_id, culture_id)
VALUES (1649219, 1, 1);

-- UPDATE
UPDATE Object_Classification
SET category_id = 2
WHERE object_id = 1649219;

--koi predmeti na koi izlozbi 

CREATE OR REPLACE VIEW Exhibition_Objects AS
SELECT 
    e.exhibition_id,
    e.name AS exhibition,
    o.object_id,
    o.title AS object,
    i.name AS institution
FROM Object_Exhibition oe
JOIN Exhibitions e ON e.exhibition_id = oe.exhibition_id
JOIN Objects o ON o.object_id = oe.object_id
JOIN Institutions i ON i.institution_id = e.location_institution_id;


SELECT * FROM Exhibition_Objects limit 50;


-- INSERT
INSERT INTO Object_Exhibition (object_id, exhibition_id)
VALUES (
    (SELECT MAX(object_id) FROM Objects),
    (SELECT MAX(exhibition_id) FROM Exhibitions)
);

-- UPDATE
UPDATE Object_Exhibition
SET exhibition_id = 3
WHERE object_id = (
    SELECT MAX(object_id) FROM Object_Exhibition
)
AND exhibition_id = (
    SELECT MAX(exhibition_id) FROM Object_Exhibition
);


--koj naucnik ili istrazuvas pobaral da istrazuva predmet dali mu e odobreno momentalno

CREATE OR REPLACE VIEW Research_Access_Details AS
SELECT 
    ra.user_id,
    u.full_name,
    ra.object_id,
    o.title AS object,
    i.name AS institution,
    s.status_name AS access_status,
    ra.access_date
FROM Researcher_Access ra
JOIN Users u ON u.user_id = ra.user_id
JOIN Objects o ON o.object_id = ra.object_id
JOIN Institutions i ON i.institution_id = ra.institution_id
JOIN Status_Types s ON s.status_id = ra.access_status_id
WHERE s.type = 'access';

SELECT * FROM Research_Access_Details limit 100;


INSERT INTO Researcher_Access (
    access_date, access_status_id, user_id, object_id, institution_id
)
SELECT 
    CURRENT_DATE,
    6,
    user_id,
    object_id,
    1
FROM Users u
JOIN Objects o ON o.object_id = u.user_id
LIMIT 1;


UPDATE Researcher_Access
SET access_status_id = 7
WHERE user_id = 1000 
AND object_id = 1000;

--tretmani vrz odreden objekt

CREATE OR REPLACE VIEW Treatment_History AS
SELECT 
    t.object_id,
    o.title,
    t.treatment_date,
    tsl.step_number,
    tsl.step_description,
    u.full_name
FROM Treatments t
JOIN Objects o ON o.object_id = t.object_id
JOIN Treatment_Step_Log tsl ON tsl.treatment_id = t.treatment_id
JOIN Users u ON u.user_id = tsl.performed_by_user;

SELECT * FROM Treatment_History limit 100;


-- INSERT
INSERT INTO Treatments (object_id, treatment_date, description)
SELECT object_id, CURRENT_DATE, 'Test treatment'
FROM Objects
WHERE object_id BETWEEN 1 AND 10
LIMIT 1;

-- UPDATE
UPDATE Treatments
SET description = 'Update treatment',
    treatment_date = CURRENT_DATE
WHERE object_id = (
    SELECT object_id 
    FROM Objects 
    WHERE object_id BETWEEN 1 AND 10 
    LIMIT 1
)
AND description = 'Test treatment';


--koj avtor koi publikacii gi napravil

CREATE OR REPLACE VIEW Publications_with_Authors AS
SELECT 
    pa.publication_id,
    p.title,
    a.full_name AS author
FROM Publication_Authors pa
JOIN Publications p ON p.publication_id = pa.publication_id
JOIN Authors a ON a.author_id = pa.author_id;


SELECT * FROM Publications_with_Authors limit 100;

-- INSERT
INSERT INTO Publication_Authors (publication_id, author_id)
VALUES (2, 50000);

UPDATE Publication_Authors
SET author_id = 49999
WHERE publication_id = 2
  AND author_id = 50000;



-- function - додавање нов објект кој го нашол и каде
CREATE OR REPLACE FUNCTION find_object_details(p_object_id BIGINT)
RETURNS TABLE (
    object_id BIGINT,
    title TEXT,
    found_by TEXT,
    site_name TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.object_id,
        o.title::TEXT,
        u.full_name::TEXT,
        s.site_name::TEXT
    FROM Objects o
    LEFT JOIN Users u ON o.found_by_user_id = u.user_id
    JOIN Sites s ON o.site_id = s.site_id
    WHERE o.object_id = p_object_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM find_object_details(804822);


-- procedure - додавање нов објект кој го нашол и каде
CREATE OR REPLACE PROCEDURE add_new_object(
    p_inventory VARCHAR,
    p_title VARCHAR,
    p_status_id BIGINT,
    p_site_id BIGINT,
    p_user_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO Objects(
        inventory_number,
        title,
        current_status_id,
        site_id,
        found_by_user_id
    )
    VALUES (
        p_inventory,
        p_title,
        p_status_id,
        p_site_id,
        p_user_id
    );
END;
$$;

CALL add_new_object(
    'INV997',
    'Test Object',
    1,
    1,
    10
);

SELECT *
FROM Objects
WHERE inventory_number = 'INV997';


--додава нов објект + категорија + култура + материјал
CREATE OR REPLACE PROCEDURE catalog_object(
    p_inventory VARCHAR,
    p_title VARCHAR,
    p_status_id BIGINT,
    p_site_id BIGINT,
    p_user_id BIGINT,
    p_category_id BIGINT,
    p_culture_id BIGINT,
    p_material_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_object_id BIGINT;
BEGIN
    INSERT INTO Objects(
        inventory_number,
        title,
        current_status_id,
        site_id,
        found_by_user_id
    )
    VALUES (
        p_inventory,
        p_title,
        p_status_id,
        p_site_id,
        p_user_id
    )
    RETURNING object_id INTO v_object_id;

    INSERT INTO Object_Classification(object_id, category_id, culture_id)
    VALUES (v_object_id, p_category_id, p_culture_id);

    INSERT INTO Materials_Objects(object_id, material_id)
    VALUES (v_object_id, p_material_id);
END;
$$;

CALL catalog_object(
    'INV888',
    'Full Object',
    1,
    1,
    10,
    1,
    1,
    1
);

SELECT *
FROM Objects
WHERE inventory_number = 'INV888';

SELECT *
FROM Object_Classification
WHERE object_id = 2804830;

SELECT *
FROM Materials_Objects
WHERE object_id = 2804830;


--FUNCTION 1 – проверка дали објект е валиден за работа
CREATE OR REPLACE FUNCTION is_object_available(p_object_id BIGINT)
RETURNS BOOLEAN AS $$
DECLARE v_status BIGINT;
BEGIN
    SELECT current_status_id
    INTO v_status
    FROM Objects
    WHERE object_id = p_object_id;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    IF v_status = 3 THEN
        RETURN FALSE;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

SELECT is_object_available(804822);


-- PROCEDURE 1 – додавање третман само ако објект е дозволен
CREATE OR REPLACE PROCEDURE add_treatment(
    p_object_id BIGINT,
    p_desc TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE v_status BIGINT;
BEGIN
    SELECT current_status_id INTO v_status
    FROM Objects
    WHERE object_id = p_object_id;

    IF NOT FOUND OR v_status = 3 THEN
        RAISE EXCEPTION 'Објектот не е достапен за третман';
    END IF;

    INSERT INTO Treatments(object_id, treatment_date, description)
    VALUES (p_object_id, CURRENT_DATE, p_desc);
END;
$$;

CALL add_treatment(804822, 'Basic treatment');
SELECT *
FROM Treatments
WHERE object_id = 804822
ORDER BY treatment_id DESC;


--додавање третман + кој работел на него
CREATE OR REPLACE FUNCTION add_treatment(
    p_object_id BIGINT,
    p_description TEXT,
    p_user_id BIGINT
)
RETURNS VOID AS $$
DECLARE 
    v_treatment_id BIGINT;
BEGIN
    INSERT INTO Treatments(object_id, treatment_date, description)
    VALUES (p_object_id, CURRENT_DATE, p_description)
    RETURNING treatment_id INTO v_treatment_id;

    INSERT INTO Treatment_Step_Log(
        treatment_id,
        step_number,
        step_description,
        performed_by_user
    )
    VALUES (
        v_treatment_id,
        1,
        'Initial treatment',
        p_user_id
    );
END;
$$ LANGUAGE plpgsql;

SELECT add_treatment(804822, 'Cleaning treatment', 10);
SELECT *
FROM Treatments
WHERE object_id = 804822
ORDER BY treatment_id DESC;


-- TRIGGER 1 – автоматско спречување „лоши фрагменти“
CREATE OR REPLACE FUNCTION trg_fragment_validation()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.object_id IS NOT NULL AND NEW.parent_fragment_id IS NOT NULL THEN
        RAISE EXCEPTION 'Фрагмент не може да има и object и parent';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;



INSERT INTO Fragments (object_id, parent_fragment_id, status_id)
VALUES (1, 2, 1);

INSERT INTO Fragments (
    fragment_id,
    site_id,
    object_id,
    status_id
)
VALUES (
    10500004,
    1,
    804822,
    1
);

SELECT *
FROM Fragments
WHERE fragment_id = 10500004;


-- FUNCTION 2 – проверка на истражувачки пристап
CREATE OR REPLACE FUNCTION has_access(
    p_user_id BIGINT,
    p_object_id BIGINT
)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM Researcher_Access
        WHERE user_id = p_user_id
          AND object_id = p_object_id
          AND access_status_id = 6
    );
END;
$$ LANGUAGE plpgsql;

SELECT has_access(10, 804822);


-- PROCEDURE 2 – барање за пристап
CREATE OR REPLACE PROCEDURE request_access(
    p_user_id BIGINT,
    p_object_id BIGINT,
    p_institution_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO Researcher_Access(
        access_date,
        access_status_id,
        user_id,
        object_id,
        institution_id
    )
    VALUES (
        CURRENT_DATE,
        7, -- во обработка
        p_user_id,
        p_object_id,
        p_institution_id
    );
END;
$$;

CALL request_access(10, 804822, 1);


SELECT *
FROM Researcher_Access
WHERE user_id = 10 AND object_id = 804822;

UPDATE Researcher_Access
SET access_status_id = 6
WHERE user_id = 10 AND object_id = 804822;

CALL request_access(10, 804822, 1);



-- FUNCTION 3 – број на автори по публикација
CREATE OR REPLACE FUNCTION count_authors(p_publication_id BIGINT)
RETURNS INT AS $$
DECLARE v_count INT;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM Publication_Authors
    WHERE publication_id = p_publication_id;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

SELECT count_authors(1);
SELECT count_authors(99999);

-- PROCEDURE 3  – додавање автор во публикација (без дупликати)
CREATE OR REPLACE PROCEDURE add_author_to_publication(
    p_publication_id BIGINT,
    p_author_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM Publication_Authors
        WHERE publication_id = p_publication_id
          AND author_id = p_author_id
    ) THEN
        RAISE NOTICE 'Авторот веќе постои';
        RETURN;
    END IF;

    INSERT INTO Publication_Authors(publication_id, author_id)
    VALUES (p_publication_id, p_author_id);
END;
$$;

CALL add_author_to_publication(1, 5);
CALL add_author_to_publication(1, 5);

SELECT *
FROM Publication_Authors
WHERE publication_id = 1 AND author_id = 5;


-- FUNCTION 4 – автоматска проценка на “важност” на објект
CREATE OR REPLACE FUNCTION object_importance(p_object_id BIGINT)
RETURNS INT AS $$
DECLARE
    v_treatments INT;
    v_publications INT;
BEGIN
    SELECT COUNT(*) INTO v_treatments
    FROM Treatments
    WHERE object_id = p_object_id;

    SELECT COUNT(*) INTO v_publications
    FROM Object_Publication
    WHERE object_id = p_object_id;

    RETURN (v_treatments * 2) + (v_publications * 3);
END;
$$ LANGUAGE plpgsql;


SELECT object_importance(1940759);
SELECT object_importance(99999);

SELECT *
FROM Treatments
LIMIT 10;

SELECT *
FROM Object_Publication
LIMIT 10;

-- Тригер кој проверува дека conservation_project_id во Researcher_Access
-- се однесува на истиот object_id наведен во барањето за пристап
-- Без оваа проверка, може да се внесе логички неконзистентен запис каде
-- истражувачот бара пристап до еден артефакт, но наведува проект кој
-- припаѓа на сосема друг предмет. Бидејќи ова правило бара споредба
-- на вредности од две различни табели истовремено, не може да се
-- спроведе со стандарден FOREIGN KEY constraint — затоа се користи тригер.


CREATE OR REPLACE FUNCTION trg_validate_project_object_match()
RETURNS TRIGGER AS $$
DECLARE
    v_project_object_id BIGINT;
BEGIN
    -- Ако нема поврзан проект, нема што да се проверува
    IF NEW.conservation_project_id IS NULL THEN
        RETURN NEW;
    END IF;

    -- Го наоѓаме object_id на проектот од Conservation_Projects
    SELECT object_id
    INTO v_project_object_id
    FROM Conservation_Projects
    WHERE project_id = NEW.conservation_project_id;

    -- Споредуваме: дали проектот навистина го покрива бараниот артефакт
    IF v_project_object_id <> NEW.object_id THEN
        RAISE EXCEPTION 'Конзерваторскиот проект % не припаѓа на објектот %. Пристапот е одбиен.',
            NEW.conservation_project_id, NEW.object_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

SELECT object_id
FROM Conservation_Projects
WHERE project_id = 1;

--valid
INSERT INTO Researcher_Access(
    access_date,
    access_status_id,
    user_id,
    object_id,
    institution_id,
    conservation_project_id
)
VALUES (
    CURRENT_DATE,
    7,
    10,
    1596278,
    1,
    1
);

SELECT *
FROM Researcher_Access
WHERE user_id = 10
  AND object_id = 1596278
  AND conservation_project_id = 1;

--invalid
INSERT INTO Researcher_Access(
    access_date,
    access_status_id,
    user_id,
    object_id,
    institution_id,
    conservation_project_id
)
VALUES (
    CURRENT_DATE,
    7,
    10,
    100,
    1,
    9999
);

CREATE TRIGGER trg_researcher_access_project_match
BEFORE INSERT OR UPDATE ON Researcher_Access
FOR EACH ROW
EXECUTE FUNCTION trg_validate_project_object_match();


CREATE TRIGGER trg_fragment_check
BEFORE INSERT OR UPDATE ON Fragments
FOR EACH ROW
EXECUTE FUNCTION trg_fragment_validation();


SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE indexname LIKE 'idx_%'
ORDER BY tablename;