SET NAMES utf8mb4;
SET time_zone = '+00:00';
CREATE TABLE IF NOT EXISTS event_settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value LONGTEXT NOT NULL,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin_principal','admin_operacional','seguranca') NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS groups_registry (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  registration_sequence BIGINT UNSIGNED NULL UNIQUE,
  group_name VARCHAR(120) NOT NULL,
  participant_count TINYINT UNSIGNED NOT NULL,
  responsible_name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  email VARCHAR(190) NOT NULL,
  status ENUM('waiting','scheduled','called','admitted','cancelled','absent') NOT NULL DEFAULT 'waiting',
  panel_token_hash CHAR(64) NOT NULL UNIQUE,
  qr_token_hash CHAR(64) NOT NULL UNIQUE,
  admitted_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT chk_group_participants CHECK (participant_count BETWEEN 1 AND 30),
  INDEX idx_group_status_sequence (status, registration_sequence),
  INDEX idx_group_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legal_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_type ENUM('privacy','responsibility','rules','cookies') NOT NULL,
  version VARCHAR(30) NOT NULL,
  title VARCHAR(190) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_document_version (document_type, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legal_acceptances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NOT NULL,
  document_type ENUM('privacy','responsibility','rules') NOT NULL,
  document_version VARCHAR(30) NOT NULL,
  accepted_at DATETIME NOT NULL,
  ip_address VARCHAR(45) NULL,
  CONSTRAINT fk_acceptance_group FOREIGN KEY (group_id) REFERENCES groups_registry(id),
  INDEX idx_acceptance_group (group_id, document_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS entry_operations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sequence_no INT UNSIGNED NOT NULL,
  status ENUM('planned','ready','started','completed','cancelled') NOT NULL DEFAULT 'planned',
  scheduled_at DATETIME NOT NULL,
  actual_started_at DATETIME NULL,
  completed_at DATETIME NULL,
  started_by BIGINT UNSIGNED NULL,
  notes VARCHAR(1000) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_operation_started_by FOREIGN KEY (started_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_operation_status_time (status, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operation_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  operation_id BIGINT UNSIGNED NOT NULL,
  group_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_og_operation FOREIGN KEY (operation_id) REFERENCES entry_operations(id),
  CONSTRAINT fk_og_group FOREIGN KEY (group_id) REFERENCES groups_registry(id),
  UNIQUE KEY uq_operation_group (operation_id, group_id),
  UNIQUE KEY uq_group_operation (group_id),
  INDEX idx_og_group (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NOT NULL,
  operation_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  participant_count TINYINT UNSIGNED NOT NULL,
  status ENUM('admitted','rejected') NOT NULL,
  validated_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_admission_group FOREIGN KEY (group_id) REFERENCES groups_registry(id),
  CONSTRAINT fk_admission_operation FOREIGN KEY (operation_id) REFERENCES entry_operations(id),
  CONSTRAINT fk_admission_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_admission_group (group_id),
  INDEX idx_admission_time (validated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NOT NULL,
  template_key VARCHAR(100) NOT NULL,
  status ENUM('queued','sent','failed') NOT NULL,
  error_summary VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_email_group FOREIGN KEY (group_id) REFERENCES groups_registry(id) ON DELETE SET NULL,
  INDEX idx_email_status_time (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS announcements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_announcement_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_announcement_active (active, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS incidents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NULL,
  operation_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  incident_type ENUM('absent','late','delay','exception','entry_issue','other') NOT NULL,
  description TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_incident_group FOREIGN KEY (group_id) REFERENCES groups_registry(id) ON DELETE SET NULL,
  CONSTRAINT fk_incident_operation FOREIGN KEY (operation_id) REFERENCES entry_operations(id) ON DELETE SET NULL,
  CONSTRAINT fk_incident_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_incident_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  context_json JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_entity (entity_type, entity_id, created_at),
  INDEX idx_audit_action_time (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO event_settings (setting_key, setting_value) VALUES
('capacity', '2000'),
('max_group_size', '30'),
('max_operation_size', '30'),
('interval_seconds', '135'),
('registration_enabled', 'true'),
('registration_opens_at', '"2026-10-31 19:00:00"'),
('auto_grouping_enabled', 'true'),
('operations_paused', 'false'),
('capacity_lock', '"singleton"');

INSERT IGNORE INTO legal_documents (document_type, version, title, body, is_current) VALUES
('privacy','1.0','Política de Privacidade','MODELO INICIAL: completar finalidades, fundamento jurídico, prazos de conservação, direitos, destinatários, contacto institucional e responsável pelo tratamento antes da publicação. Entidade organizadora provisória: Junta de Freguesia do Bom Sucesso. Telefone: 233 920 479.',1),
('responsibility','1.0','Declaração de Responsabilidade','Declaro que os dados fornecidos no registo do grupo são verdadeiros e estão corretos, e que tenho legitimidade para efetuar este registo e assumir as responsabilidades que me competem enquanto responsável pelo grupo.',1),
('rules','1.0','Termos e Regras de Participação','MODELO INICIAL: regras de entrada e permanência a validar pela entidade organizadora antes da publicação.',1),
('cookies','1.0','Política de Cookies','A aplicação deve documentar apenas os cookies efetivamente utilizados. Rever após validação da configuração final.',1);