SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE IF NOT EXISTS equipment_service CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE equipment_service;

DROP TABLE IF EXISTS repairs;
DROP TABLE IF EXISTS visits;
DROP TABLE IF EXISTS incidents;
DROP TABLE IF EXISTS equipment;
DROP TABLE IF EXISTS objects;
DROP TABLE IF EXISTS equipment_types;
DROP TABLE IF EXISTS clients;

SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE clients (
    client_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255),
    phone VARCHAR(50),
    email VARCHAR(255),
    address TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE equipment_types (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE objects (
    object_id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    object_name VARCHAR(255) NOT NULL,
    address TEXT,
    contact_person VARCHAR(255),
    phone VARCHAR(50),
    CONSTRAINT fk_objects_client FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE equipment (
    equipment_id INT AUTO_INCREMENT PRIMARY KEY,
    object_id INT NOT NULL,
    type_id INT NOT NULL,
    serial_number VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    installation_date DATE,
    CONSTRAINT fk_equipment_object FOREIGN KEY (object_id) REFERENCES objects(object_id) ON DELETE CASCADE,
    CONSTRAINT fk_equipment_type FOREIGN KEY (type_id) REFERENCES equipment_types(type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE incidents (
    incident_id INT AUTO_INCREMENT PRIMARY KEY,
    equipment_id INT NOT NULL,
    incident_date DATETIME NOT NULL,
    description TEXT NOT NULL,
    status ENUM('open', 'in_progress', 'closed') DEFAULT 'open',
    CONSTRAINT fk_incident_equipment FOREIGN KEY (equipment_id) REFERENCES equipment(equipment_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE visits (
    visit_id INT AUTO_INCREMENT PRIMARY KEY,
    incident_id INT NOT NULL,
    visit_date DATETIME NOT NULL,
    technician_name VARCHAR(255) NOT NULL,
    notes TEXT,
    CONSTRAINT fk_visit_incident FOREIGN KEY (incident_id) REFERENCES incidents(incident_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE repairs (
    repair_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    damage_description TEXT NOT NULL,
    repair_description TEXT,
    repair_date DATETIME NOT NULL,
    cost DECIMAL(10, 2),
    CONSTRAINT fk_repair_visit FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

