---

CREATE TABLE Hive (
    id_hive INT AUTO_INCREMENT PRIMARY KEY,
    hive_number VARCHAR(20) NOT NULL,
    queen_age INT NOT NULL,
    colony_strength VARCHAR(50) NOT NULL
);

CREATE TABLE Beekeeper (
    id_beekeeper INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20)
);

CREATE TABLE Equipment (
    id_equipment INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    protective BOOLEAN DEFAULT FALSE,
    quantity INT DEFAULT 0
);

CREATE TABLE Honey (
    id_honey INT AUTO_INCREMENT PRIMARY KEY,
    id_hive INT NOT NULL,
    harvest_date DATE NOT NULL,
    weight_kg DECIMAL(6,2) NOT NULL,
    honey_type VARCHAR(50),

    FOREIGN KEY (id_hive)
        REFERENCES Hive(id_hive)
);

CREATE TABLE Hive_Service (
    id_service INT AUTO_INCREMENT PRIMARY KEY,
    id_hive INT NOT NULL,
    id_beekeeper INT NOT NULL,
    service_date DATE NOT NULL,
    protective_used BOOLEAN DEFAULT FALSE,

    FOREIGN KEY (id_hive)
        REFERENCES Hive(id_hive),

    FOREIGN KEY (id_beekeeper)
        REFERENCES Beekeeper(id_beekeeper)
);