-- Knowledge Base Articles Table
CREATE TABLE IF NOT EXISTS knowledge_base (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    category VARCHAR(50) NOT NULL,
    tags TEXT,
    author_id INT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    views INT DEFAULT 0,
    helpful_count INT DEFAULT 0,
    not_helpful_count INT DEFAULT 0,
    featured BOOLEAN DEFAULT FALSE,
    meta_title VARCHAR(255),
    meta_description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_status (status),
    INDEX idx_featured (featured),
    INDEX idx_views (views),
    FULLTEXT(title, content, tags)
);

-- FAQs Table
CREATE TABLE IF NOT EXISTS faqs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question VARCHAR(500) NOT NULL,
    answer TEXT NOT NULL,
    category VARCHAR(50),
    sort_order INT DEFAULT 0,
    status ENUM('draft', 'published', 'archived') DEFAULT 'published',
    views INT DEFAULT 0,
    helpful_count INT DEFAULT 0,
    not_helpful_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_status (status),
    INDEX idx_sort_order (sort_order)
);

-- Password Reset Table
CREATE TABLE IF NOT EXISTS password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    reset_code VARCHAR(6) NOT NULL,
    reset_token VARCHAR(64) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_token (reset_token),
    INDEX idx_expires (expires_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Activity Logs Table
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    activity_type VARCHAR(50) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Failed Login Attempts Table
CREATE TABLE IF NOT EXISTS failed_logins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_ip_address (ip_address),
    INDEX idx_attempted_at (attempted_at)
);

-- Insert sample knowledge base articles
INSERT INTO knowledge_base (title, slug, content, excerpt, category, tags, status, views, helpful_count) VALUES
('How to Make a Deposit', 'how-to-make-deposit', 
'Making a deposit to your PayGateway Pro account is simple and secure. Follow these steps:\n\n1. Log in to your dashboard\n2. Navigate to the Deposit section\n3. Choose your preferred payment method\n4. Enter the amount you wish to deposit\n5. Complete the payment process\n\nSupported payment methods include:\n- Credit/Debit Cards (Visa, Mastercard, American Express)\n- Bank Transfers\n- Digital Wallets (PayPal, Apple Pay, Google Pay)\n- Cryptocurrency (Bitcoin, Ethereum)\n\nDeposits are typically processed within minutes for most payment methods. Bank transfers may take 1-3 business days.',
'Learn how to add funds to your account using various payment methods.',
'deposits', 'deposit,funding,payment,credit card,bank transfer', 'published', 1250, 45),

('Understanding Transaction Fees', 'understanding-transaction-fees',
'Our fee structure is transparent and competitive:\n\n**Processing Fees:**\n- Credit/Debit Cards: 2.9% + $0.30\n- Bank Transfers: 1% (minimum $1)\n- Digital Wallets: 3.4% + $0.30\n- Cryptocurrency: 1.5%\n\n**Other Fees:**\n- Withdrawal Fee: $2 for bank transfers, 1% for other methods\n- Currency Conversion: 2.5% above mid-market rate\n- Chargeback Fee: $15 per dispute\n\n**Fee Reductions:**\n- Professional Plan: 0.2% reduction\n- Enterprise Plan: 0.4% reduction\n- High Volume Discounts: Available for $100k+ monthly volume',
'Complete breakdown of all fees and charges on our platform.',
'fees', 'fees,charges,pricing,costs', 'published', 890, 32),

('KYC Verification Process', 'kyc-verification-process',
'Know Your Customer (KYC) verification is required to unlock full platform features:\n\n**Required Documents:**\n1. Government-issued ID (passport, driver\'s license, national ID)\n2. Proof of address (utility bill, bank statement, lease agreement - not older than 3 months)\n3. Selfie holding your ID document\n\n**Verification Levels:**\n- Level 0: Unverified - $500 daily limit\n- Level 1: Pending - Same limits during review\n- Level 2: Verified - $50,000 daily limit\n\n**Tips for Quick Approval:**\n- Ensure all documents are clear and readable\n- All corners of documents should be visible\n- Use good lighting when taking photos\n- Don\'t edit or modify documents\n\nVerification typically takes 24-48 hours.',
'Step-by-step guide to complete your identity verification.',
'verification', 'kyc,verification,identity,documents', 'published', 2100, 78),

('API Integration Guide', 'api-integration-guide',
'Integrate PayGateway Pro into your application with our RESTful API:\n\n**Getting Started:**\n1. Generate API keys in your dashboard\n2. Use demo keys for testing\n3. Switch to live keys for production\n\n**Authentication:**\nInclude your API key in the Authorization header:\n```\nAuthorization: Bearer pk_demo_your_key_here\n```\n\n**Creating a Payment:**\n```json\nPOST /api/v1/payments\n{\n  "amount": 1000,\n  "currency": "USD",\n  "description": "Test payment",\n  "payment_method": "card"\n}\n```\n\n**Webhooks:**\nConfigure webhook endpoints to receive real-time notifications about payment events.\n\n**SDKs Available:**\n- PHP\n- JavaScript/Node.js\n- Python\n- Ruby',
'Complete guide to integrate our payment API into your application.',
'api', 'api,integration,development,webhook', 'published', 567, 23),

('Security Best Practices', 'security-best-practices',
'Keep your account secure with these best practices:\n\n**Account Security:**\n- Use a strong, unique password\n- Enable two-factor authentication (2FA)\n- Regularly review account activity\n- Never share your login credentials\n\n**API Security:**\n- Keep API keys secure and private\n- Use HTTPS for all API requests\n- Implement proper error handling\n- Regularly rotate API keys\n\n**Payment Security:**\n- Verify payment notifications via webhooks\n- Implement fraud detection measures\n- Monitor for suspicious activity\n- Use secure payment forms\n\n**Data Protection:**\n- Never store sensitive payment data\n- Use tokenization for recurring payments\n- Comply with PCI DSS requirements\n- Encrypt sensitive data in transit and at rest',
'How to keep your account secure and protect your funds.',
'security', 'security,safety,protection,2fa,api keys', 'published', 1456, 67),

('Withdrawal Methods and Limits', 'withdrawal-methods-limits',
'Withdraw your funds using various methods:\n\n**Available Methods:**\n- Bank Transfer: 1-3 business days, $2 fee\n- Digital Wallets: Instant to 24 hours, 1% fee\n- Cryptocurrency: 10-60 minutes, network fees apply\n- Check by Mail: 5-7 business days, $5 fee\n\n**Withdrawal Limits:**\n- Unverified: $500 daily\n- Verified: $50,000 daily\n- Enterprise: Custom limits available\n\n**Processing Times:**\n- Requests submitted before 3 PM EST are processed same day\n- Weekend requests processed on Monday\n- Holidays may cause delays\n\n**Requirements:**\n- Minimum withdrawal: $10\n- Account must be verified for amounts over $500\n- Source verification may be required for large amounts',
'Information about withdrawal options, limits, and processing times.',
'withdrawals', 'withdrawal,payout,limits,bank transfer', 'published', 1789, 54);

-- Insert sample FAQs
INSERT INTO faqs (question, answer, category, sort_order, helpful_count) VALUES
('How long does it take to process a payment?', 'Most payments are processed instantly. Credit card payments typically take a few seconds, while bank transfers may take 1-3 business days depending on your bank.', 'payments', 1, 23),
('What are your transaction fees?', 'Our fees vary by payment method: Credit cards are 2.9% + $0.30, bank transfers are 1% (minimum $1), and digital wallets are 3.4% + $0.30. Volume discounts are available.', 'fees', 2, 18),
('Is my payment information secure?', 'Yes, we use bank-level encryption and are PCI DSS compliant. We never store your full credit card information and use tokenization for recurring payments.', 'security', 3, 31),
('How do I verify my account?', 'Account verification requires a government-issued ID, proof of address, and a selfie with your ID. Upload these documents in your dashboard under the KYC section.', 'verification', 4, 27),
('Can I get a refund for a transaction?', 'Refunds depend on the merchant\'s policy. If you need to dispute a transaction, contact our support team with your transaction details and reason for the refund request.', 'refunds', 5, 15),
('What currencies do you support?', 'We support over 100 currencies including USD, EUR, GBP, CAD, AUD, and many others. Cryptocurrency payments are also supported for Bitcoin and Ethereum.', 'currencies', 6, 12),
('How do I integrate the API?', 'Generate API keys in your dashboard, then use our RESTful API endpoints. We provide SDKs for popular programming languages and comprehensive documentation.', 'api', 7, 19),
('What happens if a payment fails?', 'Failed payments are automatically retried up to 3 times. You\'ll receive notifications about failed payments, and customers can retry with a different payment method.', 'payments', 8, 14),
('Do you offer customer support?', 'Yes, we provide 24/7 customer support via email, live chat, and support tickets. Enterprise customers also get dedicated account managers.', 'support', 9, 22),
('How do I cancel a recurring payment?', 'You can cancel recurring payments in your dashboard under the Recurring Payments section, or contact the merchant directly to cancel the subscription.', 'recurring', 10, 16);
