import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddRoomInspections1784680000000 implements MigrationInterface {
  name = 'AddRoomInspections1784680000000';

  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`CREATE TABLE \`room_inspections\` (
      \`id\` int NOT NULL AUTO_INCREMENT,
      \`inspection_date\` date NOT NULL,
      \`room_id\` int NOT NULL,
      \`inspector_id\` int NULL,
      \`inspector_name\` varchar(50) NOT NULL,
      \`source\` varchar(20) NOT NULL DEFAULT 'manual',
      \`submitted_at\` datetime NOT NULL,
      \`created_at\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      \`updated_at\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
      UNIQUE INDEX \`uq_room_inspections_date_room\` (\`inspection_date\`, \`room_id\`),
      INDEX \`idx_room_inspections_inspector_id\` (\`inspector_id\`),
      PRIMARY KEY (\`id\`)
    ) ENGINE=InnoDB`);
    await queryRunner.query(`CREATE TABLE \`room_inspection_details\` (
      \`id\` int NOT NULL AUTO_INCREMENT,
      \`inspection_id\` int NOT NULL,
      \`occupancy_id\` int NOT NULL,
      \`student_id\` int NOT NULL,
      \`bed_id\` int NULL,
      \`status\` varchar(20) NOT NULL,
      \`student_name_snapshot\` varchar(100) NOT NULL,
      \`bed_number_snapshot\` varchar(20) NULL,
      UNIQUE INDEX \`uq_room_inspection_details_occupancy\` (\`inspection_id\`, \`occupancy_id\`),
      INDEX \`idx_room_inspection_details_occupancy_id\` (\`occupancy_id\`),
      INDEX \`idx_room_inspection_details_student_id\` (\`student_id\`),
      INDEX \`idx_room_inspection_details_bed_id\` (\`bed_id\`),
      PRIMARY KEY (\`id\`)
    ) ENGINE=InnoDB`);
    await queryRunner.query('ALTER TABLE `room_inspections` ADD CONSTRAINT `fk_room_inspections_room` FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE CASCADE');
    await queryRunner.query('ALTER TABLE `room_inspections` ADD CONSTRAINT `fk_room_inspections_inspector` FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE SET NULL');
    await queryRunner.query('ALTER TABLE `room_inspection_details` ADD CONSTRAINT `fk_room_inspection_details_inspection` FOREIGN KEY (`inspection_id`) REFERENCES `room_inspections`(`id`) ON DELETE CASCADE');
    await queryRunner.query('ALTER TABLE `room_inspection_details` ADD CONSTRAINT `fk_room_inspection_details_occupancy` FOREIGN KEY (`occupancy_id`) REFERENCES `occupancies`(`id`) ON DELETE RESTRICT');
    await queryRunner.query('ALTER TABLE `room_inspection_details` ADD CONSTRAINT `fk_room_inspection_details_student` FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE RESTRICT');
    await queryRunner.query('ALTER TABLE `room_inspection_details` ADD CONSTRAINT `fk_room_inspection_details_bed` FOREIGN KEY (`bed_id`) REFERENCES `beds`(`id`) ON DELETE SET NULL');
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query('ALTER TABLE `room_inspection_details` DROP FOREIGN KEY `fk_room_inspection_details_bed`');
    await queryRunner.query('ALTER TABLE `room_inspection_details` DROP FOREIGN KEY `fk_room_inspection_details_student`');
    await queryRunner.query('ALTER TABLE `room_inspection_details` DROP FOREIGN KEY `fk_room_inspection_details_occupancy`');
    await queryRunner.query('ALTER TABLE `room_inspection_details` DROP FOREIGN KEY `fk_room_inspection_details_inspection`');
    await queryRunner.query('ALTER TABLE `room_inspections` DROP FOREIGN KEY `fk_room_inspections_inspector`');
    await queryRunner.query('ALTER TABLE `room_inspections` DROP FOREIGN KEY `fk_room_inspections_room`');
    await queryRunner.query('DROP TABLE `room_inspection_details`');
    await queryRunner.query('DROP TABLE `room_inspections`');
  }
}
