

-- ============================================================
-- 3. SESSIONS TABLE
-- ============================================================
CREATE TABLE `sessions` (
  `id` varchar(128) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. API SERVICES TABLE
-- ============================================================
CREATE TABLE `api_services` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(100) NOT NULL,
  `provider_url` varchar(500) NOT NULL,
  `provider_key` varchar(500) DEFAULT NULL,
  `method` enum('GET','POST','PUT','DELETE') NOT NULL DEFAULT 'POST',
  `headers` json DEFAULT NULL,
  `parameters` json DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_services_slug_unique` (`slug`),
  KEY `api_services_status_index` (`status`),
  KEY `api_services_category_index` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample API services
INSERT INTO `api_services` (`id`, `name`, `slug`, `description`, `category`, `provider_url`, `provider_key`, `method`, `headers`, `parameters`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Aadhaar Print API', 'aadhaar-print-api', 'Print Aadhaar card with custom design', 'Print', 'https://api.provider.com/aadhaar/print', 'pk_provider_key_123', 'POST', '{"Content-Type": "application/json", "Accept": "application/json"}', '{"aadhaar_number": "string", "name": "string", "phone": "string"}', 'active', NOW(), NOW()),
(2, 'PAN Card Print API', 'pan-card-print-api', 'Print PAN card with high quality', 'Print', 'https://api.provider.com/pan/print', 'pk_provider_key_456', 'POST', '{"Content-Type": "application/json"}', '{"pan_number": "string", "name": "string", "dob": "date"}', 'active', NOW(), NOW()),
(3, 'Voter ID Print API', 'voter-id-print-api', 'Print Voter ID card', 'Print', 'https://api.provider.com/voter/print', 'pk_provider_key_789', 'POST', '{"Content-Type": "application/json"}', '{"voter_id": "string", "name": "string", "constituency": "string"}', 'active', NOW(), NOW()),
(4, 'Driving License Print API', 'driving-license-print-api', 'Print Driving License card', 'Print', 'https://api.provider.com/dl/print', 'pk_provider_key_abc', 'POST', '{"Content-Type": "application/json"}', '{"dl_number": "string", "name": "string", "valid_till": "date"}', 'active', NOW(), NOW()),
(5, 'Ration Card Print API', 'ration-card-print-api', 'Print Ration Card', 'Print', 'https://api.provider.com/ration/print', 'pk_provider_key_def', 'POST', '{"Content-Type": "application/json"}', '{"ration_number": "string", "head_name": "string", "members": "integer"}', 'active', NOW(), NOW()),
(6, 'SMS Gateway API', 'sms-gateway-api', 'Send SMS to any number', 'SMS', 'https://api.provider.com/sms/send', 'pk_provider_key_sms', 'POST', '{"Content-Type": "application/json"}', '{"phone": "string", "message": "string", "template_id": "string"}', 'active', NOW(), NOW()),
(7, 'UPI Verification API', 'upi-verification-api', 'Verify UPI ID details', 'Verify', 'https://api.provider.com/upi/verify', 'pk_provider_key_upi', 'GET', '{"Content-Type": "application/json"}', '{"upi_id": "string"}', 'active', NOW(), NOW()),
(8, 'Bank Account Verification API', 'bank-verification-api', 'Verify bank account details', 'Verify', 'https://api.provider.com/bank/verify', 'pk_provider_key_bank', 'POST', '{"Content-Type": "application/json"}', '{"account_number": "string", "ifsc": "string"}', 'active', NOW(), NOW());

-- ============================================================
-- 5. API PRICING TABLE
-- ============================================================
CREATE TABLE `api_pricings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `api_service_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `admin_cost` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `user_price` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `reseller_price` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `minimum_hit_charge` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `api_pricings_api_service_id_index` (`api_service_id`),
  KEY `api_pricings_user_id_index` (`user_id`),
  KEY `api_pricings_status_index` (`status`),
  CONSTRAINT `api_pricings_api_service_id_foreign` FOREIGN KEY (`api_service_id`) REFERENCES `api_services` (`id`) ON DELETE CASCADE,
  CONSTRAINT `api_pricings_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default pricing for all services
INSERT INTO `api_pricings` (`id`, `api_service_id`, `user_id`, `admin_cost`, `user_price`, `reseller_price`, `minimum_hit_charge`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 0.2500, 0.5000, 0.4000, 0.50, 'active', NOW(), NOW()),
(2, 2, NULL, 0.3000, 0.6000, 0.5000, 0.60, 'active', NOW(), NOW()),
(3, 3, NULL, 0.2000, 0.4000, 0.3500, 0.40, 'active', NOW(), NOW()),
(4, 4, NULL, 0.3500, 0.7000, 0.6000, 0.70, 'active', NOW(), NOW()),
(5, 5, NULL, 0.1500, 0.3000, 0.2500, 0.30, 'active', NOW(), NOW()),
(6, 6, NULL, 0.1000, 0.2000, 0.1800, 0.20, 'active', NOW(), NOW()),
(7, 7, NULL, 0.5000, 1.0000, 0.8000, 1.00, 'active', NOW(), NOW()),
(8, 8, NULL, 0.8000, 1.5000, 1.2000, 1.50, 'active', NOW(), NOW());

-- ============================================================
-- 8. BALANCE REQUESTS TABLE
-- ============================================================
CREATE TABLE `balance_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `utr_number` varchar(50) NOT NULL,
  `payment_method` varchar(20) NOT NULL DEFAULT 'upi',
  `screenshot` varchar(255) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `approved_by` bigint(20) UNSIGNED DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `admin_remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `balance_requests_utr_number_unique` (`utr_number`),
  KEY `balance_requests_user_id_index` (`user_id`),
  KEY `balance_requests_status_index` (`status`),
  KEY `balance_requests_approved_by_index` (`approved_by`),
  CONSTRAINT `balance_requests_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `balance_requests_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. TRANSACTIONS TABLE
-- ============================================================
CREATE TABLE `transactions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `txn_id` varchar(100) NOT NULL,
  `type` enum('credit','debit','refund') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `balance_before` decimal(12,2) NOT NULL,
  `balance_after` decimal(12,2) NOT NULL,
  `reference_type` varchar(50) NOT NULL,
  `reference_id` bigint(20) UNSIGNED DEFAULT NULL,
  `description` text NOT NULL,
  `status` enum('completed','pending','failed') NOT NULL DEFAULT 'completed',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transactions_txn_id_unique` (`txn_id`),
  KEY `transactions_user_id_index` (`user_id`),
  KEY `transactions_type_index` (`type`),
  KEY `transactions_reference_type_index` (`reference_type`),
  KEY `transactions_created_at_index` (`created_at`),
  CONSTRAINT `transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. API DOCUMENTATION TABLE
-- ============================================================
CREATE TABLE `api_documentations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `api_service_id` bigint(20) UNSIGNED NOT NULL,
  `endpoint` varchar(255) NOT NULL,
  `method` enum('GET','POST','PUT','DELETE') NOT NULL,
  `description` text NOT NULL,
  `request_params` json NOT NULL,
  `response_example` json NOT NULL,
  `error_codes` json NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `api_documentations_api_service_id_index` (`api_service_id`),
  KEY `api_documentations_sort_order_index` (`sort_order`),
  CONSTRAINT `api_documentations_api_service_id_foreign` FOREIGN KEY (`api_service_id`) REFERENCES `api_services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert documentation for services
INSERT INTO `api_documentations` (`id`, `api_service_id`, `endpoint`, `method`, `description`, `request_params`, `response_example`, `error_codes`, `sort_order`, `created_at`, `updated_at`) VALUES
(1, 1, '/api/v1/aadhaar-print-api', 'POST', 'Print Aadhaar card with custom PVC card design', 
 '{"aadhaar_number": {"type": "string", "required": true, "description": "12 digit Aadhaar number"}, "name": {"type": "string", "required": true}, "phone": {"type": "string", "required": true}, "address": {"type": "string", "required": false}}',
 '{"success": true, "request_id": "req_abc123", "data": {"print_id": "PRT001", "status": "queued", "estimated_time": "2 hours"}, "charged": 0.5000, "balance_left": 150.00}',
 '{"401": "Invalid API credentials", "402": "Insufficient balance", "403": "IP not whitelisted", "404": "Service not found", "429": "Daily limit exceeded", "500": "Provider error"}',
 1, NOW(), NOW()),

(2, 2, '/api/v1/pan-card-print-api', 'POST', 'Print PAN card with high quality PVC lamination',
 '{"pan_number": {"type": "string", "required": true, "description": "10 character PAN number"}, "name": {"type": "string", "required": true}, "dob": {"type": "date", "required": true}, "father_name": {"type": "string", "required": false}}',
 '{"success": true, "request_id": "req_def456", "data": {"print_id": "PRT002", "status": "processing", "estimated_time": "3 hours"}, "charged": 0.6000, "balance_left": 149.40}',
 '{"401": "Invalid API credentials", "402": "Insufficient balance", "403": "IP not whitelisted", "404": "Service not found", "429": "Daily limit exceeded", "500": "Provider error"}',
 1, NOW(), NOW()),

(3, 6, '/api/v1/sms-gateway-api', 'POST', 'Send SMS to any Indian mobile number',
 '{"phone": {"type": "string", "required": true, "description": "10 digit mobile number"}, "message": {"type": "string", "required": true, "max_length": 160}, "template_id": {"type": "string", "required": true, "description": "DLT approved template ID"}}',
 '{"success": true, "request_id": "req_ghi789", "data": {"message_id": "MSG001", "status": "sent", "delivery_status": "pending"}, "charged": 0.2000, "balance_left": 149.20}',
 '{"401": "Invalid API credentials", "402": "Insufficient balance", "403": "IP not whitelisted", "404": "Service not found", "429": "Daily limit exceeded", "500": "Provider error"}',
 1, NOW(), NOW()),

(4, 7, '/api/v1/upi-verification-api', 'GET', 'Verify UPI ID and get account holder name',
 '{"upi_id": {"type": "string", "required": true, "description": "UPI ID (e.g., name@upi)"}}',
 '{"success": true, "request_id": "req_jkl012", "data": {"upi_id": "name@upi", "account_holder": "John Doe", "bank_name": "SBI", "verified": true}, "charged": 1.0000, "balance_left": 148.20}',
 '{"401": "Invalid API credentials", "402": "Insufficient balance", "403": "IP not whitelisted", "404": "Service not found", "429": "Daily limit exceeded", "500": "Provider error"}',
 1, NOW(), NOW());

-- ============================================================
-- 13. JOBS TABLE (for queue)
-- ============================================================
CREATE TABLE `jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL,
  `reserved_at` int(10) UNSIGNED DEFAULT NULL,
  `available_at` int(10) UNSIGNED NOT NULL,
  `created_at` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. FAILED JOBS TABLE
-- ============================================================
CREATE TABLE `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. MIGRATIONS TABLE
-- ============================================================
CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert migration records
INSERT INTO `migrations` (`migration`, `batch`) VALUES
('0000_00_00_000000_create_users_table', 1),
('0000_00_00_000001_create_api_services_table', 1),
('0000_00_00_000002_create_api_pricings_table', 1),
('0000_00_00_000003_create_api_keys_table', 1),
('0000_00_00_000004_create_api_hit_logs_table', 1),
('0000_00_00_000005_create_balance_requests_table', 1),
('0000_00_00_000006_create_transactions_table', 1),
('0000_00_00_000007_create_api_documentations_table', 1);

-- Sample Transactions
INSERT INTO `transactions` (`id`, `user_id`, `txn_id`, `type`, `amount`, `balance_before`, `balance_after`, `reference_type`, `reference_id`, `description`, `status`, `created_at`, `updated_at`) VALUES
(1, 2, 'TXN20240101120001', 'credit', 500.00, 0.00, 500.00, 'balance_request', 1, 'Initial balance added by admin', 'completed', NOW(), NOW()),
(2, 2, 'TXN20240101150002', 'debit', 0.50, 500.00, 499.50, 'api_hit', 1, 'API Hit: Aadhaar Print API', 'completed', NOW(), NOW()),
(3, 2, 'TXN20240101150003', 'debit', 0.60, 499.50, 498.90, 'api_hit', 2, 'API Hit: PAN Card Print API', 'completed', NOW(), NOW()),
(4, 3, 'TXN20240101120004', 'credit', 1000.00, 0.00, 1000.00, 'balance_request', 2, 'UPI Payment approved. UTR: 123456789012', 'completed', NOW(), NOW()),
(5, 3, 'TXN20240101180005', 'debit', 1.00, 1000.00, 999.00, 'api_hit', 7, 'API Hit: UPI Verification API', 'completed', NOW(), NOW());

-- Sample Balance Requests
INSERT INTO `balance_requests` (`id`, `user_id`, `amount`, `utr_number`, `payment_method`, `screenshot`, `remarks`, `status`, `approved_by`, `approved_at`, `admin_remarks`, `created_at`, `updated_at`) VALUES
(1, 2, 500.00, '123456789012', 'upi', NULL, 'First recharge', 'approved', 1, NOW(), 'Payment verified successfully', NOW(), NOW()),
(2, 3, 1000.00, '987654321098', 'upi', NULL, 'Monthly recharge', 'approved', 1, NOW(), 'UTR verified', NOW(), NOW()),
(3, 2, 200.00, '111111111111', 'upi', NULL, 'Quick recharge', 'pending', NULL, NULL, NULL, NOW(), NOW()),
(4, 4, 100.00, '222222222222', 'upi', NULL, 'Test recharge', 'rejected', 1, NOW(), 'Invalid UTR number', NOW(), NOW());

-- ============================================================
-- END OF SQL FILE
-- ============================================================
