-- ============================================================
-- API RESELLER PANEL - COMPLETE DATABASE SCHEMA
-- PHP Laravel Project - MySQL/MariaDB Compatible
-- ============================================================

-- Create database (optional - uncomment if needed)
-- CREATE DATABASE IF NOT EXISTS `api_reseller` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `api_reseller`;

-- ============================================================
-- 1. USERS TABLE
-- ============================================================
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','reseller','user') NOT NULL DEFAULT 'user',
  `status` enum('active','blocked','pending') NOT NULL DEFAULT 'pending',
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_spent` decimal(12,2) NOT NULL DEFAULT 0.00,
  `api_key` varchar(64) DEFAULT NULL,
  `api_secret` varchar(128) DEFAULT NULL,
  `webhook_url` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_phone_unique` (`phone`),
  UNIQUE KEY `users_api_key_unique` (`api_key`),
  KEY `users_role_index` (`role`),
  KEY `users_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: admin123)
INSERT INTO `users` (`id`, `name`, `email`, `phone`, `password`, `role`, `status`, `balance`, `total_spent`, `api_key`, `api_secret`, `webhook_url`, `ip_address`, `email_verified_at`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Admin', 'admin@api.com', '9999999999', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active', 0.00, 0.00, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), NOW(), NULL);

-- ============================================================
-- 2. PASSWORD RESET TOKENS TABLE
-- ============================================================
DROP TABLE IF EXISTS `password_reset_tokens`;
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. SESSIONS TABLE
-- ============================================================
DROP TABLE IF EXISTS `sessions`;
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
-- ============================================================
DROP TABLE IF EXISTS `api_services`;
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
-- ============================================================
DROP TABLE IF EXISTS `api_pricings`;
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());

-- ============================================================
-- 6. API KEYS TABLE
-- ============================================================
DROP TABLE IF EXISTS `api_keys`;
CREATE TABLE `api_keys` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `key` varchar(64) NOT NULL,
  `secret` varchar(128) NOT NULL,
  `name` varchar(255) NOT NULL,
  `allowed_services` json DEFAULT NULL,
  `allowed_ips` varchar(500) DEFAULT NULL,
  `daily_limit` int(11) NOT NULL DEFAULT 1000,
  `hits_today` int(11) NOT NULL DEFAULT 0,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','revoked') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_keys_key_unique` (`key`),
  KEY `api_keys_user_id_index` (`user_id`),
  KEY `api_keys_status_index` (`status`),
  CONSTRAINT `api_keys_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. API HIT LOGS TABLE
-- ============================================================
DROP TABLE IF EXISTS `api_hit_logs`;
CREATE TABLE `api_hit_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `api_service_id` bigint(20) UNSIGNED NOT NULL,
  `api_key_id` bigint(20) UNSIGNED NOT NULL,
  `request_id` varchar(100) NOT NULL,
  `request_payload` longtext NOT NULL,
  `response_payload` longtext DEFAULT NULL,
  `charge_amount` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `ip_address` varchar(45) NOT NULL,
  `status` enum('success','failed','insufficient_balance') NOT NULL DEFAULT 'success',
  `response_time_ms` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_hit_logs_request_id_unique` (`request_id`),
  KEY `api_hit_logs_user_id_index` (`user_id`),
  KEY `api_hit_logs_api_service_id_index` (`api_service_id`),
  KEY `api_hit_logs_api_key_id_index` (`api_key_id`),
  KEY `api_hit_logs_status_index` (`status`),
  KEY `api_hit_logs_created_at_index` (`created_at`),
  CONSTRAINT `api_hit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `api_hit_logs_api_service_id_foreign` FOREIGN KEY (`api_service_id`) REFERENCES `api_services` (`id`),
  CONSTRAINT `api_hit_logs_api_key_id_foreign` FOREIGN KEY (`api_key_id`) REFERENCES `api_keys` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. BALANCE REQUESTS TABLE
-- ============================================================
DROP TABLE IF EXISTS `balance_requests`;
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
-- ============================================================
DROP TABLE IF EXISTS `transactions`;
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
-- ============================================================
DROP TABLE IF EXISTS `api_documentations`;
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());

-- ============================================================
-- 11. CACHE TABLE (for Laravel cache driver)
-- ============================================================
DROP TABLE IF EXISTS `cache`;
CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. CACHE LOCKS TABLE
-- ============================================================
DROP TABLE IF EXISTS `cache_locks`;
CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. JOBS TABLE (for queue)
-- ============================================================
DROP TABLE IF EXISTS `jobs`;
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
-- ============================================================
DROP TABLE IF EXISTS `failed_jobs`;
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
-- ============================================================
DROP TABLE IF EXISTS `migrations`;
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);

-- ============================================================
-- 16. PERSONAL ACCESS TOKENS (for Sanctum)
-- ============================================================
DROP TABLE IF EXISTS `personal_access_tokens`;
CREATE TABLE `personal_access_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SAMPLE DATA FOR TESTING
-- ============================================================

-- Sample Users
INSERT INTO `users` (`id`, `name`, `email`, `phone`, `password`, `role`, `status`, `balance`, `total_spent`, `api_key`, `api_secret`, `webhook_url`, `ip_address`, `email_verified_at`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES
(2, 'Test User', 'user@test.com', '8888888888', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'user', 'active', 500.00, 150.00, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), NOW(), NULL),
(3, 'Reseller One', 'reseller@test.com', '7777777777', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'reseller', 'active', 1000.00, 500.00, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), NOW(), NULL),
(4, 'Blocked User', 'blocked@test.com', '6666666666', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'user', 'blocked', 0.00, 0.00, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), NOW(), NULL);

-- Sample API Keys
INSERT INTO `api_keys` (`id`, `user_id`, `key`, `secret`, `name`, `allowed_services`, `allowed_ips`, `daily_limit`, `hits_today`, `last_used_at`, `status`, `created_at`, `updated_at`) VALUES
(1, 2, 'pk_testuser123456789abcdef123456789', 'sk_testuser987654321fedcba987654321', 'Production Key', '[1,2,3,4,5,6]', NULL, 1000, 45, NOW(), 'active', NOW(), NOW()),
(2, 2, 'pk_testuser_secondkey123456789abc', 'sk_testuser_secondsecret987654321', 'Test Key', '[1,2]', NULL, 100, 5, NOW(), 'active', NOW(), NOW()),
(3, 3, 'pk_reseller123456789abcdef1234567', 'sk_reseller987654321fedcba9876543', 'Main API Key', NULL, '192.168.1.1', 5000, 230, NOW(), 'active', NOW(), NOW());

-- 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 API Hit Logs
INSERT INTO `api_hit_logs` (`id`, `user_id`, `api_service_id`, `api_key_id`, `request_id`, `request_payload`, `response_payload`, `charge_amount`, `ip_address`, `status`, `response_time_ms`, `created_at`, `updated_at`) VALUES
(1, 2, 1, 1, 'req_abc123xyz', '{"aadhaar_number": "123456789012", "name": "Test User"}', '{"print_id": "PRT001", "status": "queued"}', 0.5000, '127.0.0.1', 'success', 245, NOW(), NOW()),
(2, 2, 2, 1, 'req_def456uvw', '{"pan_number": "ABCDE1234F", "name": "Test User"}', '{"print_id": "PRT002", "status": "processing"}', 0.6000, '127.0.0.1', 'success', 312, NOW(), NOW()),
(3, 3, 7, 3, 'req_ghi789rst', '{"upi_id": "test@upi"}', '{"account_holder": "Test User", "verified": true}', 1.0000, '192.168.1.1', 'success', 189, NOW(), NOW()),
(4, 2, 1, 1, 'req_failed001', '{"aadhaar_number": "invalid"}', '{"error": "Invalid Aadhaar number"}', 0.0000, '127.0.0.1', 'failed', 120, 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
-- ============================================================