-- Rebuilt SQL schema for the `speedots` database

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_sync` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `posts`
--

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;

--
-- Table structure for table `published_games`
--

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;

--
-- Table structure for table `receipts`
--

CREATE TABLE IF NOT EXISTS `receipts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_token` varchar(64) NOT NULL,
  `game_id` int(11) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `game_title` varchar(255) DEFAULT NULL,
  `game_developer` varchar(255) DEFAULT NULL,
  `amount_paid` decimal(10,2) DEFAULT 0.00,
  `is_free` tinyint(1) DEFAULT 0,
  `transaction_id` varchar(255) DEFAULT NULL,
  `claimed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT NULL,
  `status` varchar(50) DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipt_token` (`receipt_token`),
  KEY `idx_receipt_token` (`receipt_token`),
  KEY `idx_user_email` (`user_email`),
  KEY `idx_game_id` (`game_id`),
  KEY `idx_status` (`status`),
  KEY `idx_claimed_at` (`claimed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracks all game purchases with unique receipt tokens';

--
-- Table structure for table `purchases`
--

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';

--
-- Table structure for table `game_sales`
--

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;
