-- Nova Strike Mods Database
-- Created automatically - Just import this file


-- Table structure for table `users`
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  `device_id` varchar(255) NOT NULL,
  `first_seen` datetime DEFAULT current_timestamp(),
  `last_seen` datetime DEFAULT current_timestamp(),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `request_count` int(11) DEFAULT 1,
  `app_version` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT current_timestamp(),
  `updated_at` timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table structure for table `version_statistics`
CREATE TABLE `version_statistics` (
  `id` int(11) NOT NULL,
  `app_version` varchar(50) NOT NULL,
  `total_users` int(11) DEFAULT 0,
  `active_users` int(11) DEFAULT 0,
  `total_requests` int(11) DEFAULT 0,
  `first_seen` datetime DEFAULT current_timestamp(),
  `last_updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Indexes for table `users`
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_device_id` (`device_id`),
  ADD KEY `idx_last_seen` (`last_seen`),
  ADD KEY `idx_app_version` (`app_version`);

-- Indexes for table `version_statistics`
ALTER TABLE `version_statistics`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_version` (`app_version`);

-- AUTO_INCREMENT for table `users`
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- AUTO_INCREMENT for table `version_statistics`
ALTER TABLE `version_statistics`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- Insert sample data
INSERT INTO `version_statistics` (`app_version`, `total_users`, `active_users`, `total_requests`) VALUES
('1.0.0', 5, 3, 15),
('1.1.0', 8, 5, 25),
('1.2.0', 12, 8, 40);

INSERT INTO `users` (`user_id`, `device_id`, `ip_address`, `user_agent`, `request_count`, `app_version`) VALUES
('user_001', 'device_abc123', '192.168.1.1', 'Mozilla/5.0', 5, '1.2.0'),
('user_002', 'device_def456', '192.168.1.2', 'Mozilla/5.0', 3, '1.1.0'),
('user_003', 'device_ghi789', '192.168.1.3', 'Mozilla/5.0', 7, '1.2.0'),
('user_004', 'device_jkl012', '192.168.1.4', 'Mozilla/5.0', 2, '1.0.0'),
('user_005', 'device_mno345', '192.168.1.5', 'Mozilla/5.0', 4, '1.2.0');

COMMIT;