-- Payment Gateway Database Schema
-- Drop existing tables if they exist

-- 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;
