-- IWZ Certification Body
-- Modulo Planning Lead Auditor con Alert e accesso Auditor
-- Importare questo file solo se l'utente MySQL dell'applicazione non dispone dei permessi CREATE/ALTER.
-- Il prefisso rilevato nello schema allegato è "iwz_".


CREATE TABLE IF NOT EXISTS `iwz_auditor_planning` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `auditor_id` int UNSIGNED NOT NULL,
  `auditor_name` varchar(255) NOT NULL,
  `title` varchar(190) NOT NULL,
  `organization` varchar(255) DEFAULT NULL,
  `standard_iso` varchar(100) DEFAULT NULL,
  `appointment_type` varchar(40) NOT NULL DEFAULT 'audit',
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `all_day` tinyint(1) NOT NULL DEFAULT 0,
  `location` varchar(255) DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'planned',
  `notes` text DEFAULT NULL,
  `is_alert` tinyint(1) NOT NULL DEFAULT 0,
  `alert_at` datetime DEFAULT NULL,
  `created_by` int UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_auditor_period` (`auditor_id`, `start_at`, `end_at`),
  KEY `idx_period` (`start_at`, `end_at`),
  KEY `idx_status` (`status`),
  KEY `idx_alert_due` (`is_alert`, `alert_at`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migrazione sicura per installazioni precedenti.
SET @db_name := DATABASE();

SET @sql := IF(
  EXISTS(
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'iwz_auditor_planning'
      AND COLUMN_NAME = 'is_alert'
  ),
  'SELECT 1',
  'ALTER TABLE `iwz_auditor_planning` ADD COLUMN `is_alert` tinyint(1) NOT NULL DEFAULT 0 AFTER `notes`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'iwz_auditor_planning'
      AND COLUMN_NAME = 'alert_at'
  ),
  'SELECT 1',
  'ALTER TABLE `iwz_auditor_planning` ADD COLUMN `alert_at` datetime DEFAULT NULL AFTER `is_alert`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  EXISTS(
    SELECT 1 FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name
      AND TABLE_NAME = 'iwz_auditor_planning'
      AND INDEX_NAME = 'idx_alert_due'
  ),
  'SELECT 1',
  'ALTER TABLE `iwz_auditor_planning` ADD KEY `idx_alert_due` (`is_alert`, `alert_at`, `status`)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
