-- ============================================================
-- Sistema de Gestión de Inventario de Equipos de Medición
-- Migración 001 - Esquema completo
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `inventory_db`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `inventory_db`;

-- ------------------------------------------------------------
-- MÓDULO DE USUARIOS Y PERMISOS
-- ------------------------------------------------------------

CREATE TABLE `roles` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `description` VARCHAR(255),
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `modules` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `label` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permissions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `module_id` INT UNSIGNED NOT NULL,
    `action` ENUM('view','create','edit','delete','approve','export') NOT NULL,
    `description` VARCHAR(255),
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_module_action` (`module_id`, `action`),
    CONSTRAINT `fk_perm_module` FOREIGN KEY (`module_id`) REFERENCES `modules`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_permissions` (
    `role_id` INT UNSIGNED NOT NULL,
    `permission_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`role_id`, `permission_id`),
    CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `role_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `email` VARCHAR(200) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(30),
    `avatar_url` VARCHAR(500),
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `last_login` TIMESTAMP NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- MÓDULO DE INVENTARIO
-- ------------------------------------------------------------

CREATE TABLE `equipment_categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `description` VARCHAR(255),
    `icon` VARCHAR(50) DEFAULT 'device',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `equipment_types` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `brand` VARCHAR(100),
    `model` VARCHAR(100),
    `unit_of_measure` VARCHAR(50),
    `parameter_schema` JSON COMMENT 'Esquema de parámetros medibles: [{name, unit, min, max}]',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_etype_cat` FOREIGN KEY (`category_id`) REFERENCES `equipment_categories`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `locations` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `address` VARCHAR(300),
    `city` VARCHAR(100),
    `state` VARCHAR(100),
    `coordinates_lat` DECIMAL(10,7),
    `coordinates_lng` DECIMAL(10,7),
    `notes` TEXT,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `equipment` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type_id` INT UNSIGNED NOT NULL,
    `location_id` INT UNSIGNED,
    `serial_number` VARCHAR(100) NOT NULL UNIQUE,
    `tag` VARCHAR(100),
    `status` ENUM('operational','under_review','out_of_service','in_storage','in_field') NOT NULL DEFAULT 'operational',
    `installation_date` DATE,
    `warranty_start` DATE,
    `warranty_end` DATE,
    `supplier` VARCHAR(150),
    `purchase_order` VARCHAR(100),
    `notes` TEXT,
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_equipment_status` (`status`),
    INDEX `idx_equipment_tag` (`tag`),
    CONSTRAINT `fk_equip_type` FOREIGN KEY (`type_id`) REFERENCES `equipment_types`(`id`),
    CONSTRAINT `fk_equip_location` FOREIGN KEY (`location_id`) REFERENCES `locations`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_equip_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- MÓDULO DE MANTENIMIENTOS / REVISIONES
-- ------------------------------------------------------------

CREATE TABLE `maintenances` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `equipment_id` INT UNSIGNED NOT NULL,
    `technician_id` INT UNSIGNED NOT NULL,
    `supervisor_id` INT UNSIGNED,
    `type` ENUM('preventive','corrective','field','workshop','calibration','installation') NOT NULL,
    `status` ENUM('scheduled','in_progress','completed','cancelled','pending_approval') NOT NULL DEFAULT 'scheduled',
    `scheduled_date` DATE,
    `start_datetime` DATETIME,
    `end_datetime` DATETIME,
    `findings` TEXT COMMENT 'Hallazgos y observaciones',
    `actions_taken` TEXT COMMENT 'Acciones realizadas',
    `recommendations` TEXT,
    `signature_technician` TEXT COMMENT 'Firma en base64',
    `signature_supervisor` TEXT COMMENT 'Firma en base64',
    `approved_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_maint_equipment` (`equipment_id`),
    INDEX `idx_maint_status` (`status`),
    INDEX `idx_maint_type` (`type`),
    CONSTRAINT `fk_maint_equip` FOREIGN KEY (`equipment_id`) REFERENCES `equipment`(`id`),
    CONSTRAINT `fk_maint_tech` FOREIGN KEY (`technician_id`) REFERENCES `users`(`id`),
    CONSTRAINT `fk_maint_super` FOREIGN KEY (`supervisor_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `maintenance_parameters` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `maintenance_id` INT UNSIGNED NOT NULL,
    `parameter_name` VARCHAR(100) NOT NULL,
    `value_before` DECIMAL(15,4),
    `value_after` DECIMAL(15,4),
    `unit` VARCHAR(30),
    `in_range` TINYINT(1),
    `notes` VARCHAR(255),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_mparam_maint` FOREIGN KEY (`maintenance_id`) REFERENCES `maintenances`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `maintenance_attachments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `maintenance_id` INT UNSIGNED NOT NULL,
    `uploaded_by` INT UNSIGNED,
    `original_name` VARCHAR(255) NOT NULL,
    `stored_name` VARCHAR(255) NOT NULL,
    `file_type` ENUM('photo','document','report') NOT NULL DEFAULT 'photo',
    `mime_type` VARCHAR(100),
    `file_size` INT UNSIGNED,
    `path` VARCHAR(500) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_mattach_maint` FOREIGN KEY (`maintenance_id`) REFERENCES `maintenances`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_mattach_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- MÓDULO DE GARANTÍAS
-- ------------------------------------------------------------

CREATE TABLE `warranties` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `equipment_id` INT UNSIGNED NOT NULL,
    `supplier` VARCHAR(150) NOT NULL,
    `contact_name` VARCHAR(150),
    `contact_email` VARCHAR(200),
    `contact_phone` VARCHAR(30),
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `coverage_description` TEXT,
    `claim_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `notes` TEXT,
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_warranty_equip` FOREIGN KEY (`equipment_id`) REFERENCES `equipment`(`id`),
    CONSTRAINT `fk_warranty_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `warranty_claims` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `warranty_id` INT UNSIGNED NOT NULL,
    `reported_by` INT UNSIGNED,
    `claim_date` DATE NOT NULL,
    `description` TEXT NOT NULL,
    `status` ENUM('open','in_progress','resolved','rejected') NOT NULL DEFAULT 'open',
    `resolution` TEXT,
    `resolved_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_claim_warranty` FOREIGN KEY (`warranty_id`) REFERENCES `warranties`(`id`),
    CONSTRAINT `fk_claim_reporter` FOREIGN KEY (`reported_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- AUDIT LOG
-- ------------------------------------------------------------

CREATE TABLE `audit_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED,
    `action` VARCHAR(50) NOT NULL,
    `table_name` VARCHAR(100),
    `record_id` INT UNSIGNED,
    `old_values` JSON,
    `new_values` JSON,
    `ip_address` VARCHAR(45),
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_audit_user` (`user_id`),
    INDEX `idx_audit_table` (`table_name`),
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;