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('superadmin','admin_pusat','verifikator','admin_daerah','member') NOT NULL DEFAULT 'member',
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS regions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 parent_id BIGINT UNSIGNED NULL,
 region_code VARCHAR(20) NULL,
 region_name VARCHAR(150) NOT NULL,
 region_type ENUM('province','regency','city') NOT NULL,
 INDEX idx_regions_parent(parent_id),
 CONSTRAINT fk_regions_parent FOREIGN KEY(parent_id) REFERENCES regions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS participants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 full_name VARCHAR(160) NOT NULL,
 email VARCHAR(190) NOT NULL,
 email_verified_at DATETIME NULL,
 participant_type ENUM('pengurus_pusat','pengurus_provinsi','pengurus_kab_kota','anggota','umum') NOT NULL DEFAULT 'umum',
 organization VARCHAR(190) NULL,
 position_title VARCHAR(150) NULL,
 province_id BIGINT UNSIGNED NULL,
 regency_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_participant_email(email),
 CONSTRAINT fk_participant_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_participant_province FOREIGN KEY(province_id) REFERENCES regions(id) ON DELETE SET NULL,
 CONSTRAINT fk_participant_regency FOREIGN KEY(regency_id) REFERENCES regions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_code VARCHAR(40) NOT NULL UNIQUE,
 title VARCHAR(220) NOT NULL,
 event_type ENUM('rapat','webinar','seminar','pelatihan','simposium','lainnya') NOT NULL DEFAULT 'rapat',
 agenda TEXT NULL,
 start_at DATETIME NOT NULL,
 end_at DATETIME NULL,
 location VARCHAR(220) NULL,
 meeting_url VARCHAR(500) NULL,
 registration_mode ENUM('account','public','hybrid') NOT NULL DEFAULT 'hybrid',
 status ENUM('draft','published','open','closed','archived') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL,
 INDEX idx_events_start(start_at),
 CONSTRAINT fk_events_creator FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_agendas (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_id BIGINT UNSIGNED NOT NULL,
 agenda_title VARCHAR(220) NOT NULL,
 start_at DATETIME NULL,
 end_at DATETIME NULL,
 sort_order INT NOT NULL DEFAULT 0,
 CONSTRAINT fk_agenda_event FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_participants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_id BIGINT UNSIGNED NOT NULL,
 participant_id BIGINT UNSIGNED NOT NULL,
 invitation_status ENUM('not_sent','sent','accepted','declined') NOT NULL DEFAULT 'not_sent',
 registration_status ENUM('registered','cancelled') NOT NULL DEFAULT 'registered',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_event_participant(event_id,participant_id),
 CONSTRAINT fk_ep_event FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE,
 CONSTRAINT fk_ep_participant FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendances (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_id BIGINT UNSIGNED NOT NULL,
 participant_id BIGINT UNSIGNED NOT NULL,
 check_in_at DATETIME NULL,
 check_out_at DATETIME NULL,
 attendance_status ENUM('present','absent','excused','pending') NOT NULL DEFAULT 'pending',
 verification_status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
 verification_by BIGINT UNSIGNED NULL,
 verification_at DATETIME NULL,
 source ENUM('account','qr','public_form','admin') NOT NULL DEFAULT 'public_form',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL,
 UNIQUE KEY uq_attendance_event_participant(event_id,participant_id),
 INDEX idx_attendance_checkin(check_in_at),
 CONSTRAINT fk_attendance_event FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE,
 CONSTRAINT fk_attendance_participant FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE CASCADE,
 CONSTRAINT fk_attendance_verifier FOREIGN KEY(verification_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_id BIGINT UNSIGNED NOT NULL,
 document_type ENUM('attendance_list','minutes','summary','certificate') NOT NULL,
 document_number VARCHAR(100) NULL,
 file_path VARCHAR(500) NOT NULL,
 version INT NOT NULL DEFAULT 1,
 issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 issued_by BIGINT UNSIGNED NOT NULL,
 INDEX idx_document_event(event_id),
 CONSTRAINT fk_document_event FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE,
 CONSTRAINT fk_document_issuer FOREIGN KEY(issued_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 participant_id BIGINT UNSIGNED NULL,
 event_id BIGINT UNSIGNED NULL,
 email_type VARCHAR(60) NOT NULL,
 recipient_email VARCHAR(190) NOT NULL,
 delivery_status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
 provider_message_id VARCHAR(190) NULL,
 sent_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_email_status(delivery_status),
 CONSTRAINT fk_email_participant FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE SET NULL,
 CONSTRAINT fk_email_event FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE SET NULL
) 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(100) NOT NULL,
 entity_type VARCHAR(100) NOT NULL,
 entity_id BIGINT UNSIGNED NULL,
 details JSON NULL,
 ip_address VARCHAR(45) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_audit_created(created_at),
 CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_updates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 from_version VARCHAR(30) NULL,
 to_version VARCHAR(30) NOT NULL,
 status ENUM('started','success','failed','rolled_back') NOT NULL,
 log_text TEXT NULL,
 performed_by BIGINT UNSIGNED NULL,
 started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 finished_at DATETIME NULL,
 CONSTRAINT fk_update_user FOREIGN KEY(performed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;