

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `item_type` varchar(50) DEFAULT NULL,
  `author` varchar(255) DEFAULT 'Admin',
  `views` int(11) DEFAULT 0,
  `downloads` int(11) DEFAULT 0,
  `hours_played` int(11) DEFAULT 0,
  `copies_sold` int(11) DEFAULT 0,
  `price` decimal(10,2) DEFAULT 0.00,
  `reserves_price` int(11) DEFAULT 0,
  `can_purchase` tinyint(1) DEFAULT 0,
  `posted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_bumped` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_sync` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`,`item_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `published_games` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `title` text NOT NULL,
  `developer` text NOT NULL,
  `price` float DEFAULT 0,
  `description` text DEFAULT NULL,
  `category` text DEFAULT NULL,
  `content_type` varchar(20) DEFAULT 'game',
  `executable_path` text DEFAULT NULL,
  `screenshot_path` text DEFAULT NULL,
  `assets_path` text DEFAULT NULL,
  `requirements` text DEFAULT NULL,
  `status` text DEFAULT 'pending',
  `downloads` int(11) DEFAULT 0,
  `rating` float DEFAULT 0,
  `published_date` text DEFAULT NULL,
  `submitted_date` text DEFAULT NULL,
  `server_id` text DEFAULT NULL,
  `download_url` text DEFAULT NULL,
  `vendor_paypal_email` varchar(255) DEFAULT NULL,
  `vendor_payout_percentage` decimal(5,2) DEFAULT 85.00,
  `platform_fee_percentage` decimal(5,2) DEFAULT 15.00,
  `image_path` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `idx_content_type` (`content_type`),
  CONSTRAINT `published_games_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `buyer_email` varchar(255) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `vendor_amount` decimal(10,2) NOT NULL,
  `platform_fee` decimal(10,2) NOT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'completed',
  `purchased_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_game_id` (`game_id`),
  KEY `idx_buyer_email` (`buyer_email`),
  KEY `idx_status` (`status`),
  KEY `idx_purchased_at` (`purchased_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Detailed purchase transaction records';

CREATE TABLE IF NOT EXISTS `game_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) DEFAULT NULL,
  `buyer_email` text DEFAULT NULL,
  `sale_price` float DEFAULT NULL,
  `commission` float DEFAULT NULL,
  `net_earnings` float DEFAULT NULL,
  `sale_date` text DEFAULT NULL,
  `payment_status` text DEFAULT 'completed',
  `paypal_transaction_id` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id` (`game_id`),
  CONSTRAINT `game_sales_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `published_games` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- From rebuilt_messaging_schema.sql
CREATE TABLE IF NOT EXISTS `online_clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `user_email` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `connected_at` datetime DEFAULT current_timestamp(),
  `last_seen` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `status` enum('online','offline') DEFAULT 'online',
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_id` (`client_id`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_status` (`status`),
  KEY `idx_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `signaling_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_client_id` varchar(255) NOT NULL,
  `to_client_id` varchar(255) NOT NULL,
  `signal_type` enum('offer','answer','ice_candidate','message') NOT NULL,
  `signal_data` text NOT NULL,
  `is_delivered` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `delivered_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_to_client` (`to_client_id`,`is_delivered`),
  KEY `idx_created` (`created_at`),
  KEY `idx_delivered` (`is_delivered`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `message_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_client_id` varchar(255) NOT NULL,
  `to_client_id` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `is_broadcast` tinyint(1) DEFAULT 0,
  `timestamp` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_from_client` (`from_client_id`),
  KEY `idx_to_client` (`to_client_id`),
  KEY `idx_timestamp` (`timestamp`),
  KEY `idx_broadcast` (`is_broadcast`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- From rebuilt_local_schema.sql (converted to MySQL)
CREATE TABLE IF NOT EXISTS `games` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` TEXT NOT NULL,
  `developer` TEXT DEFAULT NULL,
  `price` DECIMAL(10,2) DEFAULT 0.00,
  `reserves_price` INT(11) DEFAULT 0,
  `owned` TINYINT(1) DEFAULT 0,
  `installed` TINYINT(1) DEFAULT 0,
  `hours_played` FLOAT DEFAULT 0,
  `last_played` TIMESTAMP NULL DEFAULT NULL,
  `trust_score` FLOAT DEFAULT 0,
  `download_count` INT(11) DEFAULT 0,
  `last_sync` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `applications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` TEXT NOT NULL,
  `developer` TEXT DEFAULT NULL,
  `price` DECIMAL(10,2) DEFAULT 0.00,
  `reserves_price` INT(11) DEFAULT 0,
  `owned` TINYINT(1) DEFAULT 0,
  `installed` TINYINT(1) DEFAULT 0,
  `trust_score` FLOAT DEFAULT 0,
  `virus_scan_status` TEXT,
  `download_count` INT(11) DEFAULT 0,
  `last_sync` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `reserves` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) DEFAULT NULL,
  `amount` INT(11) DEFAULT 0,
  `last_transaction` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `achievements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) DEFAULT NULL,
  `game_id` INT(11) DEFAULT NULL,
  `achievement_name` TEXT DEFAULT NULL,
  `unlocked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `comments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `item_id` INT(11) DEFAULT NULL,
  `item_type` TEXT DEFAULT NULL,
  `user_id` INT(11) DEFAULT NULL,
  `comment_text` TEXT DEFAULT NULL,
  `posted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trust_votes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `item_id` INT(11) DEFAULT NULL,
  `item_type` TEXT DEFAULT NULL,
  `user_id` INT(11) DEFAULT NULL,
  `vote_value` INT(11) DEFAULT NULL,
  `voted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `post_media` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `post_id` INT(11) DEFAULT NULL,
  `media_type` TEXT DEFAULT NULL,
  `media_url` TEXT DEFAULT NULL,
  `display_order` INT(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `post_comments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `post_id` INT(11) DEFAULT NULL,
  `user_id` INT(11) DEFAULT NULL,
  `username` TEXT DEFAULT NULL,
  `comment_text` TEXT DEFAULT NULL,
  `posted_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_library` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) DEFAULT NULL,
  `item_id` INT(11) DEFAULT NULL,
  `item_type` TEXT DEFAULT NULL,
  `post_id` INT(11) DEFAULT NULL,
  `install_path` TEXT DEFAULT NULL,
  `installed` TINYINT(1) DEFAULT 0,
  `hours_played` FLOAT DEFAULT 0,
  `last_played` TIMESTAMP NULL DEFAULT NULL,
  `purchased_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `purchase_type` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `play_sessions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) DEFAULT NULL,
  `library_id` INT(11) DEFAULT NULL,
  `started_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ended_at` TIMESTAMP NULL DEFAULT NULL,
  `duration_minutes` FLOAT DEFAULT 0,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_profiles` (
  `user_id` INT(11) NOT NULL,
  `display_name` TEXT DEFAULT NULL,
  `avatar_url` TEXT DEFAULT NULL,
  `bio` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_active` TIMESTAMP NULL DEFAULT NULL,
  `total_playtime` INT(11) DEFAULT 0,
  `level` INT(11) DEFAULT 1,
  `experience` INT(11) DEFAULT 0,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) DEFAULT NULL,
  `activity_type` TEXT DEFAULT NULL,
  `item_id` INT(11) DEFAULT NULL,
  `item_name` TEXT DEFAULT NULL,
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `duration` INT(11) DEFAULT 0,
  `metadata` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `profile_achievements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `icon` TEXT DEFAULT NULL,
  `category` TEXT DEFAULT NULL,
  `points` INT(11) DEFAULT 10,
  `requirement_type` TEXT DEFAULT NULL,
  `requirement_value` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_achievements` (
  `user_id` INT(11) NOT NULL,
  `achievement_id` INT(11) NOT NULL,
  `unlocked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `progress` INT(11) DEFAULT 0,
  PRIMARY KEY (`user_id`, `achievement_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`achievement_id`) REFERENCES `profile_achievements` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_stats` (
  `user_id` INT(11) NOT NULL,
  `games_played` INT(11) DEFAULT 0,
  `apps_launched` INT(11) DEFAULT 0,
  `total_sessions` INT(11) DEFAULT 0,
  `favorite_game_id` INT(11) DEFAULT NULL,
  `favorite_app_id` INT(11) DEFAULT NULL,
  `longest_session` INT(11) DEFAULT 0,
  PRIMARY KEY (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELIMITER $$
CREATE EVENT IF NOT EXISTS `cleanup_messaging_data`
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    -- Mark clients offline if not seen in 2 minutes
    UPDATE `online_clients`
    SET `status` = 'offline'
    WHERE `last_seen` < DATE_SUB(NOW(), INTERVAL 2 MINUTE)
    AND `status` = 'online';

    -- Delete offline clients after 5 minutes
    DELETE FROM `online_clients`
    WHERE `last_seen` < DATE_SUB(NOW(), INTERVAL 5 MINUTE)
    AND `status` = 'offline';

    -- Delete old delivered signals (older than 1 hour)
    DELETE FROM `signaling_queue`
    WHERE `is_delivered` = TRUE
    AND `delivered_at` < DATE_SUB(NOW(), INTERVAL 1 HOUR);

    -- Delete old message history (older than 7 days)
    DELETE FROM `message_history`
    WHERE `timestamp` < DATE_SUB(NOW(), INTERVAL 7 DAY);
END$$
DELIMITER ;

SET GLOBAL event_scheduler = ON;
