

-- Templates table
CREATE TABLE IF NOT EXISTS templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(55),
    description TEXT,
    preview_image VARCHAR(55),
    is_premium BOOLEAN DEFAULT FALSE,
    price FLOAT DEFAULT 0.0,
    category VARCHAR(50) DEFAULT 'Professional',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_category (category),
    INDEX idx_is_premium (is_premium)
);

-- Plans table
CREATE TABLE IF NOT EXISTS plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(55),
    description TEXT,
    price FLOAT,
    duration_days INT,
    max_business_cards INT,
    max_qr_codes INT,
    premium_templates BOOLEAN DEFAULT FALSE,
    analytics BOOLEAN DEFAULT FALSE
);

-- Business Cards table
CREATE TABLE IF NOT EXISTS business_cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(55),
    description TEXT,
    qr_code VARCHAR(55),
    template_id INT,
    background_color VARCHAR(20),
    text_color VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_is_active (is_active),
    INDEX idx_created_at (created_at)
);

-- Subscriptions table
CREATE TABLE IF NOT EXISTS subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    start_date DATETIME,
    end_date DATETIME,
    status VARCHAR(20),
    payment_id VARCHAR(55),
    amount FLOAT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
);

-- Analytics table
CREATE TABLE IF NOT EXISTS analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    business_card_id INT NOT NULL,
    views INT DEFAULT 0,
    scans INT DEFAULT 0,
    unique_visitors INT DEFAULT 0,
    last_scanned DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (business_card_id) REFERENCES business_cards(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_business_card_id (business_card_id),
    INDEX idx_last_scanned (last_scanned)
);

-- Referrals table
CREATE TABLE IF NOT EXISTS referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    referrer_id INT NOT NULL,
    referral_code VARCHAR(55) UNIQUE,
    status VARCHAR(20),
    reward_credits INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_referrer_id (referrer_id),
    INDEX idx_referral_code (referral_code)
);

-- Insert default templates
INSERT INTO templates (name, description, is_premium, price, category, preview_image) VALUES
-- Professional Templates (Free)
('Modern Business', 'Clean and professional design with subtle gradients', FALSE, 0.00, 'Professional', 'template1.jpg'),
('Executive Classic', 'Traditional business card design for executives', FALSE, 0.00, 'Professional', 'template2.jpg'),
('Corporate Blue', 'Professional blue-themed corporate design', FALSE, 0.00, 'Professional', 'template3.jpg'),
('Minimalist White', 'Clean white background with elegant typography', FALSE, 0.00, 'Professional', 'template4.jpg'),

-- Creative Templates (Premium)
('Creative Gradient', 'Modern gradient design with creative elements', TRUE, 9.99, 'Creative', 'template5.jpg'),
('Artistic Abstract', 'Abstract artistic design for creative professionals', TRUE, 12.99, 'Creative', 'template6.jpg'),
('Colorful Modern', 'Vibrant colors with modern layout', TRUE, 8.99, 'Creative', 'template7.jpg'),
('Geometric Pattern', 'Geometric patterns with professional touch', TRUE, 11.99, 'Creative', 'template8.jpg'),

-- Industry-Specific Templates (Premium)
('Tech Startup', 'Modern tech-focused design for startups', TRUE, 14.99, 'Industry', 'template9.jpg'),
('Medical Professional', 'Clean design suitable for medical professionals', TRUE, 13.99, 'Industry', 'template10.jpg'),
('Legal Practice', 'Formal design for legal professionals', TRUE, 15.99, 'Industry', 'template11.jpg'),
('Real Estate', 'Professional design for real estate agents', TRUE, 10.99, 'Industry', 'template12.jpg'),

-- Premium Templates
('Luxury Gold', 'Premium gold-themed luxury design', TRUE, 19.99, 'Premium', 'template13.jpg'),
('Platinum Elite', 'Ultra-premium platinum design', TRUE, 24.99, 'Premium', 'template14.jpg'),
('Diamond Premium', 'Exclusive diamond-themed premium design', TRUE, 29.99, 'Premium', 'template15.jpg'),

-- Seasonal Templates (Premium)
('Holiday Special', 'Festive design for holiday seasons', TRUE, 7.99, 'Seasonal', 'template16.jpg'),
('Summer Vibes', 'Bright summer-themed design', TRUE, 6.99, 'Seasonal', 'template17.jpg'),
('Autumn Professional', 'Warm autumn colors with professional layout', TRUE, 8.99, 'Seasonal', 'template18.jpg');

-- Insert default subscription plans
INSERT INTO plans (name, description, price, duration_days, max_business_cards, max_qr_codes, premium_templates, analytics) VALUES
('Free Plan', 'Basic plan with limited features', 0.00, 365, 3, 3, FALSE, FALSE),
('Pro Plan', 'Professional plan with premium features', 9.99, 365, 10, 10, TRUE, TRUE),
('Business Plan', 'Business plan with advanced features', 19.99, 365, 50, 50, TRUE, TRUE),
('Enterprise Plan', 'Enterprise plan with unlimited features', 49.99, 365, -1, -1, TRUE, TRUE);
