-- Create database and user (adjust host as needed) CREATE DATABASE IF NOT EXISTS `securitisys_greg` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- Create user if it doesn't exist and grant privileges CREATE USER IF NOT EXISTS 'securitisys_greg'@'localhost' IDENTIFIED BY 'securitisys_greg'; CREATE USER IF NOT EXISTS 'securitisys_greg'@'%' IDENTIFIED BY 'securitisys_greg'; GRANT ALL PRIVILEGES ON `securitisys_greg`.* TO 'securitisys_greg'@'localhost'; GRANT ALL PRIVILEGES ON `securitisys_greg`.* TO 'securitisys_greg'@'%'; FLUSH PRIVILEGES; USE `securitisys_greg`; -- Users table: supports local credentials and Google OAuth CREATE TABLE IF NOT EXISTS `users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(50) NULL UNIQUE, `email` VARCHAR(255) NULL UNIQUE, `password_hash` VARCHAR(255) NULL, `google_id` VARCHAR(255) NULL, `name` VARCHAR(255) NULL, `image` VARCHAR(1024) NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), INDEX `idx_users_email` (`email`), INDEX `idx_users_username` (`username`), INDEX `idx_users_google_id` (`google_id`) ) ENGINE=InnoDB; -- Conversations table CREATE TABLE IF NOT EXISTS `conversations` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL, `public_id` VARCHAR(64) NOT NULL UNIQUE, `title` VARCHAR(255) 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, PRIMARY KEY (`id`), INDEX `idx_conversations_user_id` (`user_id`), INDEX `idx_conversations_public_id` (`public_id`), INDEX `idx_conversations_updated_at` (`updated_at`), CONSTRAINT `fk_conversations_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB; -- Messages table CREATE TABLE IF NOT EXISTS `messages` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `conversation_id` BIGINT UNSIGNED NOT NULL, `role` ENUM('user','assistant') NOT NULL, `content` MEDIUMTEXT NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), INDEX `idx_messages_conversation_id` (`conversation_id`), INDEX `idx_messages_created_at` (`created_at`), CONSTRAINT `fk_messages_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB; -- Optional: simple view for conversation summaries DROP VIEW IF EXISTS `v_conversation_summaries`; CREATE VIEW `v_conversation_summaries` AS SELECT c.id, c.user_id, c.title, c.updated_at FROM conversations c; -- ===================================================== -- Gregory.AI Subscription System Migration -- Run this script to add subscription support to your database -- ===================================================== -- 1. Create subscription_plans table CREATE TABLE IF NOT EXISTS subscription_plans ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(50) NOT NULL UNIQUE, stripe_product_id VARCHAR(100) NOT NULL, stripe_price_id VARCHAR(100) DEFAULT NULL, price_cents INT NOT NULL DEFAULT 0, currency VARCHAR(10) NOT NULL DEFAULT 'USD', billing_interval ENUM('month', 'year') NOT NULL DEFAULT 'month', features JSON DEFAULT NULL, query_limit INT DEFAULT NULL COMMENT 'NULL means unlimited', is_active BOOLEAN NOT NULL DEFAULT TRUE, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_slug (slug), INDEX idx_stripe_product (stripe_product_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2. Create user_subscriptions table CREATE TABLE IF NOT EXISTS user_subscriptions ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, plan_id INT NOT NULL, stripe_customer_id VARCHAR(100) DEFAULT NULL, stripe_subscription_id VARCHAR(100) DEFAULT NULL, status ENUM('active', 'canceled', 'past_due', 'trialing', 'incomplete', 'incomplete_expired', 'unpaid', 'paused') NOT NULL DEFAULT 'active', current_period_start TIMESTAMP NULL, current_period_end TIMESTAMP NULL, cancel_at_period_end BOOLEAN NOT NULL DEFAULT FALSE, canceled_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (plan_id) REFERENCES subscription_plans(id), INDEX idx_user (user_id), INDEX idx_stripe_customer (stripe_customer_id), INDEX idx_stripe_subscription (stripe_subscription_id), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 3. Create payment_history table for tracking all payments CREATE TABLE IF NOT EXISTS payment_history ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, subscription_id INT DEFAULT NULL, stripe_payment_intent_id VARCHAR(100) DEFAULT NULL, stripe_invoice_id VARCHAR(100) DEFAULT NULL, amount_cents INT NOT NULL, currency VARCHAR(10) NOT NULL DEFAULT 'USD', status ENUM('succeeded', 'pending', 'failed', 'refunded') NOT NULL, description VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (subscription_id) REFERENCES user_subscriptions(id) ON DELETE SET NULL, INDEX idx_user (user_id), INDEX idx_stripe_payment (stripe_payment_intent_id), INDEX idx_stripe_invoice (stripe_invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 4. Add stripe_customer_id to users table if not exists ALTER TABLE users ADD COLUMN IF NOT EXISTS stripe_customer_id VARCHAR(100) DEFAULT NULL, ADD COLUMN IF NOT EXISTS subscription_plan VARCHAR(50) DEFAULT 'free', ADD INDEX IF NOT EXISTS idx_stripe_customer (stripe_customer_id); -- 5. Insert default subscription plans INSERT INTO subscription_plans (name, slug, stripe_product_id, price_cents, currency, billing_interval, features, query_limit, sort_order) VALUES ('Free', 'free', 'free', 0, 'USD', 'month', '{"queries_per_day": 5, "file_uploads": false, "priority_support": false, "features": ["5 queries per day", "Basic AI responses", "Community support"]}', 5, 0), ('Gregory.AI Silver', 'silver', 'prod_TWy9ynEQrJkTCJ', 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), ('Gregory.AI Gold', 'gold', 'prod_TWy8T9MWg6QCgW', 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, 2), ('Gregory.AI Premium', 'premium', 'prod_TWy7Q5maHVrBfw', 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, 3) ON DUPLICATE KEY UPDATE name = VALUES(name), stripe_product_id = VALUES(stripe_product_id), price_cents = VALUES(price_cents), features = VALUES(features), query_limit = VALUES(query_limit), sort_order = VALUES(sort_order); -- 6. Create a view for easy subscription status lookup CREATE OR REPLACE 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; -- ===================================================== -- IMPORTANT: After running this migration, you need to: -- 1. Create Price IDs in Stripe Dashboard for each product -- 2. Update the stripe_price_id column with actual Price IDs -- -- Example update command: -- UPDATE subscription_plans SET stripe_price_id = 'price_XXXXX' WHERE slug = 'silver'; -- UPDATE subscription_plans SET stripe_price_id = 'price_XXXXX' WHERE slug = 'gold'; -- UPDATE subscription_plans SET stripe_price_id = 'price_XXXXX' WHERE slug = 'premium'; -- =====================================================