-- Payment Gateway Database Schema
-- Drop existing tables if they exist
DROP TABLE IF EXISTS payment_transactions;
DROP TABLE IF EXISTS payment_links;
DROP TABLE IF EXISTS gateway_config;
DROP TABLE IF EXISTS admin_users;

-- Admin Users Table
CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    role ENUM('super_admin', 'admin', 'viewer') DEFAULT 'admin',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gateway Configuration Table
CREATE TABLE gateway_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_name VARCHAR(100) UNIQUE NOT NULL,
    api_key VARCHAR(500) NOT NULL,
    api_secret VARCHAR(500) NOT NULL,
    webhook_secret VARCHAR(500),
    currency VARCHAR(3) DEFAULT 'USD',
    mode ENUM('test', 'live') DEFAULT 'test',
    allowed_countries TEXT, -- JSON array
    business_name VARCHAR(255),
    business_email VARCHAR(255),
    success_url VARCHAR(500),
    cancel_url VARCHAR(500),
    is_active TINYINT(1) DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_config_name (config_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Links Table
CREATE TABLE payment_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    link_id VARCHAR(100) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    config_id INT NOT NULL,
    is_recurring TINYINT(1) DEFAULT 0,
    recurring_interval ENUM('daily', 'weekly', 'monthly', 'yearly') NULL,
    max_uses INT DEFAULT NULL, -- NULL means unlimited
    current_uses INT DEFAULT 0,
    expires_at TIMESTAMP NULL,
    custom_fields TEXT, -- JSON array for additional fields
    redirect_url VARCHAR(500),
    is_active TINYINT(1) DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (config_id) REFERENCES gateway_config(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_link_id (link_id),
    INDEX idx_expires_at (expires_at),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Transactions Table (Encrypted & Minimal Info)
CREATE TABLE payment_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id VARCHAR(255) UNIQUE NOT NULL,
    link_id VARCHAR(100),
    payment_intent_id VARCHAR(255), -- Stripe/Gateway reference
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('pending', 'processing', 'completed', 'failed', 'refunded', 'cancelled') DEFAULT 'pending',
    customer_email_hash VARCHAR(255), -- Hashed for privacy
    customer_ip_hash VARCHAR(255), -- Hashed for security
    card_last4 VARCHAR(4), -- Only last 4 digits
    card_brand VARCHAR(20), -- Visa, Mastercard, etc.
    country_code VARCHAR(2),
    payment_method VARCHAR(50),
    metadata TEXT, -- JSON encrypted metadata
    error_message TEXT,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (link_id) REFERENCES payment_links(link_id) ON DELETE SET NULL,
    INDEX idx_transaction_id (transaction_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_link_id (link_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Session tokens for security
CREATE TABLE admin_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    INDEX idx_session_token (session_token),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: Creators@7620 - CHANGE THIS!)
-- Username: Creatorschoice
-- Password: Creators@7620
-- 
-- IMPORTANT: If login fails, use create-admin.php to reset the password with correct hash
-- The hash below is generated. For guaranteed compatibility, run create-admin.php after importing this file.
INSERT INTO admin_users (username, password, email, full_name, role) 
VALUES ('Creatorschoice', '$2y$10$Xk5rJ8YgDZqE8L0XxzQJVeF4KwZ9nHq6P3LmN2vB5cT7dR8sW1uA.', 'admin@gateway.local', 'System Administrator', 'super_admin');

-- Sample configuration (test mode)
INSERT INTO gateway_config (config_name, api_key, api_secret, currency, mode, business_name, business_email, created_by)
VALUES ('default_config', 'pk_test_sample_key_here', 'sk_test_sample_secret_here', 'USD', 'test', 'My Business', 'business@example.com', 1);