RelationalDesign2: kreiranje6.sql

drop schema if exists project_2425;

DROP TABLE IF EXISTS project_2425.Travel_pol CASCADE;
DROP TABLE IF EXISTS project_2425.Property_pol CASCADE;
DROP TABLE IF EXISTS project_2425.Auto_pol CASCADE;
DROP TABLE IF EXISTS project_2425.Payment CASCADE;
DROP TABLE IF EXISTS project_2425.Policy CASCADE;
DROP TABLE IF EXISTS project_2425.Covers CASCADE;
DROP TABLE IF EXISTS project_2425.Package CASCADE;
DROP TABLE IF EXISTS project_2425.Policy_Holder CASCADE;
DROP TABLE IF EXISTS project_2425.Customer CASCADE;
DROP TABLE IF EXISTS project_2425.Vehicle CASCADE;
DROP TABLE IF EXISTS project_2425.Property CASCADE;
DROP TABLE IF EXISTS project_2425.Pol_osi CASCADE;

create schema project_2425;

-- Клиент (user account)
CREATE TABLE project_2425.Customer (
  c_id        BIGSERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE NOT NULL,
  type        VARCHAR(50)  NOT NULL,
  pass        VARCHAR(255) NOT NULL
);

-- Полиса носител (осигуреник кој склучува договор)
CREATE TABLE project_2425.Pol_dog (
  d_embg      CHAR(13) PRIMARY KEY,   -- личен број
  name        VARCHAR(100) NOT NULL,
  surname     VARCHAR(100) NOT NULL,
  birthdate   DATE NOT NULL,
  kontakt     VARCHAR(100),
  c_id        BIGINT UNIQUE NOT NULL,
  CONSTRAINT fk_ph_customer
    FOREIGN KEY (c_id) REFERENCES project_2425.Customer(c_id)
);

-- Пакети (типови на осигурување)
CREATE TABLE project_2425.Package (
  code        VARCHAR(30) PRIMARY KEY,
  title       VARCHAR(120) NOT NULL,
  type_pol    VARCHAR(50)  NOT NULL,
  total       DECIMAL(12,2) NOT NULL,
  value       DECIMAL(12,2) NOT NULL
);

-- Покритија
CREATE TABLE project_2425.Covers (
  cov_id      BIGSERIAL PRIMARY KEY,
  cov_type    VARCHAR(80) NOT NULL,
  cov_amount  DECIMAL(12,2) NOT NULL,
  package_code VARCHAR(30) NOT NULL,
  CONSTRAINT fk_covers_package
    FOREIGN KEY (package_code) REFERENCES project_2425.Package(code)
);

-- Полиси
CREATE TABLE project_2425.Policy (
  p_id       BIGSERIAL PRIMARY KEY,
  s_date      DATE NOT NULL,
  e_date      DATE NOT NULL,
  package_code VARCHAR(30) NOT NULL,
  d_embg  CHAR(13) NOT NULL,
  CONSTRAINT fk_policy_package
    FOREIGN KEY (package_code) REFERENCES project_2425.Package(code),
  CONSTRAINT fk_policy_holder
    FOREIGN KEY (d_embg) REFERENCES project_2425.Pol_dog(d_embg)
);

-- Плаќања
CREATE TABLE project_2425.Payment (
  payment_num BIGSERIAL PRIMARY KEY,
  visa_number VARCHAR(30),
  p_date      DATE NOT NULL,
  p_amount    DECIMAL(12,2) NOT NULL,
  policy_id      BIGINT NOT NULL,
  CONSTRAINT fk_payment_policy
    FOREIGN KEY (policy_id) REFERENCES project_2425.Policy(p_id)
);

-- Возила
CREATE TABLE project_2425.Vehicle (
  v_id         BIGSERIAL PRIMARY KEY,
  model        VARCHAR(80) NOT NULL,
  marka        VARCHAR(80) NOT NULL,
  type         VARCHAR(50),
  license_plate VARCHAR(20) UNIQUE NOT NULL
);

-- Неподвижен имот
CREATE TABLE project_2425.Property (
  prop_id     BIGSERIAL PRIMARY KEY,
  address     VARCHAR(200) NOT NULL,
  floor       INT,
  year_build  INT,
  security    bool
);

-- Осигурено лице (за патничко осигурување)
CREATE TABLE project_2425.Pol_osi (
  o_embg      CHAR(13) PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  surname     VARCHAR(100) NOT NULL,
  birthdate   DATE NOT NULL,
  kontakt     VARCHAR(100)
);


-- Авто-полиси (поврзување Policy ↔️ Vehicle)
CREATE TABLE project_2425.Auto_pol (
  a_id    BIGSERIAL PRIMARY KEY,
  pol_id  BIGINT NOT NULL,
  v_id    BIGINT NOT NULL,
  CONSTRAINT fk_autopol_policy  FOREIGN KEY (pol_id) REFERENCES project_2425.Policy(p_id),
  CONSTRAINT fk_autopol_vehicle FOREIGN KEY (v_id)   REFERENCES project_2425.Vehicle(v_id),
  CONSTRAINT uq_autopol UNIQUE (pol_id, v_id)
);

-- Property-полиси (поврзување Policy ↔️ Property)
CREATE TABLE project_2425.Property_pol (
  pr_id   BIGSERIAL PRIMARY KEY,
  pol_id  BIGINT NOT NULL,
  prop_id BIGINT NOT NULL,
  CONSTRAINT fk_proppol_policy   FOREIGN KEY (pol_id)  REFERENCES project_2425.Policy(p_id),
  CONSTRAINT fk_proppol_property FOREIGN KEY (prop_id) REFERENCES project_2425.Property(prop_id),
  CONSTRAINT uq_proppol UNIQUE (pol_id, prop_id)
);

-- Travel-полиси (поврзување Policy ↔️ осигурено лице)
CREATE TABLE project_2425.Travel_pol (
  tr_id   BIGSERIAL PRIMARY KEY,
  pol_id  BIGINT NOT NULL,
  o_embg  CHAR(13) NOT NULL,
  CONSTRAINT fk_travelpol_policy FOREIGN KEY (pol_id) REFERENCES project_2425.Policy(p_id),
  CONSTRAINT fk_travelpol_person FOREIGN KEY (o_embg) REFERENCES project_2425.Pol_osi(o_embg),
  CONSTRAINT uq_travelpol UNIQUE (pol_id, o_embg)
);