sql code

CREATE DATABASE training;
CREATE SCHEMA company;
set search_path = company;
CREATE DOMAIN valid_number AS TEXT CHECK (VALUE ~ '^[+][7][0-9]{10}$');

CREATE TABLE client (
    id SERIAL CONSTRAINT client_pk PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL ,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50) NULL,
    address TEXT NOT NULL,
    phone valid_number NOT NULL
);

CREATE TABLE filial (
    id NUMERIC (3) CONSTRAINT filial_pk PRIMARY KEY,
    name TEXT NOT NULL,
    address TEXT NOT NULL,
    phone valid_number NOT NULL
);

CREATE TABLE agent (
    id SERIAL CONSTRAINT agent_pk PRIMARY KEY,
    filial_id INTEGER NOT NULL CONSTRAINT agent_filial_fk REFERENCES filial (id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    last_name VARCHAR(50) NOT NULL ,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50) NULL,
    phone valid_number NOT NULL
);

CREATE TABLE vid_strah (
    id SERIAL CONSTRAINT vid_strah_id_pk PRIMARY KEY,
    name TEXT CONSTRAINT vid_strah_name_key UNIQUE ,
    tarif_stav DECIMAL CONSTRAINT tarif_stav_check CHECK ((tarif_stav > (0)::DECIMAL) AND (tarif_stav <= (100)::DECIMAL))
);

CREATE TABLE dogovor (
    no SERIAL CONSTRAINT dogovor_pk PRIMARY KEY,
    date_of_reg DATE CONSTRAINT date_of_reg_check CHECK (date_of_reg <= current_date),
    strah_sum DECIMAL CONSTRAINT strah_sum_check CHECK (strah_sum > 0::DECIMAL),
    id_filial INTEGER NOT NULL CONSTRAINT dogovor_filial_fk REFERENCES filial (id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    id_vid_strah INTEGER NOT NULL CONSTRAINT dogovor_vidstrah_fk REFERENCES vid_strah(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    agent_id INTEGER CONSTRAINT dogovor_agent_fk REFERENCES agent (id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    client_id INTEGER NOT NULL CONSTRAINT dogovor_client_fk REFERENCES client(id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE ROLE administrators LOGIN PASSWORD '1' INHERIT;
GRANT USAGE ON SCHEMA company TO administrators;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA company to administrators;
GRANT administrators TO bob1;

CREATE ROLE USERS LOGIN PASSWORD '1';
CREATE ROLE user1 LOGIN PASSWORD '1';
GRANT USERS to user1;
GRANT USAGE ON SCHEMA company TO USERS;
GRANT SELECT ON company.agent, company.client, company.dogovor, company.filial, company.vid_strah TO USERS;
GRANT SELECT ON agents_info TO USERS;
GRANT SELECT ON dogovors_info TO USERS;

SELECT a.last_name, a.first_name, d.date_of_reg FROM dogovor AS d
JOIN agent AS a ON a.id = d.agent_id WHERE (date_of_reg BETWEEN current_date - '30 days'::interval AND current_date) AND (a.id = 1);

CREATE OR REPLACE VIEW agents_info AS
    SELECT a.last_name, a.first_name, a.middle_name, a.phone AS agent_phone, f.name AS filial_name, f.address AS filal_address, f.phone AS filial_phone
    FROM agent AS a JOIN filial AS f ON a.filial_id = f.id ORDER BY a.last_name;

CREATE OR REPLACE VIEW dogovors_info AS
    SELECT c.last_name AS client_lastname,
       c.first_name AS client_name,
       a.last_name AS agent_lastname,
       a.first_name AS agent_name,
       f.name AS filial_name,
       f.address AS filial_address,
       v.name AS vid_strah,
       v.tarif_stav,
       d.date_of_reg
    FROM dogovor AS d
    JOIN filial AS f ON d.id_filial = f.id
    JOIN vid_strah AS v ON d.id_vid_strah = v.id
    JOIN agent AS a ON d.agent_id = a.id
    JOIN client AS c ON d.client_id = c.id ORDER BY d.no;

INSERT INTO vid_strah (name, tarif_stav) VALUES ('Страхование автотранспорта от угона', 2);
INSERT INTO vid_strah (name, tarif_stav) VALUES ('Страхование домашнего имущества', 1);
INSERT INTO vid_strah (name, tarif_stav) VALUES ('Добровольное медицинское страхование', 2);

INSERT INTO client (last_name, first_name, middle_name, address, phone) VALUES ('Lorem', 'Ipsum', 'Dolor', 'Kirov', '+79999999999');

INSERT INTO filial (id, name, address, phone) VALUES (110 ,'filial 1_1', 'Moscow', '+74959000000');
INSERT INTO filial (id, name, address, phone) VALUES (120, 'filial 1_2', 'Moscow', '+74959001000');
INSERT INTO filial (id, name, address, phone) VALUES (130, 'filial 1_3', 'Moscow', '+74959002000');
INSERT INTO filial (id, name, address, phone) VALUES (210, 'filial 2_1', 'SPb', '+77009001000');
INSERT INTO filial (id, name, address, phone) VALUES (310, 'filial 3_1', 'Kirov', '+79539487019');

insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('130', 'Mathelon', 'Darius', 'Daveench', '+73971339107');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('130', 'Beardwood', 'Judd', null, '+73851885338');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Gouldeby', 'Lea', null, '+76163073411');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Franzonello', 'Norrie', null, '+71334264669');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('210', 'Povey', 'Anny', 'Ardeench', '+74552909505');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Olufsen', 'Joannes', 'Camilech', '+78247515982');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('120', 'Cardillo', 'Gasparo', null, '+76780097614');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Blaymires', 'Hermon', 'Berthach', '+79841679422');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('210', 'Pedrazzi', 'Di', 'Nicolettech', '+79258728872');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('210', 'Glavis', 'Lulita', 'Luisech', '+74344154512');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Noyes', 'Alanson', null, '+77513625483');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Benesevich', 'Gabbi', 'Byranch', '+72755135920');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Armiger', 'Augusta', 'Cchaddiech', '+75997818286');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Metrick', 'Padget', null, '+70004144130');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('110', 'Viscovi', 'Shaw', 'Gregch', '+75616397794');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Mosey', 'Britney', 'Gillesch', '+71478952650');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('210', 'Feechum', 'Serena', 'Maddich', '+74073231360');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('210', 'Farrington', 'Neda', 'Jacklynch', '+77291005091');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('210', 'Mableson', 'Bevvy', 'Kevanch', '+79206848088');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Whetland', 'Isabeau', null, '+78024714722');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Kleinerman', 'Rodina', 'Landch', '+78634286797');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('110', 'Wiggin', 'Samaria', 'Galench', '+79333405221');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('120', 'Chrismas', 'Alysa', 'Crawfordch', '+75244534213');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('130', 'Sterry', 'Jori', null, '+78117771555');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Gibard', 'Dallas', 'Luchoch', '+77079449623');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('110', 'Nasi', 'Guglielma', null, '+71608543094');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Gleder', 'Holt', 'Ravich', '+76018479906');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Alfonzo', 'Prince', null, '+75817092551');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('120', 'Hassell', 'Emile', 'Creightch', '+74281376833');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('130', 'Wiskar', 'Mitchell', 'Bartch', '+70478582391');
insert into agent (filial_id, last_name, first_name, middle_name, phone) values ('310', 'Cecchi', 'Nadean', 'Olinch', '+71148159242');

INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-05-11'::DATE, 300000, 310, 1, 1, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-05-10'::DATE, 300000, 310, 2, 1, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-05-10'::DATE, 300000, 310, 3, 1, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-04-20'::DATE, 300000, 310, 1, 1, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-02-21'::DATE, 300000, 310, 2, 1, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-05-05'::DATE, 300000, 310, 3, 1, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-04-19'::DATE, 300000, 310, 1, 1, 1);

INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-04-19'::DATE, 300000, 310, 2, 2, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2021-01-01'::DATE, 300000, 310, 2, 3, 1);
INSERT INTO dogovor (date_of_reg, strah_sum, id_filial, id_vid_strah, agent_id, client_id)
VALUES ('2022-05-01'::DATE, 300000, 310, 3, 5, 1);