-- Payment Gateways Configuration Table
-- This table stores configuration for different payment gateway providers
-- Supports multiple payment gateways with extensible configuration

CREATE TABLE IF NOT EXISTS `payment_gateways` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gateway_name` varchar(50) NOT NULL COMMENT 'Name of the payment gateway (e.g., paystack, stripe, flutterwave)',
  `display_name` varchar(100) NOT NULL COMMENT 'Display name shown to users',
  `gateway_type` enum('online','offline') NOT NULL DEFAULT 'online' COMMENT 'Type of payment gateway',
  `is_active` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether this gateway is currently active',
  `is_default` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether this is the default gateway',
  `logo_url` varchar(255) DEFAULT NULL COMMENT 'URL to gateway logo',
  `description` text DEFAULT NULL COMMENT 'Description of the payment gateway',
  `supported_currencies` text DEFAULT NULL COMMENT 'JSON array of supported currencies',
  `configuration` text DEFAULT NULL COMMENT 'JSON object containing gateway-specific configuration',
  `test_mode` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Whether gateway is in test/sandbox mode',
  `sort_order` int(11) NOT NULL DEFAULT '0' COMMENT 'Display order',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gateway_name` (`gateway_name`),
  KEY `is_active` (`is_active`),
  KEY `is_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default Paystack configuration
INSERT INTO `payment_gateways` (`gateway_name`, `display_name`, `gateway_type`, `is_active`, `is_default`, `description`, `supported_currencies`, `configuration`, `test_mode`, `sort_order`) VALUES
('paystack', 'Paystack', 'online', 0, 1, 'Accept payments via Paystack - Cards, Mobile Money, Bank Transfer, USSD', 
'["GHS","NGN","USD","ZAR","KES"]', 
'{"public_key":"","secret_key":"","webhook_url":"","callback_url":"","supported_channels":["card","bank","ussd","qr","mobile_money","bank_transfer"]}', 
1, 1);

-- Insert Stripe configuration (for future use)
INSERT INTO `payment_gateways` (`gateway_name`, `display_name`, `gateway_type`, `is_active`, `is_default`, `description`, `supported_currencies`, `configuration`, `test_mode`, `sort_order`) VALUES
('stripe', 'Stripe', 'online', 0, 0, 'Accept payments via Stripe - Cards and more', 
'["USD","EUR","GBP","GHS"]', 
'{"public_key":"","secret_key":"","webhook_secret":""}', 
1, 2);

-- Insert Flutterwave configuration (for future use)
INSERT INTO `payment_gateways` (`gateway_name`, `display_name`, `gateway_type`, `is_active`, `is_default`, `description`, `supported_currencies`, `configuration`, `test_mode`, `sort_order`) VALUES
('flutterwave', 'Flutterwave', 'online', 0, 0, 'Accept payments via Flutterwave - Cards, Mobile Money, Bank Transfer', 
'["GHS","NGN","USD","KES","ZAR","UGX"]', 
'{"public_key":"","secret_key":"","encryption_key":"","webhook_secret":""}', 
1, 3);

-- Insert Cash payment option
INSERT INTO `payment_gateways` (`gateway_name`, `display_name`, `gateway_type`, `is_active`, `is_default`, `description`, `supported_currencies`, `configuration`, `test_mode`, `sort_order`) VALUES
('cash', 'Cash Payment', 'offline', 1, 0, 'Pay with cash on property visit or at office', 
'["GHS","USD"]', 
'{"payment_instructions":"Please bring cash payment to our office or pay during property inspection."}', 
0, 4);

-- Insert Bank Transfer option
INSERT INTO `payment_gateways` (`gateway_name`, `display_name`, `gateway_type`, `is_active`, `is_default`, `description`, `supported_currencies`, `configuration`, `test_mode`, `sort_order`) VALUES
('bank_transfer', 'Bank Transfer', 'offline', 1, 0, 'Direct bank transfer to our account', 
'["GHS","USD"]', 
'{"bank_name":"","account_name":"","account_number":"","branch":"","swift_code":"","payment_instructions":""}', 
0, 5);

-- Payment Transactions Table (to track all payments)
CREATE TABLE IF NOT EXISTS `payment_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_ref` varchar(100) NOT NULL COMMENT 'Unique transaction reference',
  `booking_id` int(11) DEFAULT NULL COMMENT 'Related booking ID',
  `user_id` int(11) NOT NULL COMMENT 'User who made the payment',
  `gateway_id` int(11) NOT NULL COMMENT 'Payment gateway used',
  `gateway_name` varchar(50) NOT NULL COMMENT 'Gateway name for quick reference',
  `amount` decimal(10,2) NOT NULL COMMENT 'Payment amount',
  `currency` varchar(3) NOT NULL DEFAULT 'GHS',
  `status` enum('pending','processing','completed','failed','cancelled','refunded') NOT NULL DEFAULT 'pending',
  `payment_method` varchar(50) DEFAULT NULL COMMENT 'Specific payment method used (card, mobile_money, etc)',
  `gateway_response` text DEFAULT NULL COMMENT 'JSON response from payment gateway',
  `gateway_transaction_id` varchar(255) DEFAULT NULL COMMENT 'Transaction ID from gateway',
  `customer_email` varchar(255) DEFAULT NULL,
  `customer_phone` varchar(20) DEFAULT NULL,
  `metadata` text DEFAULT NULL COMMENT 'Additional metadata in JSON format',
  `paid_at` timestamp NULL DEFAULT NULL COMMENT 'When payment was completed',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_ref` (`transaction_ref`),
  KEY `booking_id` (`booking_id`),
  KEY `user_id` (`user_id`),
  KEY `gateway_id` (`gateway_id`),
  KEY `status` (`status`),
  KEY `gateway_transaction_id` (`gateway_transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign keys
ALTER TABLE `payment_transactions`
  ADD CONSTRAINT `fk_payment_gateway` FOREIGN KEY (`gateway_id`) REFERENCES `payment_gateways` (`id`) ON DELETE RESTRICT,
  ADD CONSTRAINT `fk_payment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT;