
-- VerifyLead Pro - Production Database Schema
-- Compatible with MySQL 8.0+ or MariaDB 10.4+

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `credits_balance` INT NOT NULL DEFAULT 50,
  `plan_type` ENUM('free', 'pro', 'enterprise') NOT NULL DEFAULT 'free',
  `role` ENUM('admin', 'support', 'user') NOT NULL DEFAULT 'user',
  `status` ENUM('active', 'suspended', 'pending') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for domain_stats
-- ----------------------------
CREATE TABLE IF NOT EXISTS `domain_stats` (
  `domain` VARCHAR(255) NOT NULL,
  `is_catch_all` TINYINT(1) NOT NULL DEFAULT 0,
  `has_mx` TINYINT(1) NOT NULL DEFAULT 1,
  `avg_response_time` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_seen_status` VARCHAR(50) DEFAULT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for verification_jobs
-- ----------------------------
CREATE TABLE IF NOT EXISTS `verification_jobs` (
  `id` VARCHAR(50) NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `total_emails` INT UNSIGNED NOT NULL,
  `processed_emails` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_job_user` (`user_id`),
  KEY `idx_job_status` (`status`),
  CONSTRAINT `fk_job_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for verification_results
-- ----------------------------
CREATE TABLE IF NOT EXISTS `verification_results` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` VARCHAR(50) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `status` ENUM('Valid', 'Invalid', 'Catch-All', 'Unknown') NOT NULL,
  `confidence` TINYINT UNSIGNED NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_result_job` (`job_id`),
  KEY `idx_result_status` (`status`),
  CONSTRAINT `fk_result_job` FOREIGN KEY (`job_id`) REFERENCES `verification_jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table structure for transactions
-- ----------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10, 2) DEFAULT NULL,
  `currency` VARCHAR(10) DEFAULT 'USD',
  `credits_added` INT NOT NULL,
  `payment_method` ENUM('stripe', 'crypto') NOT NULL,
  `external_reference` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_user` (`user_id`),
  CONSTRAINT `fk_transaction_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
