---

-- Таблица сортов растений
CREATE TABLE Sorts (
    sort_id INTEGER PRIMARY KEY AUTOINCREMENT,
    sort_name TEXT NOT NULL UNIQUE,
    flower_type TEXT NOT NULL
);

-- Таблица флористов
CREATE TABLE Florists (
    florist_id INTEGER PRIMARY KEY AUTOINCREMENT,
    last_name TEXT NOT NULL,
    first_name TEXT NOT NULL,
    phone TEXT UNIQUE
);

-- Таблица растений
CREATE TABLE Plants (
    plant_id INTEGER PRIMARY KEY AUTOINCREMENT,
    plant_name TEXT NOT NULL,
    sort_id INTEGER NOT NULL,
    arrival_date DATE NOT NULL,
    disease_signs INTEGER DEFAULT 0 CHECK(disease_signs IN (0,1)),
    price REAL NOT NULL CHECK(price > 0),

    FOREIGN KEY(sort_id) REFERENCES Sorts(sort_id)
);

-- Таблица карантина
CREATE TABLE Quarantine (
    quarantine_id INTEGER PRIMARY KEY AUTOINCREMENT,
    plant_id INTEGER NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    status TEXT NOT NULL CHECK(status IN ('На карантине','Завершен')),

    FOREIGN KEY(plant_id) REFERENCES Plants(plant_id)
);

-- Таблица продаж растений
CREATE TABLE Sales (
    sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
    plant_id INTEGER NOT NULL,
    florist_id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),

    FOREIGN KEY(plant_id) REFERENCES Plants(plant_id),
    FOREIGN KEY(florist_id) REFERENCES Florists(florist_id)
);


CREATE INDEX idx_plant_name
ON Plants(plant_name);


INSERT INTO Sorts (sort_name, flower_type) VALUES
('Ред Наоми', 'Роза'),
('Аваланж', 'Роза'),
('Сибирская', 'Хризантема'),
('Фаленопсис', 'Орхидея'),
('Голландский', 'Тюльпан');


INSERT INTO Florists (last_name, first_name, phone) VALUES
('Иванов', 'Алексей', '89001111111'),
('Петрова', 'Мария', '89002222222'),
('Сидоров', 'Илья', '89003333333'),
('Кузнецова', 'Анна', '89004444444'),
('Орлов', 'Дмитрий', '89005555555');


INSERT INTO Plants
(plant_name, sort_id, arrival_date, disease_signs, price)
VALUES
('Роза красная', 1, '2025-05-01', 0, 450),
('Роза белая', 2, '2025-05-03', 0, 500),
('Хризантема желтая', 3, '2025-05-06', 1, 300),
('Орхидея белая', 4, '2025-05-08', 0, 1200),
('Тюльпан красный', 5, '2025-05-10', 0, 250);


INSERT INTO Quarantine
(plant_id, start_date, end_date, status)
VALUES
(1, '2025-05-01', '2025-05-15', 'Завершен'),
(2, '2025-05-03', '2025-05-17', 'Завершен'),
(3, '2025-05-06', NULL, 'На карантине'),
(4, '2025-05-08', '2025-05-22', 'Завершен'),
(5, '2025-05-10', '2025-05-24', 'Завершен');

INSERT INTO Sales
(plant_id, florist_id, sale_date, quantity)
VALUES
(1, 1, '2025-05-20', 2),
(2, 2, '2025-05-21', 1),
(4, 3, '2025-05-25', 1),
(5, 4, '2025-05-26', 5),
(1, 5, '2025-05-28', 1);

INSERT INTO Sales
(plant_id, florist_id, sale_date, quantity)
VALUES
(1, 1, '2025-05-20', 2),
(2, 2, '2025-05-21', 1),
(4, 3, '2025-05-25', 1),
(5, 4, '2025-05-26', 5),
(1, 5, '2025-05-28', 1);


SELECT *
FROM Plants
WHERE price > 400;


SELECT
    p.plant_name,
    s.sort_name,
    p.price
FROM Plants p
JOIN Sorts s
ON p.sort_id = s.sort_id;