-- Rebuilt SQL schema for the local `speedots_local.db` SQLite database

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

CREATE TABLE IF NOT EXISTS `users` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `username` text NOT NULL UNIQUE,
  `password_hash` text NOT NULL,
  `email` text UNIQUE,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_sync` timestamp NOT NULL DEFAULT current_timestamp()
);

--
-- Table structure for table `games`
--

CREATE TABLE IF NOT EXISTS `games` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `title` text NOT NULL,
  `developer` text DEFAULT NULL,
  `price` real DEFAULT 0,
  `reserves_price` integer DEFAULT 0,
  `owned` tinyint(1) DEFAULT 0,
  `installed` tinyint(1) DEFAULT 0,
  `hours_played` real DEFAULT 0,
  `last_played` timestamp NULL DEFAULT NULL,
  `trust_score` real DEFAULT 0,
  `download_count` integer DEFAULT 0,
  `last_sync` timestamp NOT NULL DEFAULT current_timestamp()
);

--
-- Table structure for table `applications`
--

CREATE TABLE IF NOT EXISTS `applications` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `title` text NOT NULL,
  `developer` text DEFAULT NULL,
  `price` real DEFAULT 0,
  `reserves_price` integer DEFAULT 0,
  `owned` tinyint(1) DEFAULT 0,
  `installed` tinyint(1) DEFAULT 0,
  `trust_score` real DEFAULT 0,
  `virus_scan_status` text DEFAULT 'pending',
  `download_count` integer DEFAULT 0,
  `last_sync` timestamp NOT NULL DEFAULT current_timestamp()
);

--
-- Table structure for table `reserves`
--

CREATE TABLE IF NOT EXISTS `reserves` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `user_id` integer DEFAULT NULL,
  `amount` integer DEFAULT 0,
  `last_transaction` timestamp NOT NULL DEFAULT current_timestamp(),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

--
-- Table structure for table `achievements`
--

CREATE TABLE IF NOT EXISTS `achievements` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `user_id` integer DEFAULT NULL,
  `game_id` integer DEFAULT NULL,
  `achievement_name` text DEFAULT NULL,
  `unlocked_at` timestamp NOT NULL DEFAULT current_timestamp(),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

--
-- Table structure for table `comments`
--

CREATE TABLE IF NOT EXISTS `comments` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `item_id` integer DEFAULT NULL,
  `item_type` text DEFAULT NULL,
  `user_id` integer DEFAULT NULL,
  `comment_text` text DEFAULT NULL,
  `posted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

--
-- Table structure for table `trust_votes`
--

CREATE TABLE IF NOT EXISTS `trust_votes` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `item_id` integer DEFAULT NULL,
  `item_type` text DEFAULT NULL,
  `user_id` integer DEFAULT NULL,
  `vote_value` integer DEFAULT NULL,
  `voted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

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

CREATE TABLE IF NOT EXISTS `posts` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `title` text NOT NULL,
  `content` text DEFAULT NULL,
  `item_id` integer DEFAULT NULL,
  `item_type` text DEFAULT NULL,
  `author` text DEFAULT 'Admin',
  `views` integer DEFAULT 0,
  `downloads` integer DEFAULT 0,
  `hours_played` integer DEFAULT 0,
  `copies_sold` integer DEFAULT 0,
  `price` real DEFAULT 0,
  `reserves_price` integer 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()
);

--
-- Table structure for table `post_media`
--

CREATE TABLE IF NOT EXISTS `post_media` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `post_id` integer DEFAULT NULL,
  `media_type` text DEFAULT NULL,
  `media_url` text DEFAULT NULL,
  `display_order` integer DEFAULT 0,
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
);

--
-- Table structure for table `post_comments`
--

CREATE TABLE IF NOT EXISTS `post_comments` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `post_id` integer DEFAULT NULL,
  `user_id` integer DEFAULT NULL,
  `username` text DEFAULT NULL,
  `comment_text` text DEFAULT NULL,
  `posted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

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

CREATE TABLE IF NOT EXISTS `receipts` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `receipt_token` text NOT NULL UNIQUE,
  `game_id` integer NOT NULL,
  `user_email` text NOT NULL,
  `game_title` text DEFAULT NULL,
  `game_developer` text DEFAULT NULL,
  `amount_paid` real DEFAULT 0,
  `is_free` tinyint(1) DEFAULT 0,
  `transaction_id` text DEFAULT NULL,
  `claimed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT NULL,
  `status` text DEFAULT 'active',
  `server_synced` tinyint(1) DEFAULT 0
);

--
-- Table structure for table `user_library`
--

CREATE TABLE IF NOT EXISTS `user_library` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `user_id` integer DEFAULT NULL,
  `item_id` integer DEFAULT NULL,
  `item_type` text DEFAULT NULL,
  `post_id` integer DEFAULT NULL,
  `install_path` text DEFAULT NULL,
  `installed` tinyint(1) DEFAULT 0,
  `hours_played` real DEFAULT 0,
  `last_played` timestamp NULL DEFAULT NULL,
  `purchased_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `purchase_type` text DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
);

--
-- Table structure for table `play_sessions`
--

CREATE TABLE IF NOT EXISTS `play_sessions` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `user_id` integer DEFAULT NULL,
  `library_id` integer DEFAULT NULL,
  `started_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `ended_at` timestamp NULL DEFAULT NULL,
  `duration_minutes` real DEFAULT 0,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`library_id`) REFERENCES `user_library` (`id`)
);

--
-- Table structure for table `user_profiles`
--

CREATE TABLE IF NOT EXISTS `user_profiles` (
  `user_id` integer PRIMARY KEY,
  `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` integer DEFAULT 0,
  `level` integer DEFAULT 1,
  `experience` integer DEFAULT 0
);

--
-- Table structure for table `activity_log`
--

CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `user_id` integer DEFAULT NULL,
  `activity_type` text DEFAULT NULL,
  `item_id` integer DEFAULT NULL,
  `item_name` text DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `duration` integer DEFAULT 0,
  `metadata` text DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

--
-- Table structure for table `profile_achievements`
--

CREATE TABLE IF NOT EXISTS `profile_achievements` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `name` text NOT NULL UNIQUE,
  `description` text DEFAULT NULL,
  `icon` text DEFAULT NULL,
  `category` text DEFAULT NULL,
  `points` integer DEFAULT 10,
  `requirement_type` text DEFAULT NULL,
  `requirement_value` integer DEFAULT NULL
);

--
-- Table structure for table `user_achievements`
--

CREATE TABLE IF NOT EXISTS `user_achievements` (
  `user_id` integer NOT NULL,
  `achievement_id` integer NOT NULL,
  `unlocked_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `progress` integer DEFAULT 0,
  PRIMARY KEY (`user_id`, `achievement_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`achievement_id`) REFERENCES `profile_achievements` (`id`)
);

--
-- Table structure for table `user_stats`
--

CREATE TABLE IF NOT EXISTS `user_stats` (
  `user_id` integer PRIMARY KEY,
  `games_played` integer DEFAULT 0,
  `apps_launched` integer DEFAULT 0,
  `total_sessions` integer DEFAULT 0,
  `favorite_game_id` integer DEFAULT NULL,
  `favorite_app_id` integer DEFAULT NULL,
  `longest_session` integer DEFAULT 0,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

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

CREATE TABLE IF NOT EXISTS `published_games` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `user_id` integer DEFAULT NULL,
  `title` text NOT NULL,
  `developer` text NOT NULL,
  `price` real DEFAULT 0,
  `description` text DEFAULT NULL,
  `category` text DEFAULT NULL,
  `content_type` text 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` integer DEFAULT 0,
  `rating` real 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` text DEFAULT NULL,
  `vendor_payout_percentage` real DEFAULT 85,
  `platform_fee_percentage` real DEFAULT 15,
  `image_path` text DEFAULT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

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

CREATE TABLE IF NOT EXISTS `game_sales` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `game_id` integer DEFAULT NULL,
  `buyer_email` text DEFAULT NULL,
  `sale_price` real DEFAULT NULL,
  `commission` real DEFAULT NULL,
  `net_earnings` real DEFAULT NULL,
  `sale_date` text DEFAULT NULL,
  `payment_status` text DEFAULT 'completed',
  `paypal_transaction_id` text DEFAULT NULL,
  FOREIGN KEY (`game_id`) REFERENCES `published_games` (`id`)
);
