-- Binance P2P Orders Table
-- Stores all Binance P2P orders for automation

CREATE TABLE IF NOT EXISTS `binance_p2p_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(100) NOT NULL COMMENT 'Binance order number',
  `binance_order_id` varchar(100) DEFAULT NULL COMMENT 'Binance internal order ID',
  `order_status` varchar(50) NOT NULL DEFAULT 'pending' COMMENT 'pending, waiting_payment, paid, released, completed, canceled, appeal',
  `payment_status` varchar(50) DEFAULT NULL COMMENT 'pending, processing, completed, failed',
  `fiat_currency` varchar(10) NOT NULL COMMENT 'USD, EUR, MXN, etc.',
  `fiat_amount` decimal(20,2) NOT NULL COMMENT 'Amount in fiat currency',
  `crypto_currency` varchar(10) NOT NULL DEFAULT 'USDT' COMMENT 'Crypto currency being sold',
  `crypto_amount` decimal(20,8) DEFAULT NULL COMMENT 'Amount in crypto',
  `exchange_rate` decimal(20,8) DEFAULT NULL COMMENT 'Exchange rate at time of order',
  `buyer_nickname` varchar(255) DEFAULT NULL COMMENT 'Buyer nickname on Binance',
  `buyer_user_id` varchar(100) DEFAULT NULL COMMENT 'Buyer user ID on Binance',
  `payment_method` varchar(100) DEFAULT NULL COMMENT 'Payment method (bank transfer, etc.)',
  `payment_details` text DEFAULT NULL COMMENT 'JSON: Payment account details from buyer',
  `bridge_transfer_id` varchar(100) DEFAULT NULL COMMENT 'Bridge transfer ID if payment processed',
  `bridge_customer_id` varchar(100) DEFAULT NULL COMMENT 'Bridge customer ID used for payment',
  `bridge_external_account_id` varchar(100) DEFAULT NULL COMMENT 'Bridge external account ID used',
  `payment_processed_at` datetime DEFAULT NULL COMMENT 'When payment was sent via Bridge',
  `marked_paid_at` datetime DEFAULT NULL COMMENT 'When order was marked as paid on Binance',
  `released_at` datetime DEFAULT NULL COMMENT 'When crypto was released to buyer',
  `completed_at` datetime DEFAULT NULL COMMENT 'When order was completed',
  `error_message` text DEFAULT NULL COMMENT 'Error message if processing failed',
  `requires_manual_review` tinyint(1) DEFAULT 0 COMMENT '1 if order needs manual review',
  `review_reason` text DEFAULT NULL COMMENT 'Reason for manual review',
  `chat_messages_sent` tinyint(1) DEFAULT 0 COMMENT '1 if automatic chat message was sent',
  `last_message_sent_at` datetime DEFAULT NULL COMMENT 'When last chat message was sent',
  `binance_raw_data` text DEFAULT NULL COMMENT 'JSON: Full order data from Binance API',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_no` (`order_no`),
  KEY `order_status` (`order_status`),
  KEY `payment_status` (`payment_status`),
  KEY `created_at` (`created_at`),
  KEY `requires_manual_review` (`requires_manual_review`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Binance P2P Settings Table
-- Stores configuration for automation

CREATE TABLE IF NOT EXISTS `binance_p2p_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO `binance_p2p_settings` (`setting_key`, `setting_value`) VALUES
('bridge_customer_id', NULL),
('bridge_external_account_id', NULL),
('auto_payment_enabled', '1'),
('low_balance_threshold', '100'),
('low_balance_notified', '0'),
('last_balance_check', NULL),
('binance_api_key', NULL),
('binance_secret_key', NULL),
('order_poll_interval', '30'),
('payment_confirmation_delay', '5')
ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;

-- Binance P2P Notifications Table
-- Stores notifications for low balance, errors, etc.

CREATE TABLE IF NOT EXISTS `binance_p2p_notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notification_type` varchar(50) NOT NULL COMMENT 'low_balance, payment_failed, api_error, etc.',
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `order_no` varchar(100) DEFAULT NULL COMMENT 'Related order number if applicable',
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `notification_type` (`notification_type`),
  KEY `is_read` (`is_read`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

