-- phpMyAdmin SQL Dump -- version 5.2.2 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Dec 02, 2025 at 09:30 AM -- Server version: 8.0.44 -- PHP Version: 8.3.20 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `securitisys_greg` -- -- -------------------------------------------------------- -- -- Table structure for table `conversations` -- CREATE TABLE `conversations` ( `id` bigint UNSIGNED NOT NULL, `user_id` int UNSIGNED NOT NULL, `public_id` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL, `title` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'New Conversation', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci; -- -- Dumping data for table `conversations` -- -- -------------------------------------------------------- -- -- Table structure for table `messages` -- CREATE TABLE `messages` ( `id` bigint UNSIGNED NOT NULL, `conversation_id` bigint UNSIGNED NOT NULL, `role` enum('user','assistant') COLLATE utf8mb3_unicode_ci NOT NULL, `content` mediumtext COLLATE utf8mb3_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci; -- -- Dumping data for table `messages` -- -- -------------------------------------------------------- -- -- Table structure for table `subscription_plans` -- CREATE TABLE `subscription_plans` ( `id` int NOT NULL, `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `slug` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `stripe_product_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `stripe_price_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `price_cents` int NOT NULL DEFAULT '0', `currency` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `billing_interval` enum('month','year') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'month', `features` json DEFAULT NULL, `query_limit` int DEFAULT NULL COMMENT 'NULL means unlimited', `is_active` tinyint(1) NOT NULL DEFAULT '1', `sort_order` int NOT NULL DEFAULT '0', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int UNSIGNED NOT NULL, `username` varchar(50) COLLATE utf8mb3_unicode_ci DEFAULT NULL, `email` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL, `password_hash` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL, `google_id` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL, `name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL, `image` varchar(1024) COLLATE utf8mb3_unicode_ci DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `status` enum('active','disabled','suspended') COLLATE utf8mb3_unicode_ci DEFAULT 'active', `is_admin` tinyint(1) DEFAULT '0', `query_count` int UNSIGNED DEFAULT '0', `last_active` timestamp NULL DEFAULT NULL, `stripe_customer_id` varchar(100) COLLATE utf8mb3_unicode_ci DEFAULT NULL, `subscription_plan` varchar(50) COLLATE utf8mb3_unicode_ci DEFAULT 'free' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci; -- -- Dumping data for table `users` -- -- -------------------------------------------------------- -- -- Table structure for table `user_subscriptions` -- CREATE TABLE `user_subscriptions` ( `id` int NOT NULL, `user_id` int UNSIGNED NOT NULL, `plan_id` int NOT NULL, `stripe_customer_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `stripe_subscription_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('active','canceled','past_due','trialing','incomplete','incomplete_expired','unpaid','paused') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active', `current_period_start` timestamp NULL DEFAULT NULL, `current_period_end` timestamp NULL DEFAULT NULL, `cancel_at_period_end` tinyint(1) NOT NULL DEFAULT '0', `canceled_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `payment_history` -- CREATE TABLE `payment_history` ( `id` int NOT NULL, `user_id` int UNSIGNED NOT NULL, `subscription_id` int DEFAULT NULL, `stripe_payment_intent_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `stripe_invoice_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `amount_cents` int NOT NULL, `currency` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `status` enum('succeeded','pending','failed','refunded') COLLATE utf8mb4_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Stand-in structure for view `v_conversation_summaries` -- (See below for the actual view) -- CREATE TABLE `v_conversation_summaries` ( `id` bigint unsigned ,`title` varchar(255) ,`updated_at` timestamp ,`user_id` int unsigned ); -- -- Indexes for dumped tables -- -- -- Indexes for table `conversations` -- ALTER TABLE `conversations` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `public_id` (`public_id`), ADD KEY `idx_conversations_user_id` (`user_id`), ADD KEY `idx_conversations_updated_at` (`updated_at`), ADD KEY `idx_conversations_public_id` (`public_id`); -- -- Indexes for table `messages` -- ALTER TABLE `messages` ADD PRIMARY KEY (`id`), ADD KEY `idx_messages_conversation_id` (`conversation_id`), ADD KEY `idx_messages_created_at` (`created_at`); -- -- Indexes for table `subscription_plans` -- ALTER TABLE `subscription_plans` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `idx_slug` (`slug`), ADD KEY `idx_stripe_product` (`stripe_product_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `username` (`username`), ADD UNIQUE KEY `email` (`email`), ADD KEY `idx_users_email` (`email`), ADD KEY `idx_users_username` (`username`), ADD KEY `idx_users_google_id` (`google_id`), ADD KEY `idx_stripe_customer` (`stripe_customer_id`); -- -- Indexes for table `user_subscriptions` -- ALTER TABLE `user_subscriptions` ADD PRIMARY KEY (`id`), ADD KEY `idx_user` (`user_id`), ADD KEY `idx_stripe_customer` (`stripe_customer_id`), ADD KEY `idx_stripe_subscription` (`stripe_subscription_id`), ADD KEY `idx_status` (`status`), ADD KEY `plan_id` (`plan_id`); -- -- Indexes for table `payment_history` -- ALTER TABLE `payment_history` ADD PRIMARY KEY (`id`), ADD KEY `idx_user` (`user_id`), ADD KEY `idx_stripe_payment` (`stripe_payment_intent_id`), ADD KEY `idx_stripe_invoice` (`stripe_invoice_id`), ADD KEY `subscription_id` (`subscription_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `conversations` -- ALTER TABLE `conversations` MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=109; -- -- AUTO_INCREMENT for table `messages` -- ALTER TABLE `messages` MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=710; -- -- AUTO_INCREMENT for table `subscription_plans` -- ALTER TABLE `subscription_plans` MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; -- -- AUTO_INCREMENT for table `user_subscriptions` -- ALTER TABLE `user_subscriptions` MODIFY `id` int NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `payment_history` -- ALTER TABLE `payment_history` MODIFY `id` int NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12; -- -------------------------------------------------------- -- -- Structure for view `v_conversation_summaries` -- DROP TABLE IF EXISTS `v_conversation_summaries`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_conversation_summaries` AS SELECT `c`.`id` AS `id`, `c`.`user_id` AS `user_id`, `c`.`title` AS `title`, `c`.`updated_at` AS `updated_at` FROM `conversations` AS `c` ; -- -------------------------------------------------------- -- -- Structure for view `user_subscription_status` -- DROP TABLE IF EXISTS `user_subscription_status`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_subscription_status` AS SELECT u.id AS user_id, u.email, u.username, COALESCE(sp.slug, 'free') AS plan_slug, COALESCE(sp.name, 'Free') AS plan_name, COALESCE(sp.price_cents, 0) AS price_cents, COALESCE(sp.query_limit, 5) AS query_limit, us.status AS subscription_status, us.current_period_end, us.cancel_at_period_end FROM users u LEFT JOIN user_subscriptions us ON u.id = us.user_id AND us.status = 'active' LEFT JOIN subscription_plans sp ON us.plan_id = sp.id; -- -- Constraints for dumped tables -- -- -- Constraints for table `conversations` -- ALTER TABLE `conversations` ADD CONSTRAINT `fk_conversations_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `messages` -- ALTER TABLE `messages` ADD CONSTRAINT `fk_messages_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE; -- -- Constraints for table `user_subscriptions` -- ALTER TABLE `user_subscriptions` ADD CONSTRAINT `user_subscriptions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `user_subscriptions_ibfk_2` FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`); -- -- Constraints for table `payment_history` -- ALTER TABLE `payment_history` ADD CONSTRAINT `payment_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `payment_history_ibfk_2` FOREIGN KEY (`subscription_id`) REFERENCES `user_subscriptions` (`id`) ON DELETE SET NULL; -- -------------------------------------------------------- -- -- Dumping data for table `subscription_plans` -- INSERT INTO `subscription_plans` (`id`, `name`, `slug`, `stripe_product_id`, `stripe_price_id`, `price_cents`, `currency`, `billing_interval`, `features`, `query_limit`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES (1, 'Free', 'free', 'free', NULL, 0, 'USD', 'month', '{"queries_per_day": 5, "file_uploads": false, "priority_support": false, "features": ["5 queries per day", "Basic AI responses", "Community support"]}', 5, 1, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (2, 'Gregory.AI Silver', 'silver', 'prod_TWy9ynEQrJkTCJ', NULL, 1000, 'USD', 'month', '{"queries_per_day": 100, "file_uploads": true, "priority_support": false, "features": ["100 queries per day", "File uploads", "Enhanced AI responses", "Email support"]}', 100, 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (3, 'Gregory.AI Gold', 'gold', 'prod_TWy8T9MWg6QCgW', NULL, 2000, 'USD', 'month', '{"queries_per_day": 500, "file_uploads": true, "priority_support": true, "features": ["500 queries per day", "File uploads", "Advanced AI responses", "Priority email support", "API access"]}', 500, 1, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), (4, 'Gregory.AI Premium', 'premium', 'prod_TWy7Q5maHVrBfw', NULL, 3000, 'USD', 'month', '{"queries_per_day": null, "file_uploads": true, "priority_support": true, "features": ["Unlimited queries", "File uploads", "Premium AI responses", "24/7 Priority support", "Full API access", "Custom integrations"]}', NULL, 1, 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;