RelationalDesign2: kreiranje_5.sql

drop table if exists project.Policy;
drop table if exists project.Customer;
drop table if exists project.Pol_dog;
drop table if exists project.Payment;
drop table if exists project.Package;
drop table if exists project.Covers;
drop table if exists project.Property_pol;
drop table if exists project.Property;
drop table if exists project.Travel_pol;
drop table if exists project.Pol_osi;
drop table if exists project.Auto_pol;
drop table if exists project.Vehicle;

create schema project;

create table project.Package(
  code serial4 primary key,
  title varchar(255) not null,
  total numeric NULL,
  valuet varchar(255) NULL,
  type_pol int4 NULL
);

create table project.Policy(
  p_id serial4 primary key NOT NULL,
  sdate date not null,
  edate date not null,
  package int4,
  constraint fk_package_pol foreign key (package) references project.Package(code)
);

create table project.Payment(
  payment_num serial4 primary key,
  "policy" int4,
  p_date date NULL,
  p_amount int4 NULL,
  visa_number varchar(255)
  constraint fk_payment_pol foreign key (policy) references project.Policy(p_id)
);

create table project.Covers(
  cov_id int serial4 primary key,
  cov_amount int4,
  package int4,
  cov_type varchar(max),
  constraint fk_covers_pol foreign key (package) references project.Package(code)
);


create table project.Customer(
  c_id serial4 primary key, 
  name varchar(50),
  email varchar(50) not null,
  password varchar(50) not null,
  type bool not null
);

create table project.Pol_dog(
  d_embg varchar(13) primary key,
  c_id int4 NULL,
  name varchar(255) not null,
  policy int4,
  surname varchar(255),
  birthdate date not null,
  kontakt varchar(255),
  constraint fk_Profile_User foreign key (c_id) references project.Customer(c_id),
  constraint fk_Pol foreign key (policy) references project.Policy(p_id)
);

CREATE TABLE project.auto_pol (
        a_id serial4 NOT NULL,
        pol_id int4 NULL,
        CONSTRAINT auto_pol_pkey PRIMARY KEY (a_id)
);
ALTER TABLE project.auto_pol ADD CONSTRAINT fk_pol_vehicle FOREIGN KEY (pol_id) REFERENCES project."policy"(p_id);

create table project.Vehicle(
  v_id serial4 primary key,
  policy int4,
  type varchar(255) not null,
  marka varchar(255),
  model varchar(255),
  license_plate varchar(25) not null,
  constraint fk_Pol_veh foreign key (policy) references project.Auto_pol(a_id)
);

create table project.Travel_pol(
  tr_id serial4 primary key,
  pol_id int4 ,
  constraint fk_pol_travel foreign key (pol_id) references project.Policy(p_id)
);

create table project.Pol_osi(
  o_embg varchar(13) primary key,
  policy int4,
  name varchar(255) not null,
  surname varchar(255),
  birthdate date not null,
  kontakt varchar(255),
  constraint fk_Polosi foreign key (policy) references project.Travel_pol(tr_id)
);

create table project.Property_pol(
  pr_id serial4 primary key,
  pol_id int4,
  constraint fk_PolProperty foreign key (pol_id) references project.Policy(p_id)
);

create table project.Property(
  prop_id serial4 primary key,
  policy int4,
  address varchar(255),
  floor int4,
  year_build varchar(255),
  security bool,
  constraint fk_pol_propp foreign key (policy) references project.Property_pol(pr_id)
);



-------------------------------------------------------------------------------------

--View
-- project.packageview source

CREATE OR REPLACE VIEW project.packageview
AS SELECT c.cov_amount,
    c.cov_type,
    p.title AS packagetitle,
    p.total AS packagetotal,
    p.valuet AS packagevalue,
        CASE
            WHEN p.type_pol = 3 THEN 'Auto Policy'::text
            WHEN p.type_pol = 1 THEN 'Travel Health'::text
            ELSE 'Property Policy'::text
        END AS policytype
   FROM project.covers c
     JOIN project.package p ON c.package::integer = p.code;
    
    
--Function
    
CREATE OR REPLACE FUNCTION project.getpolicydata(userwhere text)
 RETURNS TABLE(p_id integer, policytype text, customername text, startdate date, enddate date, packagecode text, packagetitle text, packagetotal numeric)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    EXECUTE '
    SELECT p.p_id, 
           CASE 
               WHEN v.pol_id IS NOT NULL THEN ''Auto Policy''
               WHEN t.pol_id IS NOT NULL THEN ''Travel Health''
               ELSE ''Property Policy''
           END AS PolicyType,
           c.name::TEXT AS CustomerName,
           p.sdate AS StartDate,
           p.edate AS EndDate,
           p.package::TEXT AS PackageCode,
           pkg.title::TEXT AS PackageTitle,
           pkg.total AS PackageTotal
    FROM project.policy p
    LEFT JOIN project.Auto_pol v ON p.p_id = v.pol_id
    LEFT JOIN project.Travel_pol t ON p.p_id = t.pol_id
    LEFT JOIN project.property_pol pp ON p.p_id = pp.pr_id 
    LEFT JOIN project.pol_dog pd ON p.p_id = pd.policy
    LEFT JOIN project.customer c ON pd.c_id = c.c_id --OR t.o_embg = c.c_id
    LEFT JOIN project.package pkg ON p.package = pkg.code
    ' || userwhere || ' 
    ORDER BY p.p_id DESC;
    ';
END;
$function$
;

--Trigger
--#1
create trigger trigger_check_duplicate_p_id before
insert
    on
    project.policy for each row execute function project.check_duplicate_p_id();

CREATE OR REPLACE FUNCTION project.check_duplicate_p_id()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    -- Check if the p_id already exists in the table
    IF EXISTS (SELECT 1 FROM project."policy" WHERE p_id = NEW.p_id) THEN
        -- Raise an exception if a duplicate p_id is found
        RAISE EXCEPTION 'Duplicate p_id: %, cannot insert.', NEW.p_id;
    END IF;
    RETURN NEW;
END;
$function$
;

--#2

CREATE OR REPLACE FUNCTION validate_license_plate() 
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the license plate matches the expected format (XX-1234-XX)
    IF NEW.license_plate !~ '^[A-Z]{2}-\d{4}-[A-Z]{2}$' THEN
        RAISE EXCEPTION 'Invalid license plate format: %', NEW.license_plate;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--Index

CREATE UNIQUE INDEX policy_pkey ON project.policy USING btree (p_id);