-- CreateTable
CREATE TABLE `empresas` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `nombre` VARCHAR(191) NOT NULL,
    `razonSocial` VARCHAR(191) NULL,
    `rfc` VARCHAR(191) NULL,
    `email` VARCHAR(191) NULL,
    `telefono` VARCHAR(191) NULL,
    `estado` ENUM('ACTIVA', 'SUSPENDIDA', 'CANCELADA') NOT NULL DEFAULT 'ACTIVA',
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `empresas_uuid_key`(`uuid`),
    UNIQUE INDEX `empresas_rfc_key`(`rfc`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `roles` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `codigo` VARCHAR(191) NOT NULL,
    `nombre` VARCHAR(191) NOT NULL,
    `descripcion` VARCHAR(191) NULL,
    `esSistema` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `roles_codigo_key`(`codigo`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `permisos` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `codigo` VARCHAR(191) NOT NULL,
    `nombre` VARCHAR(191) NOT NULL,
    `descripcion` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `permisos_codigo_key`(`codigo`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `roles_permisos` (
    `rolId` INTEGER NOT NULL,
    `permisoId` INTEGER NOT NULL,

    PRIMARY KEY (`rolId`, `permisoId`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `usuarios` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `empresaId` INTEGER NOT NULL,
    `rolId` INTEGER NOT NULL,
    `nombre` VARCHAR(191) NOT NULL,
    `apellidos` VARCHAR(191) NULL,
    `email` VARCHAR(191) NOT NULL,
    `passwordHash` VARCHAR(191) NOT NULL,
    `estado` ENUM('ACTIVO', 'INACTIVO', 'BLOQUEADO') NOT NULL DEFAULT 'ACTIVO',
    `ultimoAcceso` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `usuarios_uuid_key`(`uuid`),
    INDEX `usuarios_empresaId_idx`(`empresaId`),
    UNIQUE INDEX `usuarios_empresaId_email_key`(`empresaId`, `email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `clientes` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `empresaId` INTEGER NOT NULL,
    `tipoPersona` VARCHAR(191) NOT NULL DEFAULT 'MORAL',
    `nombre` VARCHAR(191) NOT NULL,
    `razonSocial` VARCHAR(191) NULL,
    `rfc` VARCHAR(191) NULL,
    `email` VARCHAR(191) NULL,
    `telefono` VARCHAR(191) NULL,
    `contacto` VARCHAR(191) NULL,
    `activo` BOOLEAN NOT NULL DEFAULT true,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `clientes_uuid_key`(`uuid`),
    INDEX `clientes_empresaId_idx`(`empresaId`),
    UNIQUE INDEX `clientes_empresaId_rfc_key`(`empresaId`, `rfc`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `instalaciones` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `empresaId` INTEGER NOT NULL,
    `clienteId` INTEGER NOT NULL,
    `nombre` VARCHAR(191) NOT NULL,
    `descripcion` VARCHAR(191) NULL,
    `calle` VARCHAR(191) NULL,
    `numeroExterior` VARCHAR(191) NULL,
    `numeroInterior` VARCHAR(191) NULL,
    `colonia` VARCHAR(191) NULL,
    `municipio` VARCHAR(191) NULL,
    `estado` VARCHAR(191) NULL,
    `codigoPostal` VARCHAR(191) NULL,
    `latitud` DECIMAL(10, 7) NULL,
    `longitud` DECIMAL(10, 7) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `instalaciones_uuid_key`(`uuid`),
    INDEX `instalaciones_empresaId_idx`(`empresaId`),
    INDEX `instalaciones_clienteId_idx`(`clienteId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `expedientes` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(191) NOT NULL,
    `empresaId` INTEGER NOT NULL,
    `clienteId` INTEGER NOT NULL,
    `instalacionId` INTEGER NOT NULL,
    `responsableId` INTEGER NULL,
    `folio` VARCHAR(191) NOT NULL,
    `titulo` VARCHAR(191) NOT NULL,
    `descripcion` VARCHAR(191) NULL,
    `tipoVerificacion` VARCHAR(191) NULL,
    `estado` ENUM('BORRADOR', 'SOLICITUD_RECIBIDA', 'DOCUMENTACION_PENDIENTE', 'DOCUMENTACION_COMPLETA', 'EN_REVISION', 'VISITA_PROGRAMADA', 'VISITA_REALIZADA', 'CON_OBSERVACIONES', 'CORRECCIONES_RECIBIDAS', 'EN_VALIDACION', 'APROBADO', 'CERRADO', 'CANCELADO') NOT NULL DEFAULT 'BORRADOR',
    `fechaSolicitud` DATETIME(3) NULL,
    `fechaCierre` DATETIME(3) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `expedientes_uuid_key`(`uuid`),
    INDEX `expedientes_empresaId_idx`(`empresaId`),
    INDEX `expedientes_clienteId_idx`(`clienteId`),
    INDEX `expedientes_instalacionId_idx`(`instalacionId`),
    UNIQUE INDEX `expedientes_empresaId_folio_key`(`empresaId`, `folio`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `expedientes_estados` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `expedienteId` INTEGER NOT NULL,
    `estadoAnterior` ENUM('BORRADOR', 'SOLICITUD_RECIBIDA', 'DOCUMENTACION_PENDIENTE', 'DOCUMENTACION_COMPLETA', 'EN_REVISION', 'VISITA_PROGRAMADA', 'VISITA_REALIZADA', 'CON_OBSERVACIONES', 'CORRECCIONES_RECIBIDAS', 'EN_VALIDACION', 'APROBADO', 'CERRADO', 'CANCELADO') NULL,
    `estadoNuevo` ENUM('BORRADOR', 'SOLICITUD_RECIBIDA', 'DOCUMENTACION_PENDIENTE', 'DOCUMENTACION_COMPLETA', 'EN_REVISION', 'VISITA_PROGRAMADA', 'VISITA_REALIZADA', 'CON_OBSERVACIONES', 'CORRECCIONES_RECIBIDAS', 'EN_VALIDACION', 'APROBADO', 'CERRADO', 'CANCELADO') NOT NULL,
    `comentario` VARCHAR(191) NULL,
    `usuarioId` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `expedientes_estados_expedienteId_idx`(`expedienteId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `auditorias` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `empresaId` INTEGER NOT NULL,
    `usuarioId` INTEGER NULL,
    `accion` VARCHAR(191) NOT NULL,
    `entidad` VARCHAR(191) NOT NULL,
    `entidadId` VARCHAR(191) NULL,
    `datos` JSON NULL,
    `ip` VARCHAR(191) NULL,
    `userAgent` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `auditorias_empresaId_createdAt_idx`(`empresaId`, `createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `roles_permisos` ADD CONSTRAINT `roles_permisos_rolId_fkey` FOREIGN KEY (`rolId`) REFERENCES `roles`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `roles_permisos` ADD CONSTRAINT `roles_permisos_permisoId_fkey` FOREIGN KEY (`permisoId`) REFERENCES `permisos`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `usuarios` ADD CONSTRAINT `usuarios_empresaId_fkey` FOREIGN KEY (`empresaId`) REFERENCES `empresas`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `usuarios` ADD CONSTRAINT `usuarios_rolId_fkey` FOREIGN KEY (`rolId`) REFERENCES `roles`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `clientes` ADD CONSTRAINT `clientes_empresaId_fkey` FOREIGN KEY (`empresaId`) REFERENCES `empresas`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `instalaciones` ADD CONSTRAINT `instalaciones_empresaId_fkey` FOREIGN KEY (`empresaId`) REFERENCES `empresas`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `instalaciones` ADD CONSTRAINT `instalaciones_clienteId_fkey` FOREIGN KEY (`clienteId`) REFERENCES `clientes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `expedientes` ADD CONSTRAINT `expedientes_empresaId_fkey` FOREIGN KEY (`empresaId`) REFERENCES `empresas`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `expedientes` ADD CONSTRAINT `expedientes_clienteId_fkey` FOREIGN KEY (`clienteId`) REFERENCES `clientes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `expedientes` ADD CONSTRAINT `expedientes_instalacionId_fkey` FOREIGN KEY (`instalacionId`) REFERENCES `instalaciones`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `expedientes` ADD CONSTRAINT `expedientes_responsableId_fkey` FOREIGN KEY (`responsableId`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `expedientes_estados` ADD CONSTRAINT `expedientes_estados_expedienteId_fkey` FOREIGN KEY (`expedienteId`) REFERENCES `expedientes`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `auditorias` ADD CONSTRAINT `auditorias_empresaId_fkey` FOREIGN KEY (`empresaId`) REFERENCES `empresas`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `auditorias` ADD CONSTRAINT `auditorias_usuarioId_fkey` FOREIGN KEY (`usuarioId`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;