CREATE TABLE IF NOT EXISTS document_template_assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_id BIGINT UNSIGNED NOT NULL,
  scope_type ENUM('global','event','membership_cycle') NOT NULL DEFAULT 'global',
  scope_id BIGINT UNSIGNED NULL,
  document_type VARCHAR(60) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_document_assignment (scope_type, scope_id, document_type),
  KEY idx_document_assignment_template (template_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS event_streams_v3 (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  provider ENUM('youtube','facebook','vimeo','hls','web') NOT NULL,
  title VARCHAR(180) NOT NULL DEFAULT 'Live stream',
  playback_url VARCHAR(1500) NOT NULL,
  embed_url VARCHAR(1500) NULL,
  is_live TINYINT(1) NOT NULL DEFAULT 0,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_event_stream_live (event_id, is_live)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_devices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  push_token VARCHAR(512) NOT NULL,
  platform ENUM('ios','android','web') NOT NULL,
  app_version VARCHAR(40) NULL,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_push_token (push_token),
  KEY idx_user_devices_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content_type ENUM('resource','news','communication','event','community','system') NOT NULL,
  content_id BIGINT UNSIGNED 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 UNSIGNED NULL,
  status ENUM('draft','queued','sent','failed') NOT NULL DEFAULT 'draft',
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_app_notifications_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS app_notification_reads (notification_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY(notification_id,user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS community_channels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(400) NULL,
  visibility ENUM('members','event','private') NOT NULL DEFAULT 'members',
  event_id BIGINT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS community_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  parent_message_id BIGINT UNSIGNED NULL,
  message_text TEXT NULL,
  attachment_url VARCHAR(1000) NULL,
  attachment_type ENUM('image','video','audio','document','link') NULL,
  is_edited TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_community_messages_feed (channel_id, created_at),
  KEY idx_community_messages_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS community_reactions (
  message_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  reaction VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (message_id, user_id, reaction)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO community_channels(name,description,visibility) VALUES
('Member Lounge','A welcoming space for members to exchange updates and ideas.','members');

INSERT INTO payment_gateways(name,config) VALUES('PAYCHANGU','{"public_key":"","secret_key":"","webhook_url":"/api/v3/payments/paychangu/webhook.php","webhook_secret":"","api_url":"https://api.paychangu.com","enabled":false}')
ON DUPLICATE KEY UPDATE name=VALUES(name);

ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_image_url VARCHAR(1000) NULL AFTER position;