-- Baltique OS Core 0.65.0 — Governance Calendar & Review Board

CREATE TABLE IF NOT EXISTS clinical_content_review_board_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_key VARCHAR(190) NOT NULL,
  source_table VARCHAR(80) NOT NULL,
  source_id BIGINT UNSIGNED NOT NULL,
  workflow_id BIGINT UNSIGNED NULL,
  approval_id BIGINT UNSIGNED NULL,
  recertification_id BIGINT UNSIGNED NULL,
  block_id BIGINT UNSIGNED NULL,
  role_key VARCHAR(80) NULL,
  item_type VARCHAR(80) NOT NULL DEFAULT 'approval',
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  due_at DATE NULL,
  overdue_since DATE NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  assigned_user_id BIGINT UNSIGNED NULL,
  assigned_email VARCHAR(190) NULL,
  reminder_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_reminder_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  resolved_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clin_review_item_key(item_key),
  KEY idx_clin_review_due(due_at,status,priority),
  KEY idx_clin_review_role(role_key,status),
  KEY idx_clin_review_block(block_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_content_review_reminders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  review_item_id BIGINT UNSIGNED NULL,
  workflow_id BIGINT UNSIGNED NULL,
  approval_id BIGINT UNSIGNED NULL,
  recertification_id BIGINT UNSIGNED NULL,
  block_id BIGINT UNSIGNED NULL,
  role_key VARCHAR(80) NULL,
  reminder_type VARCHAR(80) NOT NULL DEFAULT 'email',
  recipient_email VARCHAR(190) NULL,
  recipient_user_id BIGINT UNSIGNED NULL,
  subject VARCHAR(255) NOT NULL,
  body TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  queue_table VARCHAR(80) NULL,
  queue_id BIGINT UNSIGNED NULL,
  scheduled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_clin_reminder_status(status,scheduled_at),
  KEY idx_clin_reminder_role(role_key,status),
  KEY idx_clin_reminder_item(review_item_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_content_governance_calendar_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_key VARCHAR(190) NOT NULL,
  event_date DATE NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  workflow_id BIGINT UNSIGNED NULL,
  approval_id BIGINT UNSIGNED NULL,
  recertification_id BIGINT UNSIGNED NULL,
  block_id BIGINT UNSIGNED NULL,
  role_key VARCHAR(80) NULL,
  title VARCHAR(255) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clin_cal_event_key(event_key),
  KEY idx_clin_cal_date(event_date,event_type,status),
  KEY idx_clin_cal_role(role_key,event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_content_compliance_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_month CHAR(7) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  total_blocks INT UNSIGNED NOT NULL DEFAULT 0,
  approved_blocks INT UNSIGNED NOT NULL DEFAULT 0,
  in_review_blocks INT UNSIGNED NOT NULL DEFAULT 0,
  expired_blocks INT UNSIGNED NOT NULL DEFAULT 0,
  overdue_approval_count INT UNSIGNED NOT NULL DEFAULT 0,
  overdue_recertification_count INT UNSIGNED NOT NULL DEFAULT 0,
  reminder_count INT UNSIGNED NOT NULL DEFAULT 0,
  compliance_score DECIMAL(6,2) NULL,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clin_compliance_month(report_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
