-- BetaPay Payment Gateway Database Dump
-- Compatible with MySQL 5.7+ / 8.0+
-- Target Database: payment_gateway

SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------
-- Table structure for table `users`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `role` ENUM('super_admin','admin','staff','support') NOT NULL DEFAULT 'staff',
  `status` ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `last_login_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `clients`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `business_name` VARCHAR(255) NOT NULL,
  `legal_name` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `phone` VARCHAR(20) DEFAULT NULL,
  `website` VARCHAR(255) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `country` VARCHAR(100) DEFAULT 'India',
  `pincode` VARCHAR(10) DEFAULT NULL,
  `api_key` VARCHAR(255) NOT NULL UNIQUE,
  `secret_key` VARCHAR(255) NOT NULL UNIQUE,
  `webhook_url` VARCHAR(255) DEFAULT NULL,
  `webhook_secret` VARCHAR(255) DEFAULT NULL,
  `commission_rate` DECIMAL(5,2) NOT NULL DEFAULT 2.00,
  `settlement_cycle` ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'daily',
  `monthly_limit` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('pending','active','suspended','rejected') NOT NULL DEFAULT 'pending',
  `kyc_status` ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `created_by` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_clients_status` (`status`),
  INDEX `idx_clients_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `gateways`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `gateways`;
CREATE TABLE `gateways` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `provider_type` ENUM('chinese','international','local') NOT NULL DEFAULT 'local',
  `api_endpoint` VARCHAR(255) NOT NULL,
  `api_key` VARCHAR(255) DEFAULT NULL,
  `api_secret` VARCHAR(255) DEFAULT NULL,
  `merchant_id` VARCHAR(255) DEFAULT NULL,
  `webhook_url` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('active','inactive','maintenance') NOT NULL DEFAULT 'inactive',
  `priority` INT NOT NULL DEFAULT 1,
  `settings` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `payment_methods`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `payment_methods`;
CREATE TABLE `payment_methods` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `transactions`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_uuid` VARCHAR(100) NOT NULL UNIQUE,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `gateway_id` BIGINT UNSIGNED DEFAULT NULL,
  `order_id` VARCHAR(100) NOT NULL,
  `payment_ref` VARCHAR(100) DEFAULT NULL,
  `customer_name` VARCHAR(255) DEFAULT NULL,
  `customer_email` VARCHAR(255) DEFAULT NULL,
  `customer_phone` VARCHAR(20) DEFAULT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `currency` VARCHAR(10) NOT NULL DEFAULT 'INR',
  `commission_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `net_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('pending','processing','success','failed','cancelled','refunded','disputed') NOT NULL DEFAULT 'pending',
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `gateway_response` JSON DEFAULT NULL,
  `request_payload` JSON DEFAULT NULL,
  `response_payload` JSON DEFAULT NULL,
  `callback_status` ENUM('pending','received','failed') NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_tx_client_id` (`client_id`),
  INDEX `idx_tx_order_id` (`order_id`),
  INDEX `idx_tx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `transaction_logs`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `transaction_logs`;
CREATE TABLE `transaction_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` BIGINT UNSIGNED NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `message` TEXT DEFAULT NULL,
  `request_data` JSON DEFAULT NULL,
  `response_data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_tx_log_tx_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `api_keys`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `api_keys`;
CREATE TABLE `api_keys` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `api_key` VARCHAR(255) NOT NULL UNIQUE,
  `secret_key` VARCHAR(255) NOT NULL UNIQUE,
  `environment` ENUM('sandbox','live') NOT NULL DEFAULT 'sandbox',
  `last_used_at` DATETIME DEFAULT NULL,
  `status` ENUM('active','revoked') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_api_keys_client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `webhooks`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `webhooks`;
CREATE TABLE `webhooks` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `transaction_id` BIGINT UNSIGNED DEFAULT NULL,
  `event_type` VARCHAR(100) NOT NULL,
  `endpoint_url` VARCHAR(255) NOT NULL,
  `request_headers` JSON DEFAULT NULL,
  `request_body` JSON DEFAULT NULL,
  `response_code` INT DEFAULT NULL,
  `response_body` JSON DEFAULT NULL,
  `delivery_status` ENUM('pending','success','failed','retrying') NOT NULL DEFAULT 'pending',
  `retry_count` INT NOT NULL DEFAULT 0,
  `next_retry_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_webhooks_client_id` (`client_id`),
  INDEX `idx_webhooks_delivery` (`delivery_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `commission_rules`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `commission_rules`;
CREATE TABLE `commission_rules` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` BIGINT UNSIGNED DEFAULT NULL,
  `gateway_id` BIGINT UNSIGNED DEFAULT NULL,
  `rule_name` VARCHAR(150) NOT NULL,
  `commission_type` ENUM('flat','percentage') NOT NULL DEFAULT 'percentage',
  `commission_value` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `min_amount` DECIMAL(15,2) DEFAULT NULL,
  `max_amount` DECIMAL(15,2) DEFAULT NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `settlements`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `settlements`;
CREATE TABLE `settlements` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `settlement_date` DATE NOT NULL,
  `gross_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `commission_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `net_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('pending','processed','paid','failed') NOT NULL DEFAULT 'pending',
  `reference_no` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_settlement_client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `refunds`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `refunds`;
CREATE TABLE `refunds` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` BIGINT UNSIGNED NOT NULL,
  `refund_uuid` VARCHAR(100) NOT NULL UNIQUE,
  `amount` DECIMAL(15,2) NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `status` ENUM('pending','approved','rejected','processed','failed') NOT NULL DEFAULT 'pending',
  `gateway_refund_id` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_refunds_tx_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `support_tickets`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `support_tickets`;
CREATE TABLE `support_tickets` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_by` BIGINT UNSIGNED DEFAULT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `priority` ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status` ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
  `assigned_to` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_tickets_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `audit_logs`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `action` VARCHAR(150) NOT NULL,
  `module_name` VARCHAR(100) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(100) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `notifications`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id` BIGINT UNSIGNED DEFAULT NULL,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `type` VARCHAR(100) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------
-- Table structure for table `settings`
-- ------------------------------------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(150) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('text','number','boolean','json') NOT NULL DEFAULT 'text',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------
-- Seeding Initial Data
-- ------------------------------------------------------

-- Admin users: admin@betapay.com (hashed password: password)
INSERT INTO `users` (`id`, `username`, `email`, `password`, `full_name`, `phone`, `role`, `status`, `created_at`, `updated_at`) VALUES
(1, 'admin', 'admin@betapay.com', '$2y$12$t419fF7q4U3LdG4sH2sFJu10Z06H.G0LqZtK7yT8G7X1b6Rk3T8Jq', 'BetaPay Admin', '+919999999999', 'super_admin', 'active', NOW(), NOW()),
(2, 'support_agent', 'support@betapay.com', '$2y$12$t419fF7q4U3LdG4sH2sFJu10Z06H.G0LqZtK7yT8G7X1b6Rk3T8Jq', 'BetaPay Support Team', '+918888888888', 'support', 'active', NOW(), NOW());

-- Default client: merchant@acme.com (hashed password checking: password)
INSERT INTO `clients` (`id`, `business_name`, `legal_name`, `email`, `phone`, `website`, `address`, `city`, `state`, `country`, `pincode`, `api_key`, `secret_key`, `webhook_url`, `webhook_secret`, `commission_rate`, `settlement_cycle`, `monthly_limit`, `status`, `kyc_status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'Acme Corporation', 'Acme Private Limited', 'merchant@acme.com', '+917777777777', 'https://acme.com', '123 Business Park, Sector 62', 'Noida', 'Uttar Pradesh', 'India', '201301', 'bp_key_acme123', 'bp_sec_acme456', 'https://webhook.site/mock', 'whsec_acme789', 2.50, 'daily', 5000000.00, 'active', 'verified', 1, NOW(), NOW());

-- API keys mapping for Acme
INSERT INTO `api_keys` (`id`, `client_id`, `name`, `api_key`, `secret_key`, `environment`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 'Default Sandbox Key', 'bp_key_acme123', 'bp_sec_acme456', 'sandbox', 'active', NOW(), NOW()),
(2, 1, 'Default Live Key', 'bp_live_key_acme123', 'bp_live_sec_acme456', 'live', 'active', NOW(), NOW());

-- Gateways config
INSERT INTO `gateways` (`id`, `name`, `code`, `provider_type`, `api_endpoint`, `api_key`, `api_secret`, `merchant_id`, `webhook_url`, `status`, `priority`, `settings`, `created_at`, `updated_at`) VALUES
(1, 'Stripe Mock Adapter', 'stripe', 'international', 'https://api.stripe.com/v1', 'sk_test_mock_stripe_key_123', 'whsec_mock_stripe_secret_123', 'acct_mock_stripe_123', 'http://localhost:8000/api/v1/webhook/gateway/stripe', 'active', 1, '{"charge_mode": "direct", "supported_currencies": ["USD", "EUR", "INR"]}', NOW(), NOW()),
(2, 'Razorpay Mock Adapter', 'razorpay', 'local', 'https://api.razorpay.com/v1', 'rzp_test_mock_key_123', 'rzp_test_mock_secret_123', 'mid_mock_razorpay_123', 'http://localhost:8000/api/v1/webhook/gateway/razorpay', 'active', 2, '{"theme_color": "#3399cc", "supported_currencies": ["INR"]}', NOW(), NOW());

-- Payment methods
INSERT INTO `payment_methods` (`id`, `name`, `code`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Credit / Debit Card', 'card', 'active', NOW(), NOW()),
(2, 'Unified Payments Interface', 'upi', 'active', NOW(), NOW()),
(3, 'Net Banking', 'netbanking', 'active', NOW(), NOW()),
(4, 'Digital Wallet', 'wallet', 'active', NOW(), NOW());

-- Core settings
INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `setting_type`, `created_at`, `updated_at`) VALUES
(1, 'rate_limit_per_minute', '60', 'number', NOW(), NOW()),
(2, 'kyc_required', '1', 'boolean', NOW(), NOW()),
(3, 'system_email', 'support@betapay.com', 'text', NOW(), NOW()),
(4, 'commission_rules_global', '{"default_rate": 2.00}', 'json', NOW(), NOW());

SET FOREIGN_KEY_CHECKS = 1;