CREATE TABLE `transactions` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `transaction_id` varchar(255) UNIQUE,
  `user_id` varchar(255),
  `type` varchar(100),
  `method` varchar(100),
  `amount` decimal(15,2),
  `status` varchar(100),
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ================================
-- Merchant Funding Sources (App Bank/Wallet)
-- ================================
CREATE TABLE `merchant_details` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `zumrails_bank_id` varchar(255),
  `wallet_id` varchar(255),
  `bank_name` varchar(255),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ================================
-- Users (stores ZumRails API response only)
-- ================================
CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `zumrails_id` varchar(100) NOT NULL,
  `is_company` tinyint(1) DEFAULT 0,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ================================
-- Auth Tokens (single-row cache, 20-min expiry)
-- ================================
CREATE TABLE `auth_tokens` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `token` text NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ================================
-- Bank Details (stores local bank info)
-- ================================
CREATE TABLE `bank_details` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `institution_number` varchar(10) NOT NULL,
  `transit_number` varchar(10) NOT NULL,
  `account_number` varchar(20) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ================================
-- Webhook Logs (for all incoming events)
-- ================================
CREATE TABLE `webhook_logs` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `event_id` varchar(255),
  `event_name` varchar(255),
  `event_type` varchar(100),
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ================================
-- EFT email client logos (match Data.Customer.CompanyName from webhook)
-- ================================
CREATE TABLE `email_brand_mappings` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `company_name` varchar(255) NOT NULL,
  `logo_filename` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  UNIQUE KEY `company_name` (`company_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ================================
-- EFT email send log (prevents duplicate sends per transaction + template)
-- ================================
CREATE TABLE `eft_email_send_log` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `transaction_id` varchar(255) NOT NULL,
  `template_key` varchar(100) NOT NULL,
  `recipient_email` varchar(255) NOT NULL,
  `funding_company` varchar(255) DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `error_message` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  UNIQUE KEY `tx_template` (`transaction_id`, `template_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;