INSERT IGNORE INTO `engine4_core_modules` (`name`, `title`, `description`, `version`, `enabled`, `type`) VALUES
('live', 'Gamifans Live', 'Professional live streaming for Gamifans with WebRTC, gifts, moderation, replays and Live Clash.', '8.0.0', 1, 'extra');

INSERT IGNORE INTO `engine4_core_menuitems` (`name`, `module`, `label`, `plugin`, `params`, `menu`, `submenu`, `enabled`, `custom`, `order`) VALUES
('core_main_live', 'live', 'Live', '', '{"route":"live_general","action":"index","icon":"fa fa-video-camera"}', 'core_main', '', 1, 0, 21),
('core_mini_live', 'live', 'Live', '', '{"route":"live_general","action":"index","icon":"fa fa-video-camera"}', 'core_mini', '', 1, 0, 21),
('core_admin_main_plugins_live', 'live', 'Gamifans Live', '', '{"route":"admin_default","module":"live","controller":"stats"}', 'core_admin_main_plugins', '', 1, 0, 940),
('live_admin_main_stats', 'live', 'Stats', '', '{"route":"admin_default","module":"live","controller":"stats"}', 'live_admin_main', '', 1, 0, 1),
('live_admin_main_manage', 'live', 'Lives', '', '{"route":"admin_default","module":"live","controller":"manage"}', 'live_admin_main', '', 1, 0, 2),
('live_admin_main_settings', 'live', 'Settings', '', '{"route":"admin_default","module":"live","controller":"settings"}', 'live_admin_main', '', 1, 0, 3),
('live_admin_main_categories', 'live', 'Categories', '', '{"route":"admin_default","module":"live","controller":"categories"}', 'live_admin_main', '', 1, 0, 4),
('live_admin_main_gifts', 'live', 'Gifts', '', '{"route":"admin_default","module":"live","controller":"gifts"}', 'live_admin_main', '', 1, 0, 5),
('live_admin_main_replays', 'live', 'Replays', '', '{"route":"admin_default","module":"live","controller":"replays"}', 'live_admin_main', '', 1, 0, 6),
('live_admin_main_clips', 'live', 'Clips', '', '{"route":"admin_default","module":"live","controller":"clips"}', 'live_admin_main', '', 1, 0, 7),
('live_admin_main_captions', 'live', 'Captions', '', '{"route":"admin_default","module":"live","controller":"captions"}', 'live_admin_main', '', 1, 0, 8),
('live_admin_main_sponsored', 'live', 'Sponsored', '', '{"route":"admin_default","module":"live","controller":"sponsored"}', 'live_admin_main', '', 1, 0, 9),
('live_admin_main_schedule', 'live', 'Schedule', '', '{"route":"admin_default","module":"live","controller":"schedule"}', 'live_admin_main', '', 1, 0, 10),
('live_admin_main_reports', 'live', 'Reports', '', '{"route":"admin_default","module":"live","controller":"reports"}', 'live_admin_main', '', 1, 0, 11),
('live_admin_main_revenue', 'live', 'Revenue', '', '{"route":"admin_default","module":"live","controller":"revenue"}', 'live_admin_main', '', 1, 0, 12),
('live_admin_main_events', 'live', 'Events', '', '{"route":"admin_default","module":"live","controller":"events"}', 'live_admin_main', '', 1, 0, 13),
('live_admin_main_automod', 'live', 'Auto Moderation', '', '{"route":"admin_default","module":"live","controller":"auto-moderation"}', 'live_admin_main', '', 1, 0, 14),
('live_admin_main_notifications', 'live', 'Notifications', '', '{"route":"admin_default","module":"live","controller":"notifications"}', 'live_admin_main', '', 1, 0, 15),
('live_admin_main_access', 'live', 'Access', '', '{"route":"admin_default","module":"live","controller":"access"}', 'live_admin_main', '', 1, 0, 16);

UPDATE `engine4_core_menuitems`
SET `enabled` = 1, `order` = 20
WHERE `name` IN ('einstaclone_mini_explore', 'eticktokclone_main_tiktokclone');

UPDATE `engine4_core_menuitems`
SET `enabled` = 1, `order` = 21
WHERE `name` IN ('core_main_live', 'core_mini_live');

INSERT IGNORE INTO `engine4_core_settings` (`name`, `value`) VALUES
('live.enabled', '1'),
('live.provider', 'agora'),
('live.agora.appid', ''),
('live.agora.certificate', ''),
('live.agora.webhook.secret', ''),
('live.agora.sdk.url', 'https://download.agora.io/sdk/release/AgoraRTC_N.js'),
('live.livekit.url', ''),
('live.livekit.apikey', ''),
('live.livekit.secret', ''),
('live.livekit.webhook.secret', ''),
('live.livekit.sdk.url', 'https://cdn.jsdelivr.net/npm/livekit-client/dist/livekit-client.umd.min.js'),
('live.sfu.webhook.secret', ''),
('live.websocket.endpoint', ''),
('live.websocket.publish.endpoint', ''),
('live.websocket.secret', ''),
('live.lottie.sdk.url', ''),
('live.svga.sdk.url', ''),
('live.default.privacy', 'everyone'),
('live.default.quality', 'auto'),
('live.max.duration', '14400'),
('live.session.timeout', '90'),
('live.maintenance.batch', '200'),
('live.allow.comments', '1'),
('live.allow.gifts', '1'),
('live.allow.clash', '1'),
('live.rate.create.hour', '12'),
('live.rate.join.minute', '60'),
('live.rate.token.minute', '120'),
('live.rate.purchase.hour', '20'),
('live.rate.notify.hour', '3'),
('live.rate.schedule.hour', '20'),
('live.rate.clip.hour', '30'),
('live.clash.duration.seconds', '300'),
('live.clash.boost.duration.seconds', '30'),
('live.clash.boost.interval.seconds', '120'),
('live.clash.boost.random.min.seconds', '60'),
('live.clash.boost.random.max.seconds', '120'),
('live.clash.boost.multiplier', '2'),
('live.auto.clips.enabled', '1'),
('live.auto.clips.max.per.replay', '3'),
('live.auto.clips.default.duration', '30'),
('live.auto.clips.min.score', '8'),
('live.auto.clips.auto.publish', '0'),
('live.rate.sponsored.hour', '10'),
('live.schedule.reminder.minutes', '30'),
('live.schedule.reminder.batch', '250'),
('live.rate.comment.minute', '30'),
('live.rate.reaction.minute', '120'),
('live.rate.gift.minute', '60'),
('live.rate.api.read.minute', '300'),
('live.rate.api.write.minute', '120'),
('live.rate.api.webhook.minute', '240'),
('live.rate.invite.five_minutes', '30'),
('live.rate.clash.request.hour', '20'),
('live.rate.clash.vote.minute', '30'),
('live.rate.room_speaker.request.minute', '10'),
('live.rate.room_speaker.invite.minute', '20'),
('live.replay.enabled', '1'),
('live.sponsored.enabled', '1'),
('live.audio.enabled', '1'),
('live.audio.max.speakers', '8'),
('live.ai.moderation.enabled', '1'),
('live.ai.moderation.provider', 'local'),
('live.ai.moderation.endpoint', ''),
('live.ai.moderation.secret', ''),
('live.ai.moderation.threshold', '0.80'),
('live.push.enabled', '1'),
('live.push.provider', 'local'),
('live.push.endpoint', ''),
('live.push.secret', ''),
('live.push.batch', '250'),
('live.push.retention.days', '90'),
('live.access.enabled', '1'),
('live.access.default.duration.days', '30'),
('live.access.platform.fee.percent', '20'),
('live.notify.followers.enabled', '1'),
('live.notify.followers.limit', '5000'),
('live.notify.batch.limit', '250'),
('live.coin.conversion.rate', '0.01'),
('live.stripe.enabled', '0'),
('live.stripe.publishable.key', ''),
('live.stripe.secret.key', ''),
('live.stripe.webhook.secret', ''),
('live.paypal.enabled', '0'),
('live.paypal.mode', 'sandbox'),
('live.paypal.client.id', ''),
('live.paypal.client.secret', ''),
('live.paypal.webhook.id', '');

INSERT IGNORE INTO `engine4_core_jobtypes` (`title`, `type`, `module`, `plugin`, `priority`) VALUES
('Live Maintenance', 'live_maintenance', 'live', 'Live_Plugin_Job_EndStaleStreams', 70),
('Notify Live Followers', 'live_notify_followers', 'live', 'Live_Plugin_Job_NotifyFollowers', 80),
('Dispatch Live Push Notifications', 'live_push_dispatch', 'live', 'Live_Plugin_Job_DispatchPush', 85),
('Generate Live Auto Clips', 'live_auto_clips', 'live', 'Live_Plugin_Job_AutoClips', 86);

INSERT IGNORE INTO `engine4_activity_actiontypes` (`type`, `module`, `body`, `enabled`, `displayable`, `attachable`, `commentable`, `shareable`, `is_generated`) VALUES
('live_stream_started', 'live', '{item:$subject} is live now:', 1, 5, 1, 3, 1, 0);

INSERT IGNORE INTO `engine4_activity_notificationtypes` (`type`, `module`, `body`, `is_request`, `handler`) VALUES
('live_started', 'live', '{item:$subject} is live now: {item:$object}.', 0, ''),
('live_invite', 'live', '{item:$subject} invited you to join {item:$object}.', 1, ''),
('live_invite_accepted', 'live', '{item:$subject} accepted your invite to {item:$object}.', 0, ''),
('live_invite_declined', 'live', '{item:$subject} declined your invite to {item:$object}.', 0, ''),
('live_scheduled_reminder', 'live', '{item:$object} starts soon.', 0, '');

INSERT IGNORE INTO `engine4_core_mailtemplates` (`type`, `module`, `vars`) VALUES
('notify_live_started', 'live', '[host],[email],[recipient_title],[recipient_link],[recipient_photo],[sender_title],[sender_link],[sender_photo],[object_title],[object_link],[object_photo],[object_description],[live_title],[live_link]'),
('notify_live_invite', 'live', '[host],[email],[recipient_title],[recipient_link],[recipient_photo],[sender_title],[sender_link],[sender_photo],[object_title],[object_link],[object_photo],[object_description],[live_title],[live_link]'),
('notify_live_invite_accepted', 'live', '[host],[email],[recipient_title],[recipient_link],[recipient_photo],[sender_title],[sender_link],[sender_photo],[object_title],[object_link],[object_photo],[object_description],[live_title],[live_link]'),
('notify_live_invite_declined', 'live', '[host],[email],[recipient_title],[recipient_link],[recipient_photo],[sender_title],[sender_link],[sender_photo],[object_title],[object_link],[object_photo],[object_description],[live_title],[live_link]'),
('notify_live_scheduled_reminder', 'live', '[host],[email],[recipient_title],[recipient_link],[recipient_photo],[sender_title],[sender_link],[sender_photo],[object_title],[object_link],[object_photo],[object_description],[live_title],[live_link],[scheduled_at]');

CREATE TABLE IF NOT EXISTS `engine4_live_categories` (
  `category_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `slug` varchar(128) NOT NULL,
  `description` text NULL,
  `icon_file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `order` int(11) NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `enabled_order` (`enabled`, `order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_streams` (
  `stream_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `host_id` int(11) unsigned NOT NULL,
  `category_id` int(11) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL,
  `description` text NULL,
  `hashtags` varchar(512) DEFAULT NULL,
  `cover_file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `status` varchar(32) NOT NULL DEFAULT 'draft',
  `privacy` varchar(32) NOT NULL DEFAULT 'everyone',
  `content_mode` varchar(32) NOT NULL DEFAULT 'video',
  `audio_only` tinyint(1) NOT NULL DEFAULT '0',
  `quality_profile` varchar(32) NOT NULL DEFAULT 'auto',
  `provider` varchar(32) NOT NULL DEFAULT 'agora',
  `provider_channel` varchar(128) DEFAULT NULL,
  `stream_key_hash` varchar(255) DEFAULT NULL,
  `allow_comments` tinyint(1) NOT NULL DEFAULT '1',
  `allow_gifts` tinyint(1) NOT NULL DEFAULT '1',
  `allow_clash` tinyint(1) NOT NULL DEFAULT '1',
  `allow_moderators` tinyint(1) NOT NULL DEFAULT '1',
  `allow_speaker_requests` tinyint(1) NOT NULL DEFAULT '1',
  `max_speakers` smallint(5) unsigned NOT NULL DEFAULT '8',
  `speaker_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `is_recommended` tinyint(1) NOT NULL DEFAULT '0',
  `viewer_count` int(11) unsigned NOT NULL DEFAULT '0',
  `peak_viewer_count` int(11) unsigned NOT NULL DEFAULT '0',
  `gift_count` int(11) unsigned NOT NULL DEFAULT '0',
  `gift_coin_total` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_count` int(11) unsigned NOT NULL DEFAULT '0',
  `reaction_count` int(11) unsigned NOT NULL DEFAULT '0',
  `country` varchar(64) DEFAULT NULL,
  `lat` decimal(10,7) DEFAULT NULL,
  `lng` decimal(10,7) DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `ended_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`stream_id`),
  KEY `host_status` (`host_id`, `status`),
  KEY `category_status` (`category_id`, `status`),
  KEY `status_started` (`status`, `started_at`),
  KEY `mode_status` (`content_mode`, `status`),
  KEY `featured_status` (`is_featured`, `status`),
  KEY `country_status` (`country`, `status`),
  KEY `status_popular` (`status`, `viewer_count`, `stream_id`),
  KEY `status_gifts` (`status`, `gift_coin_total`, `viewer_count`),
  KEY `status_recommended` (`status`, `is_recommended`, `viewer_count`),
  KEY `geo_status` (`status`, `lat`, `lng`),
  KEY `provider_channel` (`provider`, `provider_channel`, `stream_id`),
  FULLTEXT KEY `search` (`title`, `description`, `hashtags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_sessions` (
  `session_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `role` varchar(32) NOT NULL DEFAULT 'viewer',
  `platform` varchar(32) DEFAULT NULL,
  `device_id` varchar(128) DEFAULT NULL,
  `provider_uid` varchar(128) DEFAULT NULL,
  `token_hash` varchar(255) DEFAULT NULL,
  `ip` varchar(64) DEFAULT NULL,
  `public_visible` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `joined_at` datetime NOT NULL,
  `left_at` datetime DEFAULT NULL,
  `last_ping_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  PRIMARY KEY (`session_id`),
  KEY `stream_role` (`stream_id`, `role`),
  KEY `stream_role_active` (`stream_id`, `role`, `left_at`, `last_ping_at`),
  KEY `stream_public_active` (`stream_id`, `public_visible`, `role`, `left_at`, `last_ping_at`),
  KEY `user_stream` (`user_id`, `stream_id`),
  KEY `last_ping` (`last_ping_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_viewers` (
  `viewer_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `session_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `platform` varchar(32) DEFAULT NULL,
  `public_visible` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `joined_at` datetime NOT NULL,
  `left_at` datetime DEFAULT NULL,
  `last_seen_at` datetime DEFAULT NULL,
  PRIMARY KEY (`viewer_id`),
  KEY `stream_seen` (`stream_id`, `last_seen_at`),
  KEY `stream_public_seen` (`stream_id`, `public_visible`, `last_seen_at`),
  KEY `user_stream` (`user_id`, `stream_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_room_speakers` (
  `speaker_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `invited_by` int(11) unsigned NOT NULL DEFAULT '0',
  `role` varchar(32) NOT NULL DEFAULT 'speaker',
  `status` varchar(32) NOT NULL DEFAULT 'requested',
  `muted` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int(11) NOT NULL DEFAULT '0',
  `joined_at` datetime DEFAULT NULL,
  `left_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`speaker_id`),
  UNIQUE KEY `stream_user` (`stream_id`, `user_id`),
  KEY `stream_status` (`stream_id`, `status`),
  KEY `user_status` (`user_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_comments` (
  `comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `body` text NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'visible',
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `stream_created` (`stream_id`, `creation_date`),
  KEY `stream_status_created` (`stream_id`, `status`, `creation_date`, `comment_id`),
  KEY `user_created` (`user_id`, `creation_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_reactions` (
  `reaction_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `reaction_type` varchar(32) NOT NULL DEFAULT 'like',
  `quantity` int(11) unsigned NOT NULL DEFAULT '1',
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`reaction_id`),
  KEY `stream_created` (`stream_id`, `creation_date`),
  KEY `reaction_type` (`reaction_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_rate_limits` (
  `rate_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `action` varchar(64) NOT NULL,
  `key_hash` char(64) NOT NULL,
  `counter` int(11) unsigned NOT NULL DEFAULT '0',
  `reset_at` datetime NOT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`rate_id`),
  UNIQUE KEY `action_key` (`action`, `key_hash`),
  KEY `reset_at` (`reset_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_gifts` (
  `gift_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `description` text NULL,
  `icon_file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `animation_file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `coin_price` int(11) unsigned NOT NULL DEFAULT '1',
  `score_value` int(11) unsigned NOT NULL DEFAULT '1',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `order` int(11) NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`gift_id`),
  UNIQUE KEY `title` (`title`),
  KEY `enabled_order` (`enabled`, `order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_gift_sends` (
  `send_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `gift_id` int(11) unsigned NOT NULL,
  `sender_id` int(11) unsigned NOT NULL,
  `receiver_id` int(11) unsigned NOT NULL,
  `clash_id` int(11) unsigned NOT NULL DEFAULT '0',
  `quantity` int(11) unsigned NOT NULL DEFAULT '1',
  `coins` bigint(20) unsigned NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`send_id`),
  KEY `stream_created` (`stream_id`, `creation_date`),
  KEY `sender_created` (`sender_id`, `creation_date`),
  KEY `receiver_created` (`receiver_id`, `creation_date`),
  KEY `created_gift` (`creation_date`, `gift_id`),
  KEY `clash_id` (`clash_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_wallets` (
  `wallet_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `coin_balance` bigint(20) NOT NULL DEFAULT '0',
  `diamond_balance` bigint(20) NOT NULL DEFAULT '0',
  `pending_balance` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `currency` varchar(8) NOT NULL DEFAULT 'USD',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`wallet_id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_transactions` (
  `transaction_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `type` varchar(32) NOT NULL,
  `coins` bigint(20) NOT NULL DEFAULT '0',
  `diamonds` bigint(20) NOT NULL DEFAULT '0',
  `amount` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `currency` varchar(8) NOT NULL DEFAULT 'USD',
  `gateway` varchar(32) DEFAULT NULL,
  `gateway_reference` varchar(255) DEFAULT NULL,
  `gateway_payload` mediumtext NULL,
  `payment_order_id` int(11) unsigned NOT NULL DEFAULT '0',
  `source_type` varchar(64) DEFAULT NULL,
  `source_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `status` varchar(32) NOT NULL DEFAULT 'completed',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`transaction_id`),
  KEY `user_created` (`user_id`, `creation_date`),
  KEY `gateway_reference` (`gateway`, `gateway_reference`),
  KEY `source` (`source_type`, `source_id`),
  KEY `payment_order_id` (`payment_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_coin_packages` (
  `package_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `coins` int(11) unsigned NOT NULL,
  `bonus_coins` int(11) unsigned NOT NULL DEFAULT '0',
  `price` decimal(16,4) NOT NULL,
  `currency` varchar(8) NOT NULL DEFAULT 'USD',
  `gateway_sku` varchar(128) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`package_id`),
  UNIQUE KEY `title` (`title`),
  KEY `enabled_order` (`enabled`, `order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_clashes` (
  `clash_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(32) NOT NULL DEFAULT 'pending',
  `stream_id_a` int(11) unsigned NOT NULL,
  `stream_id_b` int(11) unsigned NOT NULL DEFAULT '0',
  `score_a` bigint(20) unsigned NOT NULL DEFAULT '0',
  `score_b` bigint(20) unsigned NOT NULL DEFAULT '0',
  `winner_user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `mvp_user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `started_at` datetime DEFAULT NULL,
  `ends_at` datetime DEFAULT NULL,
  `ended_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `duration_seconds` int(11) unsigned NOT NULL DEFAULT '300',
  `boost_multiplier` int(11) unsigned NOT NULL DEFAULT '2',
  `boost_window_seconds` int(11) unsigned NOT NULL DEFAULT '30',
  `boost_until` datetime DEFAULT NULL,
  `next_boost_at` datetime DEFAULT NULL,
  PRIMARY KEY (`clash_id`),
  KEY `status_ends` (`status`, `ends_at`),
  KEY `streams` (`stream_id_a`, `stream_id_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_clash_participants` (
  `participant_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `clash_id` int(11) unsigned NOT NULL,
  `stream_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `team` varchar(16) NOT NULL DEFAULT 'a',
  `score` bigint(20) unsigned NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`participant_id`),
  KEY `clash_team` (`clash_id`, `team`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_clash_votes` (
  `vote_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `clash_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `team` varchar(16) NOT NULL,
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`vote_id`),
  UNIQUE KEY `one_vote` (`clash_id`, `user_id`),
  KEY `clash_team` (`clash_id`, `team`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_moderators` (
  `moderator_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `user_id` int(11) unsigned NOT NULL,
  `added_by` int(11) unsigned NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`moderator_id`),
  UNIQUE KEY `stream_user` (`stream_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_bans` (
  `ban_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `user_id` int(11) unsigned NOT NULL,
  `moderator_id` int(11) unsigned NOT NULL DEFAULT '0',
  `type` varchar(32) NOT NULL DEFAULT 'mute',
  `reason` varchar(255) DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`ban_id`),
  KEY `stream_user` (`stream_id`, `user_id`),
  KEY `expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_moderation_rules` (
  `rule_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `description` text NULL,
  `rule_type` varchar(32) NOT NULL DEFAULT 'keyword',
  `target` varchar(32) NOT NULL DEFAULT 'all',
  `pattern` text NOT NULL,
  `severity` varchar(32) NOT NULL DEFAULT 'medium',
  `action` varchar(32) NOT NULL DEFAULT 'flag',
  `ban_seconds` int(11) unsigned NOT NULL DEFAULT '0',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `order` int(11) NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`rule_id`),
  KEY `enabled_order` (`enabled`, `order`),
  KEY `target_enabled` (`target`, `enabled`),
  KEY `action_enabled` (`action`, `enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_moderation_events` (
  `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `rule_id` int(11) unsigned NOT NULL DEFAULT '0',
  `report_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `subject_type` varchar(64) NOT NULL DEFAULT 'live_stream',
  `subject_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `target` varchar(32) NOT NULL DEFAULT 'comment',
  `source` varchar(32) NOT NULL DEFAULT 'local_rule',
  `provider` varchar(64) DEFAULT NULL,
  `severity` varchar(32) NOT NULL DEFAULT 'medium',
  `action` varchar(32) NOT NULL DEFAULT 'flag',
  `confidence` decimal(5,4) NOT NULL DEFAULT '1.0000',
  `status` varchar(32) NOT NULL DEFAULT 'detected',
  `matched_text` varchar(255) DEFAULT NULL,
  `payload` mediumtext NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`),
  KEY `stream_created` (`stream_id`, `creation_date`),
  KEY `user_created` (`user_id`, `creation_date`),
  KEY `status_created` (`status`, `creation_date`),
  KEY `rule_created` (`rule_id`, `creation_date`),
  KEY `target_created` (`target`, `creation_date`),
  KEY `source_created` (`source`, `creation_date`),
  KEY `subject` (`subject_type`, `subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_reports` (
  `report_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `reporter_id` int(11) unsigned NOT NULL,
  `subject_type` varchar(64) NOT NULL DEFAULT 'live_stream',
  `subject_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `reason` varchar(128) NOT NULL,
  `body` text NULL,
  `status` varchar(32) NOT NULL DEFAULT 'open',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`report_id`),
  KEY `status_created` (`status`, `creation_date`),
  KEY `stream_id` (`stream_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `engine4_live_categories` (`title`, `slug`, `description`, `enabled`, `order`, `creation_date`, `modified_date`) VALUES
('Gaming', 'gaming', 'Gameplay, esports and creator sessions.', 1, 1, NOW(), NOW()),
('Music', 'music', 'Live music, DJ sets and listening sessions.', 1, 2, NOW(), NOW()),
('Talk Shows', 'talk-shows', 'Creator conversations and live interviews.', 1, 3, NOW(), NOW()),
('Sports', 'sports', 'Sports reactions, training and fan rooms.', 1, 4, NOW(), NOW()),
('Learning', 'learning', 'Tutorials, workshops and skill sessions.', 1, 5, NOW(), NOW());

INSERT IGNORE INTO `engine4_live_gifts` (`title`, `description`, `coin_price`, `score_value`, `enabled`, `order`, `creation_date`, `modified_date`) VALUES
('Spark', 'A quick animated boost for the creator.', 5, 5, 1, 1, NOW(), NOW()),
('Crown', 'A premium gift for standout moments.', 50, 60, 1, 2, NOW(), NOW()),
('Galaxy', 'A high impact gift for Live Clash scoring.', 250, 320, 1, 3, NOW(), NOW());

INSERT IGNORE INTO `engine4_live_coin_packages` (`title`, `coins`, `bonus_coins`, `price`, `currency`, `enabled`, `order`) VALUES
('Starter Coins', 100, 0, 0.9900, 'USD', 1, 1),
('Creator Boost', 550, 50, 4.9900, 'USD', 1, 2),
('Clash Pack', 1200, 200, 9.9900, 'USD', 1, 3);

CREATE TABLE IF NOT EXISTS `engine4_live_invites` (
  `invite_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `sender_id` int(11) unsigned NOT NULL,
  `recipient_id` int(11) unsigned NOT NULL,
  `type` varchar(32) NOT NULL DEFAULT 'guest',
  `status` varchar(32) NOT NULL DEFAULT 'pending',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`invite_id`),
  KEY `recipient_status` (`recipient_id`, `status`),
  KEY `stream_recipient_status` (`stream_id`, `recipient_id`, `status`),
  KEY `stream_type` (`stream_id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_notifications` (
  `notification_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `activity_notification_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `recipient_id` int(11) unsigned NOT NULL,
  `sender_id` int(11) unsigned NOT NULL DEFAULT '0',
  `object_type` varchar(64) NOT NULL DEFAULT '',
  `object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `type` varchar(64) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` varchar(512) DEFAULT NULL,
  `href` varchar(1024) DEFAULT NULL,
  `payload` mediumtext NULL,
  `status` varchar(32) NOT NULL DEFAULT 'unread',
  `delivery_status` varchar(32) NOT NULL DEFAULT 'queued',
  `delivery_attempts` smallint(5) unsigned NOT NULL DEFAULT '0',
  `last_error` varchar(255) DEFAULT NULL,
  `scheduled_at` datetime DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `read_at` datetime DEFAULT NULL,
  `dismissed_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`notification_id`),
  KEY `recipient_status` (`recipient_id`, `status`, `notification_id`),
  KEY `delivery_status` (`delivery_status`, `scheduled_at`),
  KEY `queue_dispatch` (`delivery_status`, `delivery_attempts`, `scheduled_at`, `notification_id`),
  KEY `type_created` (`type`, `creation_date`),
  KEY `object` (`object_type`, `object_id`),
  KEY `activity_notification_id` (`activity_notification_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_replays` (
  `replay_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL,
  `host_id` int(11) unsigned NOT NULL,
  `file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `external_url` varchar(1024) DEFAULT NULL,
  `duration` int(11) unsigned NOT NULL DEFAULT '0',
  `status` varchar(32) NOT NULL DEFAULT 'processing',
  `auto_clip_status` varchar(32) NOT NULL DEFAULT 'pending',
  `auto_clip_checked_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`replay_id`),
  KEY `stream_id` (`stream_id`),
  KEY `stream_replay` (`stream_id`, `replay_id`),
  KEY `auto_clip_queue` (`status`, `auto_clip_status`, `replay_id`),
  KEY `host_status` (`host_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_clips` (
  `clip_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `replay_id` int(11) unsigned NOT NULL DEFAULT '0',
  `host_id` int(11) unsigned NOT NULL,
  `creator_id` int(11) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL,
  `description` text NULL,
  `external_url` varchar(1024) DEFAULT NULL,
  `file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `thumbnail_file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `start_offset` int(11) unsigned NOT NULL DEFAULT '0',
  `duration` int(11) unsigned NOT NULL DEFAULT '30',
  `source` varchar(32) NOT NULL DEFAULT 'manual',
  `auto_score` int(11) unsigned NOT NULL DEFAULT '0',
  `auto_reason` varchar(128) DEFAULT NULL,
  `auto_metadata` mediumtext NULL,
  `auto_generated_at` datetime DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'published',
  `privacy` varchar(32) NOT NULL DEFAULT 'everyone',
  `view_count` int(11) unsigned NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`clip_id`),
  KEY `replay_status` (`replay_id`, `status`),
  KEY `stream_status` (`stream_id`, `status`),
  KEY `host_status` (`host_id`, `status`),
  KEY `source_status` (`source`, `status`),
  KEY `replay_source_offset` (`replay_id`, `source`, `start_offset`),
  KEY `auto_score` (`source`, `auto_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_captions` (
  `caption_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `resource_type` varchar(32) NOT NULL,
  `resource_id` int(11) unsigned NOT NULL,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `replay_id` int(11) unsigned NOT NULL DEFAULT '0',
  `clip_id` int(11) unsigned NOT NULL DEFAULT '0',
  `host_id` int(11) unsigned NOT NULL,
  `creator_id` int(11) unsigned NOT NULL DEFAULT '0',
  `language` varchar(16) NOT NULL DEFAULT 'en',
  `kind` varchar(32) NOT NULL DEFAULT 'subtitles',
  `source` varchar(32) NOT NULL DEFAULT 'manual',
  `format` varchar(16) NOT NULL DEFAULT 'vtt',
  `status` varchar(32) NOT NULL DEFAULT 'ready',
  `external_url` varchar(1024) DEFAULT NULL,
  `content` mediumtext NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`caption_id`),
  KEY `resource_status` (`resource_type`, `resource_id`, `status`),
  KEY `stream_status` (`stream_id`, `status`),
  KEY `replay_status` (`replay_id`, `status`),
  KEY `clip_status` (`clip_id`, `status`),
  KEY `language_status` (`language`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_sponsored_campaigns` (
  `campaign_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) unsigned NOT NULL,
  `resource_type` varchar(32) NOT NULL,
  `resource_id` int(11) unsigned NOT NULL,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `replay_id` int(11) unsigned NOT NULL DEFAULT '0',
  `clip_id` int(11) unsigned NOT NULL DEFAULT '0',
  `schedule_id` int(11) unsigned NOT NULL DEFAULT '0',
  `host_id` int(11) unsigned NOT NULL DEFAULT '0',
  `placement` varchar(64) NOT NULL DEFAULT 'home',
  `title` varchar(255) NOT NULL,
  `description` text NULL,
  `cta_label` varchar(64) DEFAULT NULL,
  `cta_url` varchar(1024) DEFAULT NULL,
  `creative_file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `budget_amount` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `daily_cap` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `currency` varchar(8) NOT NULL DEFAULT 'USD',
  `priority` int(11) NOT NULL DEFAULT '0',
  `impression_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `click_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `status` varchar(32) NOT NULL DEFAULT 'draft',
  `starts_at` datetime DEFAULT NULL,
  `ends_at` datetime DEFAULT NULL,
  `approved_by` int(11) unsigned NOT NULL DEFAULT '0',
  `approved_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`campaign_id`),
  KEY `owner_status` (`owner_id`, `status`),
  KEY `resource_status` (`resource_type`, `resource_id`, `status`),
  KEY `placement_status` (`placement`, `status`),
  KEY `status_dates` (`status`, `starts_at`, `ends_at`),
  KEY `priority_status` (`priority`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_scheduled_events` (
  `schedule_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `host_id` int(11) unsigned NOT NULL,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `category_id` int(11) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL,
  `description` text NULL,
  `hashtags` varchar(512) DEFAULT NULL,
  `cover_file_id` int(11) unsigned NOT NULL DEFAULT '0',
  `privacy` varchar(32) NOT NULL DEFAULT 'everyone',
  `content_mode` varchar(32) NOT NULL DEFAULT 'video',
  `audio_only` tinyint(1) NOT NULL DEFAULT '0',
  `quality_profile` varchar(32) NOT NULL DEFAULT 'auto',
  `scheduled_at` datetime NOT NULL,
  `timezone` varchar(64) DEFAULT NULL,
  `duration` int(11) unsigned NOT NULL DEFAULT '3600',
  `status` varchar(32) NOT NULL DEFAULT 'planned',
  `allow_reminders` tinyint(1) NOT NULL DEFAULT '1',
  `allow_speaker_requests` tinyint(1) NOT NULL DEFAULT '1',
  `max_speakers` smallint(5) unsigned NOT NULL DEFAULT '8',
  `subscriber_count` int(11) unsigned NOT NULL DEFAULT '0',
  `country` varchar(64) DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`schedule_id`),
  KEY `host_status` (`host_id`, `status`),
  KEY `status_scheduled` (`status`, `scheduled_at`),
  KEY `mode_status` (`content_mode`, `status`),
  KEY `category_status` (`category_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_schedule_subscriptions` (
  `subscription_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `schedule_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'subscribed',
  `reminder_sent` tinyint(1) NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`subscription_id`),
  UNIQUE KEY `schedule_user` (`schedule_id`, `user_id`),
  KEY `user_status` (`user_id`, `status`),
  KEY `reminder_status` (`status`, `reminder_sent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_access_passes` (
  `pass_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `resource_type` varchar(32) NOT NULL,
  `resource_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `granted_by` int(11) unsigned NOT NULL DEFAULT '0',
  `source` varchar(32) NOT NULL DEFAULT 'manual',
  `status` varchar(32) NOT NULL DEFAULT 'active',
  `expires_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`pass_id`),
  UNIQUE KEY `resource_user` (`resource_type`, `resource_id`, `user_id`),
  KEY `user_status` (`user_id`, `status`),
  KEY `resource_status` (`resource_type`, `resource_id`, `status`),
  KEY `expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_access_plans` (
  `plan_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) unsigned NOT NULL,
  `resource_type` varchar(32) NOT NULL,
  `resource_id` int(11) unsigned NOT NULL,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `clip_id` int(11) unsigned NOT NULL DEFAULT '0',
  `schedule_id` int(11) unsigned NOT NULL DEFAULT '0',
  `host_id` int(11) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL,
  `description` text NULL,
  `duration_days` int(11) unsigned NOT NULL DEFAULT '30',
  `payment_mode` varchar(32) NOT NULL DEFAULT 'coins',
  `coin_price` int(11) unsigned NOT NULL DEFAULT '0',
  `amount` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `currency` varchar(8) NOT NULL DEFAULT 'USD',
  `purchase_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  `revenue_coins` bigint(20) unsigned NOT NULL DEFAULT '0',
  `revenue_amount` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `status` varchar(32) NOT NULL DEFAULT 'draft',
  `starts_at` datetime DEFAULT NULL,
  `ends_at` datetime DEFAULT NULL,
  `creation_date` datetime NOT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`plan_id`),
  KEY `resource_status` (`resource_type`, `resource_id`, `status`),
  KEY `owner_status` (`owner_id`, `status`),
  KEY `host_status` (`host_id`, `status`),
  KEY `status_dates` (`status`, `starts_at`, `ends_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `engine4_live_provider_events` (
  `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `provider` varchar(32) NOT NULL,
  `event_type` varchar(128) NOT NULL,
  `stream_id` int(11) unsigned NOT NULL DEFAULT '0',
  `payload` mediumtext NULL,
  `signature_valid` tinyint(1) NOT NULL DEFAULT '0',
  `processed` tinyint(1) NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL,
  PRIMARY KEY (`event_id`),
  KEY `provider_type` (`provider`, `event_type`),
  KEY `provider_stream` (`provider`, `stream_id`, `event_id`),
  KEY `stream_id` (`stream_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
