-- Event production schema hotfix. Safe to run repeatedly on MySQL/MariaDB.
-- Back up the database before applying in production.
SET NAMES utf8mb4;

DELIMITER $$
DROP PROCEDURE IF EXISTS ms_hotfix_add_column$$
CREATE PROCEDURE ms_hotfix_add_column(IN p_table VARCHAR(64), IN p_column VARCHAR(64), IN p_definition TEXT)
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=p_table)
     AND NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=p_table AND column_name=p_column) THEN
    SET @ddl=CONCAT('ALTER TABLE `',p_table,'` ADD COLUMN `',p_column,'` ',p_definition);
    PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
  END IF;
END$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS event_activities (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT NOT NULL,
  name VARCHAR(255) NOT NULL,
  summary VARCHAR(500) NULL,
  details_html MEDIUMTEXT NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  location VARCHAR(255) NULL,
  has_capacity_limit TINYINT(1) NOT NULL DEFAULT 0,
  capacity INT UNSIGNED NULL,
  registration_opens_at DATETIME NULL,
  registration_closes_at DATETIME NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'published',
  created_by BIGINT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_event_activities_event_status(event_id,status,starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CALL ms_hotfix_add_column('event_activities','summary','VARCHAR(500) NULL AFTER `name`');
CALL ms_hotfix_add_column('event_activities','details_html','MEDIUMTEXT NULL AFTER `summary`');
CALL ms_hotfix_add_column('event_activities','starts_at','DATETIME NULL AFTER `details_html`');
CALL ms_hotfix_add_column('event_activities','ends_at','DATETIME NULL AFTER `starts_at`');
CALL ms_hotfix_add_column('event_activities','location','VARCHAR(255) NULL AFTER `ends_at`');
CALL ms_hotfix_add_column('event_activities','has_capacity_limit','TINYINT(1) NOT NULL DEFAULT 0 AFTER `location`');
CALL ms_hotfix_add_column('event_activities','capacity','INT UNSIGNED NULL AFTER `has_capacity_limit`');
CALL ms_hotfix_add_column('event_activities','registration_opens_at','DATETIME NULL AFTER `capacity`');
CALL ms_hotfix_add_column('event_activities','registration_closes_at','DATETIME NULL AFTER `registration_opens_at`');
CALL ms_hotfix_add_column('event_activities','status','VARCHAR(20) NOT NULL DEFAULT ''published'' AFTER `registration_closes_at`');
CALL ms_hotfix_add_column('event_activities','created_by','BIGINT NULL AFTER `status`');
CALL ms_hotfix_add_column('event_activities','created_at','TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_by`');
CALL ms_hotfix_add_column('event_activities','updated_at','TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`');

CREATE TABLE IF NOT EXISTS event_activity_images (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  activity_id BIGINT NOT NULL,
  image_url VARCHAR(500) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_event_activity_images_sort(activity_id,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_activity_registrations (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  activity_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'registered',
  registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  cancelled_at DATETIME NULL,
  UNIQUE KEY uq_activity_user(activity_id,user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_notifications (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  content_type VARCHAR(64) NOT NULL,
  content_id BIGINT NULL,
  title VARCHAR(180) NOT NULL,
  body VARCHAR(400) NOT NULL,
  deep_link VARCHAR(1000) NULL,
  audience ENUM('all','members','event_attendees','individual') NOT NULL DEFAULT 'all',
  audience_id BIGINT NULL,
  status ENUM('draft','queued','sent','failed') NOT NULL DEFAULT 'draft',
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE app_notifications MODIFY content_type VARCHAR(64) NOT NULL;
DROP PROCEDURE IF EXISTS ms_hotfix_add_column;

CREATE TABLE IF NOT EXISTS schema_migrations (
  migration_name VARCHAR(190) NOT NULL PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes VARCHAR(500) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO schema_migrations(migration_name,notes)
VALUES('20260901_events_production_hotfix','Event activities and notification schema verified')
ON DUPLICATE KEY UPDATE applied_at=CURRENT_TIMESTAMP,notes=VALUES(notes);