-- Enterprise Hybrid Payment Gateway Database Schema

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE `admins` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(50) NOT NULL DEFAULT 'admin',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `merchants` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `merchant_id_string` VARCHAR(100) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `webhook_url` TEXT,
  `allowed_ips` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_merchant_id_string` (`merchant_id_string`),
  UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `merchant_api_keys` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `merchant_id` INT NOT NULL,
  `public_key` VARCHAR(255) NOT NULL,
  `secret_key` VARCHAR(255) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_public_key` (`public_key`),
  KEY `fk_merchant_api_keys_merchant_id` (`merchant_id`),
  CONSTRAINT `fk_merchant_api_keys_merchant_id` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `merchant_wallets` (
  `merchant_id` INT NOT NULL,
  `payin_balance` DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
  `payout_balance` DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
  `pending_balance` DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
  `settlement_balance` DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`merchant_id`),
  CONSTRAINT `fk_merchant_wallets_merchant_id` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payment_providers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('PAYIN','PAYOUT') NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code_type` (`code`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `merchant_provider_configs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `merchant_id` INT NOT NULL,
  `provider_id` INT NOT NULL,
  `api_key` VARCHAR(255) DEFAULT NULL,
  `secret_key` VARCHAR(255) DEFAULT NULL,
  `salt_index` VARCHAR(50) DEFAULT NULL,
  `base_url` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_merchant_provider` (`merchant_id`,`provider_id`),
  KEY `fk_configs_provider_id` (`provider_id`),
  CONSTRAINT `fk_configs_merchant_id` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_configs_provider_id` FOREIGN KEY (`provider_id`) REFERENCES `payment_providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `upi_accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `merchant_provider_config_id` INT NOT NULL,
  `upi_id` VARCHAR(255) NOT NULL,
  `daily_limit` DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
  `current_volume` DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_upi_id` (`upi_id`),
  KEY `fk_upi_accounts_config_id` (`merchant_provider_config_id`),
  CONSTRAINT `fk_upi_accounts_config_id` FOREIGN KEY (`merchant_provider_config_id`) REFERENCES `merchant_provider_configs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transactions` (
  `id` VARCHAR(36) NOT NULL,
  `merchant_id` INT NOT NULL,
  `amount` DECIMAL(18,4) NOT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'PENDING',
  `utr` VARCHAR(255) DEFAULT NULL,
  `order_id` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_id` (`merchant_id`,`order_id`),
  KEY `idx_utr` (`utr`),
  KEY `fk_transactions_merchant_id` (`merchant_id`),
  CONSTRAINT `fk_transactions_merchant_id` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payouts` (
  `id` VARCHAR(36) NOT NULL,
  `merchant_id` INT NOT NULL,
  `provider_id` INT DEFAULT NULL,
  `amount` DECIMAL(18,4) NOT NULL,
  `account_number` VARCHAR(100) NOT NULL,
  `ifsc` VARCHAR(50) NOT NULL,
  `reference_id` VARCHAR(255) DEFAULT NULL,
  `utr` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'PENDING',
  `priority` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_reference_id` (`merchant_id`,`reference_id`),
  KEY `idx_utr` (`utr`),
  KEY `fk_payouts_merchant_id` (`merchant_id`),
  KEY `fk_payouts_provider_id` (`provider_id`),
  CONSTRAINT `fk_payouts_merchant_id` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payouts_provider_id` FOREIGN KEY (`provider_id`) REFERENCES `payment_providers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `webhook_queue` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `target_url` TEXT NOT NULL,
  `payload` JSON NOT NULL,
  `retry_count` INT NOT NULL DEFAULT 0,
  `status` VARCHAR(50) NOT NULL DEFAULT 'PENDING',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `next_retry_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status_next_retry` (`status`,`next_retry_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `gateways` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('payin', 'payout') NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `gateway_credentials` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `gateway_id` INT NOT NULL,
  `key_name` VARCHAR(100) NOT NULL,
  `key_value` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_gateway_key` (`gateway_id`, `key_name`),
  CONSTRAINT `fk_gateway_credentials_gateway_id` FOREIGN KEY (`gateway_id`) REFERENCES `gateways` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;