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

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;
