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

CREATE TABLE merchants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id VARCHAR(32) UNIQUE NOT NULL,
    merchant_name VARCHAR(128) NOT NULL,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    secret_key VARCHAR(64) UNIQUE NOT NULL,
    webhook_secret VARCHAR(64) DEFAULT NULL,
    callback_url VARCHAR(512) DEFAULT NULL,
    return_url VARCHAR(512) DEFAULT NULL,
    balance DECIMAL(12,2) DEFAULT 0.00,
    ip_whitelist TEXT DEFAULT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(32) UNIQUE NOT NULL,
    token VARCHAR(64) UNIQUE NOT NULL,
    merchant_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'INR',
    customer_name VARCHAR(128) DEFAULT NULL,
    customer_mobile VARCHAR(16) DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    is_reusable TINYINT(1) DEFAULT 0,
    status ENUM('PENDING','PAID','EXPIRED','FAILED') DEFAULT 'PENDING',
    txn_id VARCHAR(64) DEFAULT NULL,
    upi_id VARCHAR(64) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    paid_at TIMESTAMP NULL,
    callback_attempted TINYINT(1) DEFAULT 0,
    callback_response TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id),
    INDEX idx_token (token),
    INDEX idx_status_amount (status, amount)
) ENGINE=InnoDB;

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    txn_id VARCHAR(64) UNIQUE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    upi_id VARCHAR(64) NOT NULL,
    status ENUM('SUCCESS','FAILED') NOT NULL,
    raw_email LONGTEXT DEFAULT NULL,
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

CREATE TABLE payment_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT DEFAULT NULL,
    action VARCHAR(64) NOT NULL,
    status ENUM('SUCCESS','FAILED','PENDING') NOT NULL,
    message TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

CREATE TABLE rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(64) NOT NULL,
    endpoint VARCHAR(128) NOT NULL,
    request_count INT DEFAULT 1,
    window_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_window (identifier, endpoint, window_start)
) ENGINE=InnoDB;

INSERT INTO merchants (merchant_id, merchant_name, api_key, secret_key, webhook_secret, callback_url, return_url, ip_whitelist)
VALUES ('MERCH001', 'Furniture Store', 'pi_live_e09c7a6b236435e459582b66622b30f5a118aa126c40b795', 'sk_live_f6905dccca492860a8ab6bc4c418125cabd53a8127146617', 'whsec_test_abc123xyz', 'https://furniture-store.com/callback.php', 'https://furniture-store.com/success', '127.0.0.1,::1,192.168.1.100');