CREATE TABLE Marka (
    kod_marki SERIAL PRIMARY KEY,
    nazvanie_marki VARCHAR(100) NOT NULL
);
select from marka

CREATE TABLE Model (
    id_modeli SERIAL PRIMARY KEY,
    kod_marki INT NOT NULL REFERENCES Marka(kod_marki)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    kod_modeli VARCHAR(20) NOT NULL,
    nazvanie_modeli VARCHAR(100) NOT NULL
);

select from model

CREATE TABLE Avtomobil (
    id_avtomobilya SERIAL PRIMARY KEY,
    id_modeli INT NOT NULL REFERENCES Model(id_modeli)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    VIN_nomer CHAR(17) NOT NULL UNIQUE,
    cena DECIMAL(12,2) NOT NULL,
    klass_avtomobilya VARCHAR(50),
    tehnicheskie_dannye TEXT
);

SELECT COUNT(*) FROM model

CREATE TABLE Klient (
    id_klienta SERIAL PRIMARY KEY,
    adres VARCHAR(255),
    nomer_telefona VARCHAR(20),
    gorod VARCHAR(100)
);

SELECT * FROM klient;

CREATE TABLE Zakaz (
    id_zakaza SERIAL PRIMARY KEY,
    id_klienta INT NOT NULL REFERENCES Klient(id_klienta)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    id_avtomobilya INT NOT NULL REFERENCES Avtomobil(id_avtomobilya)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    data_pokupki DATE NOT NULL
);

SELECT * FROM users;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    login VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL
);

INSERT INTO users(login, password, role)
VALUES
('admin', '12345', 'Администратор'),
('manager', '11111', 'Менеджер'),
('guest', '00000', 'Гость');

--хранимая процедура по нахождению списка городов 
CREATE OR REPLACE FUNCTION GetCitiesByMarkAndPeriod(
    p_nazvanie_marki VARCHAR,
    p_date_from DATE,
    p_date_to DATE
)
RETURNS TABLE(gorod VARCHAR, nazvanie_marki VARCHAR, kolichestvo_zakaz BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        k.gorod,
        ma.nazvanie_marki,
        COUNT(z.id_zakaza) AS kolichestvo_zakaz
    FROM Zakaz z
    JOIN Klient k       ON z.id_klienta     = k.id_klienta
    JOIN Avtomobil a    ON z.id_avtomobilya  = a.id_avtomobilya
    JOIN Model mo       ON a.id_modeli       = mo.id_modeli
    JOIN Marka ma       ON mo.kod_marki      = ma.kod_marki
    WHERE ma.nazvanie_marki = p_nazvanie_marki
      AND z.data_pokupki BETWEEN p_date_from AND p_date_to
    GROUP BY k.gorod, ma.nazvanie_marki
    ORDER BY kolichestvo_zakaz DESC;
END;
$$;

SELECT * FROM GetCitiesByMarkAndPeriod('Toyota',
    '2023-01-01',
    '2026-06-03');

TRUNCATE TABLE zakaz RESTART IDENTITY CASCADE;
TRUNCATE TABLE avtomobil RESTART IDENTITY CASCADE;
TRUNCATE TABLE klient RESTART IDENTITY CASCADE;
TRUNCATE TABLE model RESTART IDENTITY CASCADE;
TRUNCATE TABLE marka RESTART IDENTITY CASCADE;


SELECT COUNT(*)
FROM zakaz
WHERE data_pokupki BETWEEN '2023-01-01' AND '2026-06-03';
SELECT nazvanie_marki FROM marka;

--расчетный атрибут
CREATE OR REPLACE VIEW vw_avtomobil AS
SELECT
    id_avtomobilya,
    VIN_nomer,
    cena,
    cena * 1.20 AS cena_s_nds,
    klass_avtomobilya
FROM Avtomobil;

SELECT * FROM vw_avtomobil;