-- =====================================================
-- 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
-- Note: Run these one at a time, ignore errors if column already exists
ALTER TABLE users ADD COLUMN stripe_customer_id VARCHAR(100) DEFAULT NULL;
ALTER TABLE users ADD COLUMN subscription_plan VARCHAR(50) DEFAULT 'free';
ALTER TABLE users ADD INDEX idx_stripe_customer (stripe_customer_id);

-- 5. Insert default subscription plans (with stripe_price_id included)
INSERT INTO subscription_plans (name, slug, stripe_product_id, stripe_price_id, price_cents, currency, billing_interval, features, query_limit, sort_order) VALUES
('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, 0),
('Gregory.AI Silver', 'silver', 'prod_TWy9ynEQrJkTCJ', 'price_1SZuCkJG1XXL1CZFRJJwVmfu', 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', 'price_1SZuCCJG1XXL1CZF0HxwvqY0', 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', 'price_1SZuBkJG1XXL1CZFrStPfwFb', 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),
    stripe_price_id = VALUES(stripe_price_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;

-- =====================================================
-- NOTE: The stripe_price_id values above are placeholders.
-- You need to get the actual Price IDs from your Stripe Dashboard.
-- 
-- To get Price IDs:
-- 1. Go to Stripe Dashboard → Products
-- 2. Click on each product (Silver, Gold, Premium)
-- 3. Under "Pricing", find the Price ID (starts with price_)
-- 4. Run the UPDATE queries below with your actual Price IDs
-- =====================================================

-- 7. UPDATE Price IDs (run these after getting actual Price IDs from Stripe)
-- Replace the placeholder values with your actual Stripe Price IDs

UPDATE subscription_plans SET stripe_price_id = 'price_1SZuCkJG1XXL1CZFRJJwVmfu' WHERE slug = 'silver';
UPDATE subscription_plans SET stripe_price_id = 'price_1SZuCCJG1XXL1CZF0HxwvqY0' WHERE slug = 'gold';
UPDATE subscription_plans SET stripe_price_id = 'price_1SZuBkJG1XXL1CZFrStPfwFb' WHERE slug = 'premium';

-- =====================================================
-- VERIFICATION: Run this to check your plans are set up correctly
-- =====================================================
-- SELECT id, name, slug, stripe_product_id, stripe_price_id, price_cents, query_limit FROM subscription_plans ORDER BY sort_order;