---

create database samoletiki;
\c samoletiki
create table marshrut(
num_mar Serial Primary Key, 
aer_vilet Varchar(100) not null, 
aer_pril Varchar(100) not null,
price_bilet Decimal(10, 2) not null check (price_bilet >= 0), 
prodol_poleta Integer not null check (prodol_poleta > 0)
);

create table samolet(
bort_num Varchar(20) Primary Key,
model Varchar(50) Not null, 
date_izgot Date not null, 
srok_ecspluat Integer Not null check (srok_ecspluat > 0),
gotovnosty Boolean not null default TRUE
);

create table komandir(
lich_num Serial primary Key,
fio Varchar(150) not null,
adress text,
phone Varchar(20),
nalet_chas Integer not null check (nalet_chas >= 0)
);

create table reis(
num_reis Serial Primary Key,
num_mar integer not null,
foreign key (num_mar) references marshrut(num_mar) On delete restrict,
bort_num varchar(20) not null,
foreign key (bort_num) references samolet(bort_num) on delete restrict,
date_time_vilet Timestamp not null,
reis_otmen Boolean not null default false
);

create table passzhir(
num_pasp Varchar(20) primary Key,
fio Varchar(150) not null,
adress Text,
phone Varchar(20) not null
);

create table bilet(
num_pasp Varchar(20) not null,
num_reis Integer Not null,
Primary key(num_pasp, num_reis),
foreign key (num_pasp) references passzhir(num_pasp) on delete cascade,
foreign key (num_reis) references reis(num_reis) on delete cascade
); 

ALTER TABLE samolet ADD COLUMN lich_num_komand INTEGER NOT NULL UNIQUE;
ALTER TABLE samolet ADD FOREIGN KEY (lich_num_komand) REFERENCES komandir(lich_num) ON DELETE RESTRICT;

CREATE INDEX idx_reis_marshrut ON reis(num_mar);
CREATE INDEX idx_reis_samolet ON reis(bort_num);
CREATE INDEX idx_reis_date ON reis(date_time_vilet);
CREATE INDEX idx_bilet_reis ON bilet(num_reis);

INSERT INTO komandir(fio, adress, phone, nalet_chas) VALUES
('Кузнецова Екатерина Максимовна', 'Минск, ул. Ленина 1', '+375291234567', 6752),
('Петров Петр Петрович', 'Минск, ул. Гагарина 5', '+375292345678', 4200);

INSERT INTO samolet(bort_num, model, date_izgot, srok_ecspluat, gotovnosty, lich_num_komand) VALUES
('EW-101PA', 'Боинг-747', '2015-06-01', 25, TRUE, 2),
('EW-202TB', 'Ту-134', '2010-03-12', 30, TRUE, 1);

INSERT INTO marshrut(aer_vilet, aer_pril, price_bilet, prodol_poleta) VALUES
('Минск', 'Франкфурт', 250.00, 120),
('Минск', 'Рига', 180.00, 80);

INSERT INTO reis(num_mar, bort_num, date_time_vilet, reis_otmen) VALUES
(1, 'EW-101PA', '2025-05-20 08:30:00', FALSE),
(2, 'EW-202TB', '2025-05-20 14:15:00', FALSE);

INSERT INTO passzhir(num_pasp, fio, adress, phone) VALUES
('MP1234567', 'Сидоров Сидор Сидорович', 'Минск, ул. Красная 10', '+375293334455'),
('MP7654321', 'Кузнецова Анна Петровна', 'Брест, пр. Машерова 22', '+375256667788');

INSERT INTO bilet(num_pasp, num_reis) VALUES
('MP1234567', 1),
('MP7654321', 1),
('MP7654321', 2);