CREATE TABLE `property_transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255),
  `buyer_id` INT,
  `seller_id` INT,
  `amount` DECIMAL(10,2),
  `status` VARCHAR(50) DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `escrow_webhook_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `escrow_transaction_id` VARCHAR(100),
  `payload` TEXT, -- full JSON payload from webhook
  `received_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE `escrow_mappings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `internal_transaction_id` INT NOT NULL, -- ID from your own system (e.g., property sale, listing, etc.)
  `escrow_transaction_id` VARCHAR(100) NOT NULL, -- ID from Escrow.com (e.g., "txn_ab123")
  `escrow_status` VARCHAR(50), -- e.g., "payment_sent", "in_progress", "completed"
  `last_synced_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY `escrow_transaction_id_unique` (`escrow_transaction_id`),
  INDEX `internal_transaction_idx` (`internal_transaction_id`)
);