-- ============================================================================
-- FLUTTERWAVE INTEGRATION - DATABASE SETUP
-- ============================================================================
-- Developer: Core Tech Web Design Technology
-- WhatsApp: +2349162671360
-- Telegram: @jeditech
-- ============================================================================

-- Add Flutterwave configuration columns to settings table
-- ============================================================================

-- Enable/disable Flutterwave gateway
ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_enabled TINYINT(1) NOT NULL DEFAULT 0
COMMENT 'Toggle Flutterwave payment gateway on/off';

-- Flutterwave API public key
ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_public_key VARCHAR(255) NOT NULL DEFAULT ''
COMMENT 'Flutterwave public API key (FLWPUBK-...)';

-- Flutterwave API secret key
ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_secret_key VARCHAR(255) NOT NULL DEFAULT ''
COMMENT 'Flutterwave secret API key (FLWSECK-...)';

-- Minimum deposit amount in NGN
ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_minimum DECIMAL(10,2) NOT NULL DEFAULT 100.00
COMMENT 'Minimum deposit amount in Nigerian Naira';

-- Default currency for Flutterwave payments
ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_default_currency VARCHAR(10) NOT NULL DEFAULT 'NGN'
COMMENT 'Default currency (NGN, USD, EUR, GBP, etc.)';

-- ============================================================================
-- WEBHOOK SECRET (HIGHLY RECOMMENDED FOR PRODUCTION)
-- ============================================================================
-- Add this to receive and verify webhook notifications from Flutterwave
-- Get the secret hash from: Flutterwave Dashboard → Settings → Webhooks

ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_webhook_secret VARCHAR(255) NOT NULL DEFAULT ''
COMMENT 'Flutterwave webhook verification hash (verif-hash header)';

-- ============================================================================
-- OPTIONAL: Add FX markup configuration
-- ============================================================================

ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_fx_markup DECIMAL(5,2) NOT NULL DEFAULT 0.00
COMMENT 'FX markup percentage (e.g., 2.00 for 2%)';

-- ============================================================================
-- OPTIONAL: Add supported currencies JSON field
-- ============================================================================

ALTER TABLE settings 
ADD COLUMN IF NOT EXISTS flutterwave_supported_currencies TEXT
COMMENT 'JSON array of enabled currencies ["NGN","USD","EUR","GBP"]';

-- ============================================================================
-- Update existing settings record with default Flutterwave values
-- ============================================================================

UPDATE settings 
SET 
    flutterwave_enabled = 0,
    flutterwave_public_key = '',
    flutterwave_secret_key = '',
    flutterwave_minimum = 100.00,
    flutterwave_default_currency = 'NGN'
WHERE id = 1
LIMIT 1;

-- ============================================================================
-- Verify settings columns exist
-- ============================================================================

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_DEFAULT,
    IS_NULLABLE,
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'settings'
AND TABLE_SCHEMA = DATABASE()
AND COLUMN_NAME LIKE 'flutterwave%'
ORDER BY ORDINAL_POSITION;

-- ============================================================================
-- Check transactions table structure (should already support Flutterwave)
-- ============================================================================

-- Verify transactions table has required columns
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'transactions'
AND TABLE_SCHEMA = DATABASE()
AND COLUMN_NAME IN (
    'transaction_ref',
    'payment_gateway',
    'amount',
    'currency',
    'metadata',
    'status',
    'payment_method',
    'gateway_reference',
    'gateway_response',
    'is_first_deposit',
    'referral_awarded'
)
ORDER BY ORDINAL_POSITION;

-- ============================================================================
-- OPTIONAL: Add indexes for better query performance
-- ============================================================================

-- Index for transaction lookup by reference
ALTER TABLE transactions 
ADD INDEX IF NOT EXISTS idx_transaction_ref (transaction_ref);

-- Index for gateway-specific queries
ALTER TABLE transactions 
ADD INDEX IF NOT EXISTS idx_payment_gateway_status (payment_gateway, status);

-- Index for user transaction history
ALTER TABLE transactions 
ADD INDEX IF NOT EXISTS idx_user_status_created (user_id, status, created_at);

-- ============================================================================
-- Sample configuration for testing (REPLACE WITH YOUR ACTUAL KEYS)
-- ============================================================================

-- IMPORTANT: Replace these with your actual Flutterwave API keys
-- Get keys from: https://dashboard.flutterwave.com/settings/apis

/*
-- TEST MODE CONFIGURATION
UPDATE settings 
SET 
    flutterwave_enabled = 1,
    flutterwave_public_key = 'FLWPUBK-TEST-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-X',
    flutterwave_secret_key = 'FLWSECK-TEST-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-X',
    flutterwave_minimum = 100.00,
    flutterwave_default_currency = 'NGN',
    flutterwave_webhook_secret = 'your_webhook_hash_here'
WHERE id = 1;

-- LIVE MODE CONFIGURATION
UPDATE settings 
SET 
    flutterwave_enabled = 1,
    flutterwave_public_key = 'FLWPUBK-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-X',
    flutterwave_secret_key = 'FLWSECK-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-X',
    flutterwave_minimum = 100.00,
    flutterwave_default_currency = 'NGN',
    flutterwave_webhook_secret = 'your_webhook_hash_here'
WHERE id = 1;
*/

-- ============================================================================
-- Query to check current Flutterwave configuration
-- ============================================================================

SELECT 
    flutterwave_enabled AS 'Enabled',
    CASE 
        WHEN LENGTH(flutterwave_public_key) > 0 THEN CONCAT(LEFT(flutterwave_public_key, 15), '...')
        ELSE 'Not Set'
    END AS 'Public Key',
    CASE 
        WHEN LENGTH(flutterwave_secret_key) > 0 THEN CONCAT(LEFT(flutterwave_secret_key, 15), '...')
        ELSE 'Not Set'
    END AS 'Secret Key',
    flutterwave_minimum AS 'Minimum (NGN)',
    flutterwave_default_currency AS 'Default Currency'
FROM settings
WHERE id = 1;

-- ============================================================================
-- Query to view recent Flutterwave transactions
-- ============================================================================

SELECT 
    id,
    user_id,
    transaction_ref,
    amount,
    currency,
    status,
    payment_method,
    JSON_EXTRACT(metadata, '$.original_amount') AS original_amount,
    JSON_EXTRACT(metadata, '$.original_currency') AS original_currency,
    JSON_EXTRACT(metadata, '$.converted_ngn') AS converted_ngn,
    created_at,
    updated_at
FROM transactions
WHERE payment_gateway = 'flutterwave'
ORDER BY created_at DESC
LIMIT 20;

-- ============================================================================
-- Query to get Flutterwave transaction statistics
-- ============================================================================

SELECT 
    COUNT(*) AS total_transactions,
    COUNT(CASE WHEN status = 'successful' THEN 1 END) AS successful,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
    COUNT(CASE WHEN status = 'failed' THEN 1 END) AS failed,
    SUM(CASE WHEN status = 'successful' THEN amount ELSE 0 END) AS total_successful_amount,
    AVG(CASE WHEN status = 'successful' THEN amount END) AS avg_successful_amount,
    MIN(created_at) AS first_transaction,
    MAX(created_at) AS last_transaction
FROM transactions
WHERE payment_gateway = 'flutterwave';

-- ============================================================================
-- Query to view Flutterwave transactions by currency
-- ============================================================================

SELECT 
    currency,
    COUNT(*) AS transaction_count,
    COUNT(CASE WHEN status = 'successful' THEN 1 END) AS successful_count,
    SUM(CASE WHEN status = 'successful' THEN amount ELSE 0 END) AS total_ngn_amount,
    AVG(CASE WHEN status = 'successful' THEN amount END) AS avg_ngn_amount
FROM transactions
WHERE payment_gateway = 'flutterwave'
GROUP BY currency
ORDER BY transaction_count DESC;

-- ============================================================================
-- Query to find users who have made Flutterwave deposits
-- ============================================================================

SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(t.id) AS flutterwave_deposits,
    SUM(CASE WHEN t.status = 'successful' THEN t.amount ELSE 0 END) AS total_deposited_ngn,
    MAX(t.created_at) AS last_deposit_date
FROM users u
INNER JOIN transactions t ON u.id = t.user_id
WHERE t.payment_gateway = 'flutterwave'
GROUP BY u.id, u.username, u.email
ORDER BY total_deposited_ngn DESC
LIMIT 50;

-- ============================================================================
-- TROUBLESHOOTING QUERIES
-- ============================================================================

-- Check for stuck pending transactions (older than 1 hour)
SELECT 
    id,
    user_id,
    transaction_ref,
    amount,
    currency,
    status,
    created_at,
    TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS minutes_pending
FROM transactions
WHERE payment_gateway = 'flutterwave'
AND status = 'pending'
AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY created_at ASC;

-- Check for duplicate transaction references
SELECT 
    transaction_ref,
    COUNT(*) AS duplicate_count
FROM transactions
WHERE payment_gateway = 'flutterwave'
GROUP BY transaction_ref
HAVING COUNT(*) > 1;

-- Check transactions with missing metadata
SELECT 
    id,
    transaction_ref,
    amount,
    currency,
    status,
    metadata
FROM transactions
WHERE payment_gateway = 'flutterwave'
AND (metadata IS NULL OR metadata = '' OR metadata = '{}')
ORDER BY created_at DESC
LIMIT 20;

-- ============================================================================
-- MAINTENANCE QUERIES
-- ============================================================================

-- Clean up old failed transactions (older than 30 days)
/*
DELETE FROM transactions
WHERE payment_gateway = 'flutterwave'
AND status = 'failed'
AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
*/

-- Archive old successful transactions (older than 1 year)
/*
CREATE TABLE IF NOT EXISTS transactions_archive LIKE transactions;

INSERT INTO transactions_archive
SELECT * FROM transactions
WHERE payment_gateway = 'flutterwave'
AND status = 'successful'
AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- After verifying archive, delete from main table:
-- DELETE FROM transactions WHERE id IN (SELECT id FROM transactions_archive);
*/

-- ============================================================================
-- BACKUP COMMANDS (Run before making changes)
-- ============================================================================

/*
-- Backup settings table
CREATE TABLE settings_backup_flutterwave AS SELECT * FROM settings;

-- Backup transactions table
CREATE TABLE transactions_backup_flutterwave AS 
SELECT * FROM transactions WHERE payment_gateway = 'flutterwave';

-- Restore from backup if needed:
-- UPDATE settings s 
-- INNER JOIN settings_backup_flutterwave b ON s.id = b.id
-- SET s.flutterwave_enabled = b.flutterwave_enabled,
--     s.flutterwave_public_key = b.flutterwave_public_key,
--     s.flutterwave_secret_key = b.flutterwave_secret_key;
*/

-- ============================================================================
-- VERIFICATION CHECKLIST
-- ============================================================================

/*
✓ Settings table has all Flutterwave columns
✓ Default values are set correctly
✓ Transactions table supports payment_gateway = 'flutterwave'
✓ Metadata column can store JSON (original_amount, currency, converted_ngn)
✓ Indexes are created for better performance
✓ Configuration is set in admin panel or via SQL

NEXT STEPS:
1. Add your Flutterwave API keys in Admin Panel → Settings → Payment Gateways
2. Set minimum deposit amount
3. Choose default currency (NGN recommended)
4. Enable Flutterwave gateway
5. Test with Flutterwave test keys first
6. Use test cards from: https://developer.flutterwave.com/docs/test-cards
7. Monitor transactions table for successful deposits
*/

-- ============================================================================
-- END OF SQL SETUP SCRIPT
-- ============================================================================
