-- Database Schema

-- Table: activity_logs
CREATE TABLE `activity_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `action` varchar(255) NOT NULL,
  `model` varchar(255) DEFAULT NULL,
  `model_id` bigint(20) unsigned DEFAULT NULL,
  `description` text DEFAULT NULL,
  `properties` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`properties`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_logs_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `activity_logs_model_model_id_index` (`model`,`model_id`),
  CONSTRAINT `activity_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: api_requests
CREATE TABLE `api_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `api_token_id` bigint(20) unsigned NOT NULL,
  `endpoint` varchar(255) NOT NULL,
  `method` varchar(10) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `response_status` int(11) NOT NULL,
  `response_time_ms` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `api_requests_api_token_id_created_at_index` (`api_token_id`,`created_at`),
  KEY `api_requests_endpoint_index` (`endpoint`),
  KEY `api_requests_created_at_index` (`created_at`),
  CONSTRAINT `api_requests_api_token_id_foreign` FOREIGN KEY (`api_token_id`) REFERENCES `api_tokens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: api_tokens
CREATE TABLE `api_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `plain_token` text DEFAULT NULL,
  `abilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`abilities`)),
  `ip_whitelist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ip_whitelist`)),
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `api_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: backup_activity_logs
CREATE TABLE `backup_activity_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `backup_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `backup_activity_logs_backup_id_created_at_index` (`backup_id`,`created_at`),
  KEY `backup_activity_logs_action_index` (`action`),
  KEY `backup_activity_logs_user_id_index` (`user_id`),
  CONSTRAINT `backup_activity_logs_backup_id_foreign` FOREIGN KEY (`backup_id`) REFERENCES `backups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `backup_activity_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=187 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: backup_destinations
CREATE TABLE `backup_destinations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('local','ftp','sftp','ssh','s3','google_drive') NOT NULL DEFAULT 'local',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`config`)),
  `max_backups` int(11) NOT NULL DEFAULT 10,
  `retention_days` int(11) NOT NULL DEFAULT 10,
  `max_storage_mb` bigint(20) DEFAULT NULL,
  `total_backups` int(11) NOT NULL DEFAULT 0,
  `total_size_mb` bigint(20) NOT NULL DEFAULT 0,
  `last_backup_at` timestamp NULL DEFAULT NULL,
  `last_test_at` timestamp NULL DEFAULT NULL,
  `last_test_status` enum('success','failed','pending') DEFAULT NULL,
  `last_test_message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `backup_destinations_type_index` (`type`),
  KEY `backup_destinations_is_active_index` (`is_active`),
  KEY `backup_destinations_is_default_index` (`is_default`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: backup_restorations
CREATE TABLE `backup_restorations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `backup_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `selected_zones` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`selected_zones`)),
  `overwrite_existing` tinyint(1) NOT NULL DEFAULT 0,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`result`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `backup_restorations_backup_id_foreign` (`backup_id`),
  KEY `backup_restorations_user_id_foreign` (`user_id`),
  KEY `backup_restorations_status_index` (`status`),
  CONSTRAINT `backup_restorations_backup_id_foreign` FOREIGN KEY (`backup_id`) REFERENCES `backups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `backup_restorations_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: backup_schedules
CREATE TABLE `backup_schedules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `frequency` varchar(255) NOT NULL,
  `time` time NOT NULL DEFAULT '02:00:00',
  `day_of_week` int(11) DEFAULT NULL,
  `day_of_month` int(11) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `retention_days` int(11) NOT NULL DEFAULT 30,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)),
  `last_run_at` timestamp NULL DEFAULT NULL,
  `next_run_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `backup_schedules_enabled_index` (`enabled`),
  KEY `backup_schedules_next_run_at_index` (`next_run_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: backups
CREATE TABLE `backups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('full','incremental') NOT NULL DEFAULT 'full',
  `filename` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` bigint(20) NOT NULL DEFAULT 0,
  `includes_database` tinyint(1) NOT NULL DEFAULT 1,
  `includes_files` tinyint(1) NOT NULL DEFAULT 1,
  `tables_count` int(11) DEFAULT NULL,
  `records_count` int(11) DEFAULT NULL,
  `files_count` int(11) DEFAULT NULL,
  `parent_backup_id` bigint(20) unsigned DEFAULT NULL,
  `changes_since` timestamp NULL DEFAULT NULL,
  `status` enum('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
  `error_message` text DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `restored_at` timestamp NULL DEFAULT NULL,
  `restored_by` bigint(20) unsigned DEFAULT NULL,
  `is_encrypted` tinyint(1) NOT NULL DEFAULT 0,
  `uploaded_to` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`uploaded_to`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `backups_parent_backup_id_foreign` (`parent_backup_id`),
  KEY `backups_created_by_foreign` (`created_by`),
  KEY `backups_restored_by_foreign` (`restored_by`),
  KEY `backups_type_index` (`type`),
  KEY `backups_status_index` (`status`),
  KEY `backups_created_at_index` (`created_at`),
  CONSTRAINT `backups_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `backups_parent_backup_id_foreign` FOREIGN KEY (`parent_backup_id`) REFERENCES `backups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `backups_restored_by_foreign` FOREIGN KEY (`restored_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: cache
CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: cache_locks
CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: captcha_rate_limits
CREATE TABLE `captcha_rate_limits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `location` varchar(255) NOT NULL,
  `attempts` int(11) NOT NULL DEFAULT 1,
  `failed_attempts` int(11) NOT NULL DEFAULT 0,
  `first_attempt_at` timestamp NULL DEFAULT NULL,
  `last_attempt_at` timestamp NULL DEFAULT NULL,
  `blocked_until` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `captcha_rate_limits_ip_address_location_unique` (`ip_address`,`location`),
  KEY `captcha_rate_limits_blocked_until_index` (`blocked_until`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: captcha_settings
CREATE TABLE `captcha_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `provider` enum('hcaptcha','recaptcha_v2','recaptcha_v2_invisible','recaptcha_v3','turnstile','trustip') NOT NULL DEFAULT 'hcaptcha',
  `enabled` tinyint(1) NOT NULL DEFAULT 0,
  `site_key` varchar(255) DEFAULT NULL,
  `secret_key` varchar(255) DEFAULT NULL,
  `api_key` varchar(255) DEFAULT NULL,
  `recaptcha_v3_threshold` decimal(2,1) NOT NULL DEFAULT 0.5,
  `invisible_badge_hide` tinyint(1) NOT NULL DEFAULT 0,
  `enabled_login` tinyint(1) NOT NULL DEFAULT 1,
  `enabled_register` tinyint(1) NOT NULL DEFAULT 1,
  `enabled_forgot_password` tinyint(1) NOT NULL DEFAULT 1,
  `enabled_contact` tinyint(1) NOT NULL DEFAULT 0,
  `enabled_api` tinyint(1) NOT NULL DEFAULT 0,
  `whitelist_ips` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`whitelist_ips`)),
  `testing_mode` tinyint(1) NOT NULL DEFAULT 0,
  `test_key` varchar(255) DEFAULT NULL,
  `fallback_provider` enum('hcaptcha','recaptcha_v2','recaptcha_v2_invisible','recaptcha_v3','turnstile','trustip','none') NOT NULL DEFAULT 'none',
  `fallback_site_key` varchar(255) DEFAULT NULL,
  `fallback_secret_key` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: captcha_statistics
CREATE TABLE `captcha_statistics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `provider` enum('hcaptcha','recaptcha_v2','recaptcha_v2_invisible','recaptcha_v3','turnstile','trustip') NOT NULL,
  `location` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `success` tinyint(1) NOT NULL,
  `score` decimal(2,1) DEFAULT NULL,
  `error_code` varchar(255) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `response_time_ms` int(11) DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `verified_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `captcha_statistics_provider_location_created_at_index` (`provider`,`location`,`created_at`),
  KEY `captcha_statistics_success_created_at_index` (`success`,`created_at`),
  KEY `captcha_statistics_ip_address_index` (`ip_address`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_query_usage
CREATE TABLE `dns_query_usage` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `year` int(11) NOT NULL,
  `month` int(11) NOT NULL,
  `query_count` bigint(20) NOT NULL DEFAULT 0,
  `alert_80_sent` tinyint(1) NOT NULL DEFAULT 0,
  `alert_100_sent` tinyint(1) NOT NULL DEFAULT 0,
  `alert_80_sent_at` timestamp NULL DEFAULT NULL,
  `alert_100_sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dns_query_usage_user_id_year_month_unique` (`user_id`,`year`,`month`),
  KEY `dns_query_usage_user_id_year_month_index` (`user_id`,`year`,`month`),
  CONSTRAINT `dns_query_usage_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_record_changes
CREATE TABLE `dns_record_changes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dns_record_id` bigint(20) unsigned DEFAULT NULL,
  `dns_zone_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `record_name` varchar(255) NOT NULL,
  `record_type` varchar(255) NOT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dns_record_changes_dns_record_id_created_at_index` (`dns_record_id`,`created_at`),
  KEY `dns_record_changes_dns_zone_id_created_at_index` (`dns_zone_id`,`created_at`),
  KEY `dns_record_changes_user_id_created_at_index` (`user_id`,`created_at`),
  CONSTRAINT `dns_record_changes_dns_record_id_foreign` FOREIGN KEY (`dns_record_id`) REFERENCES `dns_records` (`id`) ON DELETE CASCADE,
  CONSTRAINT `dns_record_changes_dns_zone_id_foreign` FOREIGN KEY (`dns_zone_id`) REFERENCES `dns_zones` (`id`) ON DELETE CASCADE,
  CONSTRAINT `dns_record_changes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=490 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_records
CREATE TABLE `dns_records` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dns_zone_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `ttl` int(11) NOT NULL DEFAULT 3600,
  `priority` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dns_records_dns_zone_id_type_index` (`dns_zone_id`,`type`),
  CONSTRAINT `dns_records_dns_zone_id_foreign` FOREIGN KEY (`dns_zone_id`) REFERENCES `dns_zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2477 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_statistics
CREATE TABLE `dns_statistics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dns_zone_id` bigint(20) unsigned NOT NULL,
  `date` date NOT NULL,
  `query_count` bigint(20) unsigned NOT NULL DEFAULT 0,
  `query_types` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`query_types`)),
  `response_codes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`response_codes`)),
  `top_clients` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`top_clients`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dns_statistics_dns_zone_id_date_unique` (`dns_zone_id`,`date`),
  KEY `dns_statistics_dns_zone_id_date_index` (`dns_zone_id`,`date`),
  CONSTRAINT `dns_statistics_dns_zone_id_foreign` FOREIGN KEY (`dns_zone_id`) REFERENCES `dns_zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_template_records
CREATE TABLE `dns_template_records` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dns_template_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `ttl` int(11) NOT NULL DEFAULT 3600,
  `priority` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dns_template_records_dns_template_id_foreign` (`dns_template_id`),
  CONSTRAINT `dns_template_records_dns_template_id_foreign` FOREIGN KEY (`dns_template_id`) REFERENCES `dns_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_templates
CREATE TABLE `dns_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `allowed_plans` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`allowed_plans`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_zone_alerts
CREATE TABLE `dns_zone_alerts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dns_zone_id` bigint(20) unsigned NOT NULL,
  `alert_type` enum('nameserver_misconfigured','zone_offline','sync_failed') NOT NULL DEFAULT 'nameserver_misconfigured',
  `first_detected_at` timestamp NULL DEFAULT NULL,
  `last_checked_at` timestamp NULL DEFAULT NULL,
  `last_notified_at` timestamp NULL DEFAULT NULL,
  `notification_count` int(11) NOT NULL DEFAULT 0,
  `is_resolved` tinyint(1) NOT NULL DEFAULT 0,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dns_zone_alerts_dns_zone_id_is_resolved_index` (`dns_zone_id`,`is_resolved`),
  KEY `dns_zone_alerts_first_detected_at_index` (`first_detected_at`),
  CONSTRAINT `dns_zone_alerts_dns_zone_id_foreign` FOREIGN KEY (`dns_zone_id`) REFERENCES `dns_zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_zone_assignments
CREATE TABLE `dns_zone_assignments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `zone_name` varchar(255) NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `access_level` enum('full','records_only') NOT NULL DEFAULT 'full',
  `assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `assigned_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dns_zone_assignments_zone_name_user_id_unique` (`zone_name`,`user_id`),
  KEY `dns_zone_assignments_assigned_by_foreign` (`assigned_by`),
  KEY `dns_zone_assignments_zone_name_index` (`zone_name`),
  KEY `dns_zone_assignments_user_id_index` (`user_id`),
  CONSTRAINT `dns_zone_assignments_assigned_by_foreign` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`),
  CONSTRAINT `dns_zone_assignments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_zone_nameserver
CREATE TABLE `dns_zone_nameserver` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dns_zone_id` bigint(20) unsigned NOT NULL,
  `name_server_id` bigint(20) unsigned NOT NULL,
  `powerdns_zone_id` varchar(255) DEFAULT NULL,
  `is_synced` tinyint(1) NOT NULL DEFAULT 0,
  `last_synced_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dns_zone_nameserver_dns_zone_id_name_server_id_unique` (`dns_zone_id`,`name_server_id`),
  KEY `dns_zone_nameserver_name_server_id_foreign` (`name_server_id`),
  CONSTRAINT `dns_zone_nameserver_dns_zone_id_foreign` FOREIGN KEY (`dns_zone_id`) REFERENCES `dns_zones` (`id`) ON DELETE CASCADE,
  CONSTRAINT `dns_zone_nameserver_name_server_id_foreign` FOREIGN KEY (`name_server_id`) REFERENCES `name_servers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=226 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_zone_statistics
CREATE TABLE `dns_zone_statistics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(255) NOT NULL,
  `nameserver_id` bigint(20) unsigned DEFAULT NULL,
  `statistic_date` date NOT NULL,
  `period_type` varchar(255) NOT NULL DEFAULT 'daily',
  `raw_statistics` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`raw_statistics`)),
  `total_queries` bigint(20) NOT NULL DEFAULT 0,
  `cache_hits` bigint(20) NOT NULL DEFAULT 0,
  `cache_misses` bigint(20) NOT NULL DEFAULT 0,
  `servfail_responses` bigint(20) NOT NULL DEFAULT 0,
  `nxdomain_responses` bigint(20) NOT NULL DEFAULT 0,
  `noerror_responses` bigint(20) NOT NULL DEFAULT 0,
  `cache_hit_ratio` decimal(5,2) NOT NULL DEFAULT 0.00,
  `top_query_types` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`top_query_types`)),
  `top_client_ips` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`top_client_ips`)),
  `top_subdomains` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`top_subdomains`)),
  `geographic_distribution` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`geographic_distribution`)),
  `comparison_stats` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`comparison_stats`)),
  `protocol_distribution` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`protocol_distribution`)),
  `hourly_distribution` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`hourly_distribution`)),
  `hourly_queries` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`hourly_queries`)),
  `collected_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dns_stats_unique` (`domain`,`nameserver_id`,`statistic_date`,`period_type`),
  KEY `dns_zone_statistics_domain_statistic_date_period_type_index` (`domain`,`statistic_date`,`period_type`),
  KEY `dns_zone_statistics_nameserver_id_statistic_date_index` (`nameserver_id`,`statistic_date`),
  CONSTRAINT `dns_zone_statistics_nameserver_id_foreign` FOREIGN KEY (`nameserver_id`) REFERENCES `name_servers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=282 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dns_zones
CREATE TABLE `dns_zones` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `name_server_id` bigint(20) unsigned DEFAULT NULL,
  `domain` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL DEFAULT 'MASTER',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `suspended_at` timestamp NULL DEFAULT NULL,
  `suspension_reason` text DEFAULT NULL,
  `can_be_reactivated` tinyint(1) NOT NULL DEFAULT 0,
  `sync_to_powerdns` tinyint(1) NOT NULL DEFAULT 0,
  `dnssec_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `powerdns_zone_id` varchar(255) DEFAULT NULL,
  `allocated_nameservers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`allocated_nameservers`)),
  `is_powerdns_synced` tinyint(1) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dns_zones_domain_unique` (`domain`),
  KEY `dns_zones_user_id_foreign` (`user_id`),
  KEY `dns_zones_is_powerdns_synced_user_id_index` (`is_powerdns_synced`,`user_id`),
  KEY `dns_zones_name_server_id_index` (`name_server_id`),
  KEY `dns_zones_suspended_at_index` (`suspended_at`),
  KEY `dns_zones_can_be_reactivated_index` (`can_be_reactivated`),
  CONSTRAINT `dns_zones_name_server_id_foreign` FOREIGN KEY (`name_server_id`) REFERENCES `name_servers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `dns_zones_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: dynamic_dns_clients
CREATE TABLE `dynamic_dns_clients` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `dns_zone_id` bigint(20) unsigned NOT NULL,
  `hostname` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `current_ip` varchar(45) DEFAULT NULL,
  `last_ip` varchar(45) DEFAULT NULL,
  `last_update` timestamp NULL DEFAULT NULL,
  `update_count` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `notify_on_change` tinyint(1) NOT NULL DEFAULT 0,
  `ttl` int(11) NOT NULL DEFAULT 300,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dynamic_dns_clients_token_unique` (`token`),
  KEY `dynamic_dns_clients_dns_zone_id_foreign` (`dns_zone_id`),
  KEY `dynamic_dns_clients_token_is_active_index` (`token`,`is_active`),
  KEY `dynamic_dns_clients_user_id_index` (`user_id`),
  CONSTRAINT `dynamic_dns_clients_dns_zone_id_foreign` FOREIGN KEY (`dns_zone_id`) REFERENCES `dns_zones` (`id`) ON DELETE CASCADE,
  CONSTRAINT `dynamic_dns_clients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: email_templates
CREATE TABLE `email_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `variables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variables`)),
  `category` varchar(255) NOT NULL DEFAULT 'system',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_templates_key_unique` (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: failed_jobs
CREATE TABLE `failed_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: job_batches
CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: jobs
CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB AUTO_INCREMENT=41449 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: languages
CREATE TABLE `languages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(10) NOT NULL,
  `flag` varchar(10) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `languages_code_unique` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: migrations
CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: name_server_stats
CREATE TABLE `name_server_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name_server_id` bigint(20) unsigned NOT NULL,
  `status` varchar(20) NOT NULL COMMENT 'online, offline, degraded',
  `response_time_ms` int(11) DEFAULT NULL COMMENT 'Timp răspuns în milisecunde',
  `is_reachable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Dacă serverul răspunde',
  `zones_count` int(11) NOT NULL DEFAULT 0 COMMENT 'Număr zone DNS la momentul verificării',
  `records_count` int(11) NOT NULL DEFAULT 0 COMMENT 'Număr total înregistrări DNS',
  `cpu_usage` decimal(5,2) DEFAULT NULL COMMENT 'Utilizare CPU %',
  `memory_usage` decimal(5,2) DEFAULT NULL COMMENT 'Utilizare memorie %',
  `disk_usage` decimal(5,2) DEFAULT NULL COMMENT 'Utilizare disc %',
  `bandwidth_in` bigint(20) DEFAULT NULL COMMENT 'Trafic intrare (bytes)',
  `bandwidth_out` bigint(20) DEFAULT NULL COMMENT 'Trafic ieșire (bytes)',
  `api_requests` int(11) NOT NULL DEFAULT 0 COMMENT 'Număr cereri API',
  `api_errors` int(11) NOT NULL DEFAULT 0 COMMENT 'Număr erori API',
  `uptime_seconds` int(11) DEFAULT NULL COMMENT 'Uptime server în secunde',
  `uptime_percentage` decimal(5,2) DEFAULT NULL COMMENT 'Uptime % pentru perioada',
  `error_message` text DEFAULT NULL COMMENT 'Mesaj eroare dacă există',
  `additional_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Date suplimentare JSON' CHECK (json_valid(`additional_data`)),
  `checked_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Când a fost făcută verificarea',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_server_stats_name_server_id_index` (`name_server_id`),
  KEY `name_server_stats_checked_at_index` (`checked_at`),
  KEY `name_server_stats_name_server_id_checked_at_index` (`name_server_id`,`checked_at`),
  KEY `name_server_stats_status_index` (`status`),
  CONSTRAINT `name_server_stats_name_server_id_foreign` FOREIGN KEY (`name_server_id`) REFERENCES `name_servers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17540 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: name_servers
CREATE TABLE `name_servers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'Nume descriptiv server',
  `hostname` varchar(255) NOT NULL COMMENT 'Hostname NS (ex: ns1.example.com)',
  `ip_address` varchar(255) NOT NULL COMMENT 'IP Address principal',
  `ipv6_address` varchar(255) DEFAULT NULL COMMENT 'IPv6 Address (opțional)',
  `api_url` varchar(255) DEFAULT NULL COMMENT 'URL API PowerDNS',
  `api_key` varchar(255) DEFAULT NULL COMMENT 'API Key pentru autentificare',
  `cloudflare_account_id` varchar(255) DEFAULT NULL COMMENT 'Cloudflare Account ID',
  `api_type` varchar(255) NOT NULL DEFAULT 'powerdns' COMMENT 'Tip API: powerdns, bind, etc',
  `api_port` int(11) NOT NULL DEFAULT 8081 COMMENT 'Port API',
  `server_id` varchar(255) NOT NULL DEFAULT 'localhost' COMMENT 'Server ID pentru API',
  `country_code` varchar(2) DEFAULT NULL COMMENT 'Cod țară (RO, US, etc)',
  `city` varchar(255) DEFAULT NULL COMMENT 'Oraș',
  `region` varchar(255) DEFAULT NULL COMMENT 'Regiune/Continent (EU, NA, AS, etc)',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT 'Latitudine',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT 'Longitudine',
  `provider` varchar(255) DEFAULT NULL COMMENT 'Provider hosting (AWS, DigitalOcean, etc)',
  `datacenter` varchar(255) DEFAULT NULL COMMENT 'Datacenter location',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Server activ',
  `is_primary` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Server NS primar',
  `priority` int(11) NOT NULL DEFAULT 10 COMMENT 'Prioritate (mai mic = mai important)',
  `weight` int(11) NOT NULL DEFAULT 100 COMMENT 'Weight pentru load balancing',
  `last_check_at` timestamp NULL DEFAULT NULL COMMENT 'Ultima verificare status',
  `status` varchar(255) NOT NULL DEFAULT 'unknown' COMMENT 'Status: online, offline, degraded, unknown',
  `response_time_ms` int(11) DEFAULT NULL COMMENT 'Timp răspuns în ms',
  `max_zones` int(11) DEFAULT NULL COMMENT 'Număr maxim zone DNS',
  `current_zones` int(11) NOT NULL DEFAULT 0 COMMENT 'Număr curent zone DNS',
  `description` text DEFAULT NULL COMMENT 'Descriere server',
  `notes` text DEFAULT NULL COMMENT 'Note administrative',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `web_port` int(11) DEFAULT 8082 COMMENT 'Port pentru web server (query-logs.php)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_servers_hostname_unique` (`hostname`),
  KEY `name_servers_is_active_index` (`is_active`),
  KEY `name_servers_is_primary_index` (`is_primary`),
  KEY `name_servers_status_index` (`status`),
  KEY `name_servers_country_code_index` (`country_code`),
  KEY `name_servers_region_index` (`region`),
  KEY `name_servers_provider_index` (`provider`),
  KEY `name_servers_is_active_priority_index` (`is_active`,`priority`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: nameservers
CREATE TABLE `nameservers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `hostname` varchar(255) NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `web_port` int(11) DEFAULT 8082 COMMENT 'Port pentru web server (query-logs.php)',
  PRIMARY KEY (`id`),
  KEY `nameservers_user_id_foreign` (`user_id`),
  CONSTRAINT `nameservers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: notifications
CREATE TABLE `notifications` (
  `id` char(36) NOT NULL,
  `type` varchar(255) NOT NULL,
  `notifiable_type` varchar(255) NOT NULL,
  `notifiable_id` bigint(20) unsigned NOT NULL,
  `data` text NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifications_notifiable_type_notifiable_id_index` (`notifiable_type`,`notifiable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: password_reset_tokens
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: personal_access_tokens
CREATE TABLE `personal_access_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint(20) unsigned NOT NULL,
  `name` text NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: plans
CREATE TABLE `plans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `stripe_price_id` varchar(255) DEFAULT NULL,
  `stripe_product_id` varchar(255) DEFAULT NULL,
  `dns_zones_limit` int(11) NOT NULL DEFAULT 3,
  `subdomains_limit` int(11) NOT NULL DEFAULT 100,
  `nameservers_limit` int(11) NOT NULL DEFAULT 2,
  `dns_queries_limit` bigint(20) DEFAULT NULL COMMENT 'Limita de query-uri DNS pe lună (null = nelimitat)',
  `allowed_record_types` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`allowed_record_types`)),
  `allowed_nameservers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Name servers permise (null = toate)' CHECK (json_valid(`allowed_nameservers`)),
  `allowed_templates` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'DNS templates permise (null = toate)' CHECK (json_valid(`allowed_templates`)),
  `allow_zone_export` tinyint(1) NOT NULL DEFAULT 0,
  `allow_zone_import` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `api_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `api_rate_limit_hour` int(11) NOT NULL DEFAULT 60,
  `api_rate_limit_day` int(11) NOT NULL DEFAULT 1000,
  `api_max_tokens` int(11) NOT NULL DEFAULT 1,
  `api_endpoints_access` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`api_endpoints_access`)),
  `api_features` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`api_features`)),
  `dyndns_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `dyndns_max_clients` int(11) NOT NULL DEFAULT 0 COMMENT 'Numărul maxim de clienți DynDNS permis (0 = nelimitat)',
  `is_visible` tinyint(1) NOT NULL DEFAULT 1,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plans_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: plugins
CREATE TABLE `plugins` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `version` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `author_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `main_file` varchar(255) NOT NULL DEFAULT 'Plugin.php',
  `order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plugins_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: powerdns_changelog
CREATE TABLE `powerdns_changelog` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `zone_name` varchar(255) DEFAULT NULL,
  `record_name` varchar(255) DEFAULT NULL,
  `record_type` varchar(255) DEFAULT NULL,
  `old_content` text DEFAULT NULL,
  `new_content` text DEFAULT NULL,
  `old_ttl` int(11) DEFAULT NULL,
  `new_ttl` int(11) DEFAULT NULL,
  `old_disabled` tinyint(1) DEFAULT NULL,
  `new_disabled` tinyint(1) DEFAULT NULL,
  `action` enum('create','update','delete','activate','deactivate','restore','view_zones','view_zone','view_records','view_record','edit_form','create_form','view_changelog','view_activity_log') NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `ip_address` varchar(255) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `powerdns_changelog_zone_name_record_name_index` (`zone_name`,`record_name`),
  KEY `powerdns_changelog_user_id_created_at_index` (`user_id`,`created_at`),
  CONSTRAINT `powerdns_changelog_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: powerdns_record_metadata
CREATE TABLE `powerdns_record_metadata` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `zone_name` varchar(255) NOT NULL,
  `record_name` varchar(255) NOT NULL,
  `record_type` varchar(255) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `powerdns_record_unique` (`zone_name`,`record_name`,`record_type`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: reseller_plans
CREATE TABLE `reseller_plans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `max_clients` int(11) NOT NULL,
  `dns_zones_per_client` int(11) NOT NULL DEFAULT 10,
  `subdomains_per_client` int(11) NOT NULL DEFAULT 50,
  `nameservers_per_client` int(11) NOT NULL DEFAULT 2,
  `dns_queries_limit_per_client` bigint(20) DEFAULT NULL COMMENT 'Limita de query-uri DNS pe lună per client (null = nelimitat)',
  `allowed_record_types` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`allowed_record_types`)),
  `allowed_nameservers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Name servers permise (null = toate)' CHECK (json_valid(`allowed_nameservers`)),
  `allowed_templates` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'DNS templates permise (null = toate)' CHECK (json_valid(`allowed_templates`)),
  `allow_zone_export` tinyint(1) NOT NULL DEFAULT 0,
  `allow_zone_import` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `api_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `api_rate_limit_hour` int(11) NOT NULL DEFAULT 300,
  `api_rate_limit_day` int(11) NOT NULL DEFAULT 10000,
  `api_max_tokens` int(11) NOT NULL DEFAULT 5,
  `api_endpoints_access` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`api_endpoints_access`)),
  `api_features` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`api_features`)),
  `dyndns_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `dyndns_max_clients` int(11) NOT NULL DEFAULT 0 COMMENT 'Numărul maxim de clienți DynDNS permis (0 = nelimitat)',
  `is_visible` tinyint(1) NOT NULL DEFAULT 1,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reseller_plans_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: sessions
CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: settings
CREATE TABLE `settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` text DEFAULT NULL,
  `group` varchar(255) NOT NULL DEFAULT 'general',
  `type` varchar(255) NOT NULL DEFAULT 'text',
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: subscription_items
CREATE TABLE `subscription_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_id` bigint(20) unsigned NOT NULL,
  `stripe_id` varchar(255) NOT NULL,
  `stripe_product` varchar(255) NOT NULL,
  `stripe_price` varchar(255) NOT NULL,
  `meter_id` varchar(255) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `meter_event_name` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscription_items_stripe_id_unique` (`stripe_id`),
  KEY `subscription_items_subscription_id_stripe_price_index` (`subscription_id`,`stripe_price`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: subscriptions
CREATE TABLE `subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `type` varchar(255) NOT NULL,
  `stripe_id` varchar(255) NOT NULL,
  `stripe_status` varchar(255) NOT NULL,
  `stripe_price` varchar(255) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  `ends_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscriptions_stripe_id_unique` (`stripe_id`),
  KEY `subscriptions_user_id_stripe_status_index` (`user_id`,`stripe_status`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: translations
CREATE TABLE `translations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `language_code` varchar(10) NOT NULL,
  `group` varchar(100) NOT NULL,
  `key` varchar(255) NOT NULL,
  `value` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `translations_language_code_group_key_unique` (`language_code`,`group`,`key`),
  KEY `translations_group_key_index` (`group`,`key`),
  CONSTRAINT `translations_language_code_foreign` FOREIGN KEY (`language_code`) REFERENCES `languages` (`code`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=307 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Table: users
CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `address_number` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `county` varchar(255) DEFAULT NULL,
  `postal_code` varchar(255) DEFAULT NULL,
  `country` varchar(255) NOT NULL DEFAULT 'Romania',
  `company_cui` varchar(255) DEFAULT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `company_reg_com` varchar(255) DEFAULT NULL,
  `company_address` varchar(255) DEFAULT NULL,
  `company_number` varchar(255) DEFAULT NULL,
  `company_city` varchar(255) DEFAULT NULL,
  `company_county` varchar(255) DEFAULT NULL,
  `company_postal_code` varchar(255) DEFAULT NULL,
  `company_country` varchar(255) DEFAULT NULL,
  `terms_accepted` tinyint(1) NOT NULL DEFAULT 0,
  `user_type` enum('individual','company') NOT NULL DEFAULT 'individual',
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('user','admin','reseller') NOT NULL DEFAULT 'user',
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `reseller_id` bigint(20) unsigned DEFAULT NULL,
  `reseller_plan_id` bigint(20) unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `remember_token` varchar(100) DEFAULT NULL,
  `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `two_factor_secret` text DEFAULT NULL,
  `two_factor_recovery_codes` text DEFAULT NULL,
  `two_factor_confirmed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `stripe_id` varchar(255) DEFAULT NULL,
  `pm_type` varchar(255) DEFAULT NULL,
  `pm_last_four` varchar(4) DEFAULT NULL,
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_plan_id_foreign` (`plan_id`),
  KEY `users_reseller_id_foreign` (`reseller_id`),
  KEY `users_reseller_plan_id_foreign` (`reseller_plan_id`),
  KEY `users_stripe_id_index` (`stripe_id`),
  CONSTRAINT `users_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `users_reseller_id_foreign` FOREIGN KEY (`reseller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `users_reseller_plan_id_foreign` FOREIGN KEY (`reseller_plan_id`) REFERENCES `reseller_plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

