DatabaseCreation: views.sql

-- views.sql
-- Погледи (views) и помошни индекси за нив.

CREATE INDEX IF NOT EXISTS idx_sites_region ON Sites(region_id);

CREATE INDEX IF NOT EXISTS idx_sites_status ON Sites(protection_status_id);

DROP VIEW IF EXISTS Heritage_Full_Overview CASCADE;

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;

--podeleni spored lokalitet

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);

ANALYZE Objects;

ANALYZE Fragments;

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;

--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;

--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;

-- 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;

CREATE INDEX idx_olh_end_date ON Object_Location_History(end_date);

--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;

CREATE INDEX idx_oc_object_id ON Object_Classification(object_id);

--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;

CREATE INDEX idx_oe_exhibition_id ON Object_Exhibition(exhibition_id);

CREATE INDEX idx_oe_object_id ON Object_Exhibition(object_id);

--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';

CREATE INDEX idx_ra_user_id ON Researcher_Access(user_id);

CREATE INDEX idx_ra_object_id ON Researcher_Access(object_id);

ANALYZE Researcher_Access;

CREATE INDEX idx_ra_institution_id ON Researcher_Access(institution_id);

ANALYZE Objects;

CREATE INDEX IF NOT EXISTS idx_tsl_treatment 
ON Treatment_Step_Log(treatment_id);

CREATE INDEX IF NOT EXISTS idx_treatments_object 
ON Treatments(object_id);

CREATE INDEX IF NOT EXISTS idx_tsl_treatment_user_step
ON Treatment_Step_Log(treatment_id, performed_by_user, step_number);

CREATE INDEX idx_ra_user_object 
ON Researcher_Access(user_id, object_id);

--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;

--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;

CREATE INDEX idx_pa_publication_id ON Publication_Authors(publication_id);

CREATE INDEX idx_pa_author_id ON Publication_Authors(author_id);

ANALYZE Publication_Authors;

ANALYZE Publications;