-- P2P Trading Module SQL Schema and Initial Data

-- Table structure for table `p2p_ads`
CREATE TABLE IF NOT EXISTS `p2p_ads` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `asset_currency_id` bigint(20) unsigned NOT NULL,
  `fiat_currency_id` bigint(20) unsigned NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'buy, sell',
  `min_amount` decimal(28,8) NOT NULL,
  `max_amount` decimal(28,8) NOT NULL,
  `total_amount` decimal(28,8) NOT NULL,
  `price` decimal(28,8) NOT NULL,
  `payment_duration` int(11) NOT NULL COMMENT 'minutes',
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `auto_response_message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending' COMMENT 'pending, active, inactive, completed, cancelled',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p2p_ads_main_index` (`asset_currency_id`,`fiat_currency_id`,`status`,`type`,`price`),
  KEY `p2p_ads_user_id_status_index` (`user_id`,`status`),
  KEY `p2p_ads_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `p2p_ads_orders`
CREATE TABLE IF NOT EXISTS `p2p_ads_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ads_id` bigint(20) unsigned NOT NULL,
  `ad_owner_id` bigint(20) unsigned NOT NULL,
  `order_creator_id` bigint(20) unsigned NOT NULL,
  `seller_user_id` bigint(20) unsigned NOT NULL,
  `buyer_user_id` bigint(20) unsigned NOT NULL,
  `asset_currency_id` bigint(20) unsigned NOT NULL,
  `fiat_currency_id` bigint(20) unsigned NOT NULL,
  `payment_method_id` bigint(20) unsigned NOT NULL,
  `asset_amount` decimal(28,8) NOT NULL,
  `fiat_amount` decimal(28,8) NOT NULL,
  `price` decimal(28,8) NOT NULL,
  `fee_percent` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `fee_asset_amount` decimal(28,8) NOT NULL DEFAULT '0.00000000',
  `seller_locked_asset` decimal(28,8) NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending_payment',
  `note` text COLLATE utf8mb4_unicode_ci,
  `payment_deadline_at` timestamp NULL DEFAULT NULL,
  `marked_paid_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `expired_at` timestamp NULL DEFAULT NULL,
  `disputed_at` timestamp NULL DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `dispute_reason` text COLLATE utf8mb4_unicode_ci,
  `resolution_note` text COLLATE utf8mb4_unicode_ci,
  `resolved_by_admin_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p2p_ads_orders_status_payment_deadline_at_index` (`status`,`payment_deadline_at`),
  KEY `p2p_ads_orders_seller_user_id_status_index` (`seller_user_id`,`status`),
  KEY `p2p_ads_orders_buyer_user_id_status_index` (`buyer_user_id`,`status`),
  KEY `p2p_ads_orders_ads_id_status_index` (`ads_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `p2p_ads_order_messages`
CREATE TABLE IF NOT EXISTS `p2p_ads_order_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `sender_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'user,admin',
  `sender_id` bigint(20) unsigned NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `attachment_path` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attachment_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attachment_mime` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attachment_size` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p2p_ads_order_messages_order_id_created_at_index` (`order_id`,`created_at`),
  KEY `p2p_ads_order_messages_sender_type_sender_id_index` (`sender_type`,`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `p2p_payment_methods`
CREATE TABLE IF NOT EXISTS `p2p_payment_methods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `currency_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fields` json NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `p2p_payment_methods_currency_id_index` (`currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `p2p_user_payment_methods`
CREATE TABLE IF NOT EXISTS `p2p_user_payment_methods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `payment_method_id` bigint(20) unsigned NOT NULL,
  `fields` json NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `p2p_ads_payment_methods`
CREATE TABLE IF NOT EXISTS `p2p_ads_payment_methods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ads_id` bigint(20) unsigned NOT NULL,
  `payment_method_id` bigint(20) unsigned NOT NULL,
  `fields` json NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add column to users table
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `is_p2p_verified` tinyint(1) NOT NULL DEFAULT '0' AFTER `kyc`;

-- Seed Permissions
INSERT INTO `permissions` (`category`, `name`, `guard_name`, `created_at`, `updated_at`) VALUES
('P2P Trade Management', 'p2p-payment-method-manage', 'admin', NOW(), NOW()),
('P2P Trade Management', 'p2p-ads-manage', 'admin', NOW(), NOW()),
('P2P Trade Management', 'p2p-ads-approve', 'admin', NOW(), NOW()),
('P2P Trade Management', 'p2p-orders-manage', 'admin', NOW(), NOW()),
('P2P Trade Management', 'p2p-orders-resolve', 'admin', NOW(), NOW()),
('P2P Trade Management', 'p2p-orders-chat-manage', 'admin', NOW(), NOW());

-- Seed Notification Templates
INSERT INTO `templates` (`name`, `code`, `for`, `title`, `subject`, `salutation`, `email_body`, `button_level`, `button_link`, `footer_status`, `footer_body`, `short_codes`, `notification_status`, `email_status`, `sms_status`, `icon`, `sms_body`, `notification_body`, `created_at`, `updated_at`) VALUES
('P2P Trader Applied', 'p2p_trader_applied', 'Admin', 'P2P Trader Applied', 'New P2P Verified Trader Application', 'Hello Admin,', 'User [[full_name]] has applied for Verified Trader status. Please review the application in the admin panel.', 'Review Application', '[[kyc_status_link]]', 1, 'Regards,', '["[[full_name]]","[[kyc_status_link]]"]', 1, 1, 0, 'user-check', NULL, 'User [[full_name]] has applied for Verified Trader status.', NOW(), NOW()),
('P2P Trader Approved', 'p2p_trader_approved', 'User', 'P2P Trader Approved', 'Verified Trader Status Approved', 'Hello [[full_name]],', 'Congratulations! Your application for Verified Trader status has been approved. You now have a verified badge on your P2P advertisements.', NULL, NULL, 1, 'Regards,', '["[[full_name]]"]', 1, 1, 1, 'badge-check', 'Congratulations [[full_name]]! Your P2P Verified Trader application has been approved.', 'Your P2P Verified Trader application has been approved!', NOW(), NOW()),
('P2P Trader Rejected', 'p2p_trader_rejected', 'User', 'P2P Trader Rejected', 'Verified Trader Application Status', 'Hello [[full_name]],', 'We regret to inform you that your application for Verified Trader status has been rejected for the following reason: [[message]]. Please address these concerns and you may apply again.', NULL, NULL, 1, 'Regards,', '["[[full_name]]","[[message]]"]', 1, 1, 1, 'user-x', 'Hello [[full_name]], your P2P Verified Trader application was rejected: [[message]].', 'Your P2P Verified Trader application was rejected: [[message]].', NOW(), NOW()),
('P2P New Order (Seller)', 'p2p_order_created_seller', 'User', 'New P2P Order Received', 'New P2P Order #[[order_id]]', 'Hello [[seller_name]],', 'You have received a new P2P order [[asset_amount]] [[asset_symbol]] for [[fiat_amount]] [[fiat_symbol]]. Please wait for the buyer to mark the payment.', NULL, NULL, 1, 'Regards,', '["[[seller_name]]","[[order_id]]","[[asset_amount]]","[[asset_symbol]]","[[fiat_amount]]","[[fiat_symbol]]"]', 1, 1, 1, 'shopping-cart', 'New P2P order #[[order_id]] received for [[fiat_amount]] [[fiat_symbol]].', 'New P2P order #[[order_id]] received.', NOW(), NOW()),
('P2P Order Initiated (Buyer)', 'p2p_order_created_buyer', 'User', 'P2P Order Initiated', 'P2P Order #[[order_id]] Initiated', 'Hello [[buyer_name]],', 'Your P2P order #[[order_id]] has been initiated. Please complete the payment of [[fiat_amount]] [[fiat_symbol]] within the allowed time.', NULL, NULL, 1, 'Regards,', '["[[buyer_name]]","[[order_id]]","[[fiat_amount]]","[[fiat_symbol]]"]', 1, 1, 1, 'clock', 'Your P2P order #[[order_id]] is initiated. Please pay [[fiat_amount]] [[fiat_symbol]].', 'P2P order #[[order_id]] initiated.', NOW(), NOW()),
('P2P Payment Marked (Seller)', 'p2p_order_paid_seller', 'User', 'Buyer Marked Payment', 'P2P Order #[[order_id]] Marked as Paid', 'Hello [[seller_name]],', 'The buyer [[buyer_name]] has marked the P2P order #[[order_id]] as paid. Please verify the payment and release the assets.', NULL, NULL, 1, 'Regards,', '["[[seller_name]]","[[buyer_name]]","[[order_id]]"]', 1, 1, 1, 'dollar-sign', 'Buyer has marked P2P order #[[order_id]] as paid. Please release assets.', 'P2P order #[[order_id]] marked as paid.', NOW(), NOW()),
('P2P Order Released (Buyer)', 'p2p_order_released_buyer', 'User', 'Assets Released', 'P2P Order #[[order_id]] Assets Released', 'Hello [[buyer_name]],', 'The seller [[seller_name]] has released the assets for your P2P order #[[order_id]]. [[asset_amount]] [[asset_symbol]] has been credited to your wallet.', NULL, NULL, 1, 'Regards,', '["[[buyer_name]]","[[seller_name]]","[[order_id]]","[[asset_amount]]","[[asset_symbol]]"]', 1, 1, 1, 'check-circle', 'Assets released for P2P order #[[order_id]]. [[asset_amount]] [[asset_symbol]] credited.', 'Assets released for P2P order #[[order_id]].', NOW(), NOW()),
('P2P Order Cancelled', 'p2p_order_cancelled', 'User', 'P2P Order Cancelled', 'P2P Order #[[order_id]] Cancelled', 'Hello [[full_name]],', 'The P2P order #[[order_id]] has been cancelled by [[cancelled_by_name]]. Assets have been returned to the seller if applicable.', NULL, NULL, 1, 'Regards,', '["[[full_name]]","[[order_id]]","[[cancelled_by_name]]"]', 1, 1, 1, 'x-circle', 'P2P order #[[order_id]] has been cancelled.', 'P2P order #[[order_id]] cancelled.', NOW(), NOW()),
('P2P Order Disputed', 'p2p_order_disputed', 'User', 'Dispute Raised', 'Dispute Raised for P2P Order #[[order_id]]', 'Hello [[full_name]],', 'A dispute has been raised for the P2P order #[[order_id]] by [[disputed_by_name]]. An admin will review the case.', NULL, NULL, 1, 'Regards,', '["[[full_name]]","[[order_id]]","[[disputed_by_name]]"]', 1, 1, 1, 'alert-triangle', 'Dispute raised for P2P order #[[order_id]].', 'Dispute raised for P2P order #[[order_id]].', NOW(), NOW()),
('P2P Dispute Resolved', 'p2p_order_resolved', 'User', 'Dispute Resolved', 'P2P Order #[[order_id]] Dispute Resolved', 'Hello [[full_name]],', 'The dispute for P2P order #[[order_id]] has been resolved by an admin. Resolution: [[resolution_action]]. Notes: [[resolution_note]]', NULL, NULL, 1, 'Regards,', '["[[full_name]]","[[order_id]]","[[resolution_action]]","[[resolution_note]]"]', 1, 1, 1, 'check-square', 'Dispute for P2P order #[[order_id]] resolved.', 'Dispute for P2P order #[[order_id]] resolved.', NOW(), NOW()),
('P2P Order Expired', 'p2p_order_expired', 'User', 'Order Expired', 'P2P Order #[[order_id]] Expired', 'Hello [[full_name]],', 'The P2P order #[[order_id]] has expired as the payment was not marked within the allowed time.', NULL, NULL, 1, 'Regards,', '["[[full_name]]","[[order_id]]"]', 1, 1, 1, 'timer-off', 'P2P order #[[order_id]] has expired.', 'P2P order #[[order_id]] expired.', NOW(), NOW()),
('P2P New Message', 'p2p_order_message', 'User', 'New Message Received', 'New Message for P2P Order #[[order_id]]', 'Hello [[full_name]],', 'You have received a new message for your P2P order #[[order_id]] from [[sender_name]].', NULL, NULL, 1, 'Regards,', '["[[full_name]]","[[order_id]]","[[sender_name]]"]', 1, 1, 0, 'message-square', NULL, 'New message for P2P order #[[order_id]].', NOW(), NOW()),
('P2P Ad Pending (Admin)', 'p2p_ad_pending_admin', 'Admin', 'New P2P Ad for Approval', 'New P2P Ad #[[ad_id]] Pending Approval', 'Hello Admin,', 'User [[full_name]] has created a new P2P ad which requires your approval.', 'Review Ad', '[[ad_link]]', 1, 'Regards,', '["[[full_name]]","[[ad_id]]","[[ad_link]]"]', 1, 1, 0, 'file-plus', NULL, 'New P2P ad pending approval from [[full_name]].', NOW(), NOW()),
('P2P Ad Status Update', 'p2p_ad_status_update', 'User', 'Ad Status Update', 'Your P2P Ad #[[ad_id]] is [[status]]', 'Hello [[full_name]],', 'Your P2P ad #[[ad_id]] status has been updated to [[status]] by an admin.', NULL, NULL, 1, 'Regards,', '["[[full_name]]","[[ad_id]]","[[status]]"]', 1, 1, 1, 'info', 'Your P2P ad #[[ad_id]] is now [[status]].', 'P2P ad #[[ad_id]] status update: [[status]].', NOW(), NOW());

-- Seed Cron Jobs
INSERT INTO `cron_jobs` (`name`, `next_run_at`, `schedule`, `type`, `reserved_method`, `status`, `created_at`, `updated_at`) VALUES
('P2P Order Timeouts', DATE_ADD(NOW(), INTERVAL 1 MINUTE), 60, 'system', 'p2pOrderTimeouts', 'running', NOW(), NOW());