-- Добавяне на таблица за оборудване
CREATE TABLE IF NOT EXISTS `equipment` (
  `equipment_id` INT NOT NULL AUTO_INCREMENT,
  `object_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `type` VARCHAR(100) NOT NULL,
  `model` VARCHAR(100) NOT NULL,
  `serial_number` VARCHAR(100) NULL,
  `installation_date` DATE NULL,
  `last_inspection_date` DATE NULL,
  `next_inspection_date` DATE NULL,
  `status` ENUM('active', 'inactive', 'maintenance') NOT NULL DEFAULT 'active',
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`equipment_id`),
  INDEX `fk_equipment_object_idx` (`object_id` ASC),
  CONSTRAINT `fk_equipment_object`
    FOREIGN KEY (`object_id`)
    REFERENCES `objects` (`object_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Добавяне на таблица за системи
CREATE TABLE IF NOT EXISTS `systems` (
  `system_id` INT NOT NULL AUTO_INCREMENT,
  `object_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`system_id`),
  INDEX `fk_systems_object_idx` (`object_id` ASC),
  CONSTRAINT `fk_systems_object`
    FOREIGN KEY (`object_id`)
    REFERENCES `objects` (`object_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Добавяне на таблица за връзка между оборудване и системи
CREATE TABLE IF NOT EXISTS `equipment_system` (
  `equipment_id` INT NOT NULL,
  `system_id` INT NOT NULL,
  PRIMARY KEY (`equipment_id`, `system_id`),
  INDEX `fk_equipment_system_system_idx` (`system_id` ASC),
  CONSTRAINT `fk_equipment_system_equipment`
    FOREIGN KEY (`equipment_id`)
    REFERENCES `equipment` (`equipment_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equipment_system_system`
    FOREIGN KEY (`system_id`)
    REFERENCES `systems` (`system_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Добавяне на таблица за проверки на оборудване
CREATE TABLE IF NOT EXISTS `equipment_inspections` (
  `inspection_id` INT NOT NULL AUTO_INCREMENT,
  `equipment_id` INT NOT NULL,
  `inspector_id` INT NOT NULL,
  `inspection_date` DATE NOT NULL,
  `status` ENUM('passed', 'failed', 'needs_maintenance') NOT NULL,
  `notes` TEXT NULL,
  `next_inspection_date` DATE NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inspection_id`),
  INDEX `fk_equipment_inspections_equipment_idx` (`equipment_id` ASC),
  INDEX `fk_equipment_inspections_user_idx` (`inspector_id` ASC),
  CONSTRAINT `fk_equipment_inspections_equipment`
    FOREIGN KEY (`equipment_id`)
    REFERENCES `equipment` (`equipment_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equipment_inspections_user`
    FOREIGN KEY (`inspector_id`)
    REFERENCES `users` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Добавяне на таблица за проблеми с оборудване
CREATE TABLE IF NOT EXISTS `equipment_problems` (
  `problem_id` INT NOT NULL AUTO_INCREMENT,
  `equipment_id` INT NOT NULL,
  `inspection_id` INT NULL,
  `reported_by` INT NOT NULL,
  `report_date` DATE NOT NULL,
  `description` TEXT NOT NULL,
  `priority` ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
  `status` ENUM('pending', 'in_progress', 'resolved') NOT NULL DEFAULT 'pending',
  `resolution_notes` TEXT NULL,
  `resolution_date` DATE NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`problem_id`),
  INDEX `fk_equipment_problems_equipment_idx` (`equipment_id` ASC),
  INDEX `fk_equipment_problems_inspection_idx` (`inspection_id` ASC),
  INDEX `fk_equipment_problems_user_idx` (`reported_by` ASC),
  CONSTRAINT `fk_equipment_problems_equipment`
    FOREIGN KEY (`equipment_id`)
    REFERENCES `equipment` (`equipment_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equipment_problems_inspection`
    FOREIGN KEY (`inspection_id`)
    REFERENCES `equipment_inspections` (`inspection_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equipment_problems_user`
    FOREIGN KEY (`reported_by`)
    REFERENCES `users` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Добавяне на връзка между снимки и оборудване
ALTER TABLE `photos` 
ADD COLUMN `equipment_id` INT NULL AFTER `inspection_id`,
ADD INDEX `fk_photos_equipment_idx` (`equipment_id` ASC),
ADD CONSTRAINT `fk_photos_equipment`
  FOREIGN KEY (`equipment_id`)
  REFERENCES `equipment` (`equipment_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
