CREATE DATABASE IF NOT EXISTS swiftpay_api_marketplace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE swiftpay_api_marketplace;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    api_key VARCHAR(100) UNIQUE NOT NULL,
    role ENUM('admin','user') DEFAULT 'user',
    status ENUM('active','suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallets (
    user_id INT PRIMARY KEY,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    frozen TINYINT(1) NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    display_name VARCHAR(150) NOT NULL,
    description TEXT,
    category VARCHAR(60) DEFAULT 'general',
    endpoint_url VARCHAR(500) NOT NULL,
    request_method ENUM('GET','POST') DEFAULT 'POST',
    static_headers TEXT NULL,
    default_price DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    rate_limit_per_min INT NOT NULL DEFAULT 60,
    rate_limit_per_day INT NOT NULL DEFAULT 5000,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    docs TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_service_rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_id INT NOT NULL,
    custom_price DECIMAL(8,4) NULL,
    rate_limit_per_min INT NULL,
    rate_limit_per_day INT NULL,
    is_blocked TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY user_service (user_id, service_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_id INT NULL,
    amount DECIMAL(12,4) NOT NULL,
    type ENUM('credit','debit') NOT NULL,
    description VARCHAR(255),
    status ENUM('success','failed') DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_id INT NOT NULL,
    ip_address VARCHAR(45),
    request_data TEXT,
    response_data TEXT,
    http_status INT,
    cost DECIMAL(10,4),
    status ENUM('success','failed') DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (service_id) REFERENCES services(id)
) ENGINE=InnoDB;

CREATE INDEX idx_requests_user_time ON api_requests(user_id, created_at);
CREATE INDEX idx_requests_service_time ON api_requests(service_id, created_at);

-- Default admin: admin / admin123
-- IMPORTANT: change this password immediately after first login.
INSERT INTO users (username,email,password,api_key,role)
SELECT 'admin','admin@example.com',
'$2y$12$ydfOSY/N72sqagChtJFrCuIwvLuDy/mXPaoDZC/Lb0iUJDexmaNyC',
CONCAT('sph_admin_', SHA2(RAND(),256)),
'admin'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username='admin');

INSERT INTO wallets (user_id,balance)
SELECT id,0.00 FROM users u
WHERE u.username='admin'
AND NOT EXISTS (SELECT 1 FROM wallets w WHERE w.user_id=u.id);