---

CREATE DATABASE SadyDB2;
GO

USE SadyDB2;
GO

CREATE TABLE Garden1 (
    Garden_ID INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Address NVARCHAR(200) NOT NULL,
    Area DECIMAL(10,2) NOT NULL
);

CREATE TABLE TreeType1 (
    TreeType_ID INT PRIMARY KEY,
    Type_Name NVARCHAR(100) NOT NULL
);

CREATE TABLE Variety1 (
    Variety_ID INT PRIMARY KEY,
    Variety_Name NVARCHAR(100) NOT NULL,
    Flowering_Time NVARCHAR(50) NOT NULL,
    TreeType_ID INT NOT NULL,

    FOREIGN KEY (TreeType_ID) REFERENCES TreeType1(TreeType_ID)
);

CREATE TABLE Tree1 (
    Tree_ID INT PRIMARY KEY,
    Planting_Date DATE NOT NULL,
    Death_Date DATE NULL,
    Status NVARCHAR(50) NOT NULL,
    TreeType_ID INT NOT NULL,
    Garden_ID INT NOT NULL,

    FOREIGN KEY (TreeType_ID) REFERENCES TreeType1(TreeType_ID),
    FOREIGN KEY (Garden_ID) REFERENCES Garden1(Garden_ID)
);

CREATE TABLE Tree_Variety1 (
    TreeVariety_ID INT PRIMARY KEY,
    Tree_ID INT NOT NULL,
    Variety_ID INT NOT NULL,

    FOREIGN KEY (Tree_ID) REFERENCES Tree1(Tree_ID),
    FOREIGN KEY (Variety_ID) REFERENCES Variety1(Variety_ID)
);

CREATE TABLE Harvest1 (
    Harvest_ID INT PRIMARY KEY,
    Tree_ID INT NOT NULL,
    Variety_ID INT NOT NULL,
    Season_Year INT NOT NULL,
    Amount_KG DECIMAL(10,2) NOT NULL,
    Harvest_Date DATE NOT NULL,

    FOREIGN KEY (Tree_ID) REFERENCES Tree1(Tree_ID),
    FOREIGN KEY (Variety_ID) REFERENCES Variety1(Variety_ID)
);

INSERT INTO Garden1 VALUES
(1, N'Весенний сад', N'Тверь, ул. Цветочная 3', 12.4),
(2, N'Луговой сад', N'Москва, ул. Ягодная 15', 17.8),
(3, N'Западный сад', N'Вологда, ул. Полевая 8', 22.1),
(4, N'Яблоневый сад', N'Тула, ул. Солнечная 10', 14.6),
(5, N'Старый сад', N'Калуга, ул. Речная 4', 19.3);

INSERT INTO TreeType1 VALUES
(1, N'Абрикос'),
(2, N'Черешня'),
(3, N'Яблоня'),
(4, N'Груша'),
(5, N'Слива');

INSERT INTO Variety1 VALUES
(1, N'Медовый абрикос', N'Апрель', 1),
(2, N'Черная черешня', N'Май', 2),
(3, N'Белый налив', N'Май', 3),
(4, N'Конференция', N'Апрель', 4),
(5, N'Ранняя слива', N'Март', 5);

INSERT INTO Tree1 VALUES
(1, '2014-05-11', NULL, N'Живое', 1, 1),
(2, '2016-04-09', NULL, N'Живое', 2, 2),
(3, '2013-03-18', '2022-10-01', N'Погибло', 3, 3),
(4, '2020-06-21', NULL, N'Живое', 4, 4),
(5, '2018-05-30', NULL, N'Живое', 5, 5);

INSERT INTO Tree_Variety1 VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5);

INSERT INTO Harvest1 VALUES
(1, 1, 1, 2024, 98.4, '2024-08-11'),
(2, 2, 2, 2024, 135.2, '2024-07-20'),
(3, 3, 3, 2024, 160.0, '2024-09-05'),
(4, 4, 4, 2024, 88.6, '2024-09-12'),
(5, 5, 5, 2024, 73.9, '2024-08-28');
GO