-- BillDesk Payment Gateway Database Schema

-- Create sales_orders table
CREATE TABLE IF NOT EXISTS `sales_orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `orderid` VARCHAR(50) NOT NULL UNIQUE,
    `bdorderid` VARCHAR(100) NULL,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `number` VARCHAR(20) NOT NULL,
    `address` TEXT NULL,
    `product_id` VARCHAR(50) NULL,
    `amount` DECIMAL(10, 2) NULL,
    `rdata` LONGTEXT NULL,
    `status` ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_orderid` (`orderid`),
    INDEX `idx_bdorderid` (`bdorderid`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create billdesk_api_log table
CREATE TABLE IF NOT EXISTS `billdesk_api_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `identifier` VARCHAR(200) NOT NULL,
    `data` LONGTEXT NOT NULL,
    `type` ENUM('create_api_request_string', 'create_api_response_string', 'payment_callback', 'error') DEFAULT 'create_api_request_string',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_identifier` (`identifier`),
    INDEX `idx_type` (`type`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create payments table
CREATE TABLE IF NOT EXISTS `payments` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT NOT NULL,
    `transaction_id` VARCHAR(100) NULL,
    `amount` DECIMAL(10, 2) NOT NULL,
    `currency` VARCHAR(3) DEFAULT '356',
    `status` ENUM('pending', 'success', 'failed') DEFAULT 'pending',
    `response_data` LONGTEXT NULL,
    `error_message` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `sales_orders` (`id`) ON DELETE CASCADE,
    INDEX `idx_transaction_id` (`transaction_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
