-- Test Database Structure for SmartPanel Flutterwave Integration
-- SQLite version for testing

-- Payments table
CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type VARCHAR(255),
    name VARCHAR(225) NOT NULL,
    min DOUBLE NOT NULL,
    max DOUBLE NOT NULL,
    sort INTEGER,
    new_users INTEGER NOT NULL DEFAULT 0,
    status INTEGER NOT NULL DEFAULT 1,
    params TEXT NOT NULL
);

-- Transaction logs table  
CREATE TABLE IF NOT EXISTS transaction_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ids VARCHAR(100),
    uid INTEGER,
    type VARCHAR(100),
    transaction_id VARCHAR(255),
    amount DECIMAL(15,5),
    txn_fee DECIMAL(15,5),
    note TEXT,
    status INTEGER NOT NULL DEFAULT 0,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Users table (simplified)
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    balance DECIMAL(15,5) DEFAULT 0.00000,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Payment methods table
CREATE TABLE IF NOT EXISTS payment_methods (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    type VARCHAR(100) NOT NULL,
    status INTEGER NOT NULL DEFAULT 1,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert test data
INSERT INTO payments (type, name, min, max, sort, new_users, status, params) VALUES
('flutterwave', 'Flutterwave Payment Gateway (TEST)', 100, 1000000, 2, 1, 1, '{"type":"flutterwave","name":"Flutterwave Payment Gateway (TEST)","min":"100","max":"1000000","new_users":"1","status":"1","take_fee_from_user":"1","option":{"environment":"test","public_key":"FLWPUBK_TEST-SANDBOXDEMOKEY-X","secret_key":"FLWSECK_TEST-SANDBOXDEMOKEY-X","encryption_key":"FLWSECK_TEST-SANDBOXENCKEY-X","tnx_fee":"2.5","rate_to_usd":"1500","supported_currencies":["NGN","USD","EUR","GBP","KES","UGX","TZS"],"payment_methods":["card","banktransfer","ussd","mobilemoney"]}}');

-- Insert test payment method
INSERT INTO payment_methods (name, slug, type, status) VALUES
('Flutterwave', 'flutterwave', 'automatic', 1);

-- Insert test user
INSERT INTO users (first_name, last_name, email, balance) VALUES
('John', 'Doe', 'john.doe@example.com', 0.00000);