-- BOTz Cloner ☕︎ MySQL schema
-- Import this file into the cPanel MySQL database when Artisan migrations are unavailable.
-- Preferred deployment: php artisan migrate --seed. The Laravel seeder hashes the admin password safely.
-- No API keys, OAuth secrets, Telegram tokens, or passwords are stored in this SQL file.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `google_id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `avatar` text NULL,
  `status` varchar(24) NOT NULL DEFAULT 'active',
  `google_metadata` longtext NULL,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `users_google_id_unique` (`google_id`), UNIQUE KEY `users_email_unique` (`email`), KEY `users_status_index` (`status`), KEY `users_last_login_at_index` (`last_login_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admins` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT 'Administrator',
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `must_change_password` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `remember_token` varchar(100) NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `admins_email_unique` (`email`), KEY `admins_last_login_at_index` (`last_login_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bot_templates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `identifier` varchar(255) NOT NULL,
  `description` text NULL,
  `icon` varchar(64) NOT NULL DEFAULT 'bot',
  `module_identifier` varchar(255) NOT NULL,
  `version` varchar(32) NOT NULL DEFAULT '1.0.0',
  `default_settings` json NULL,
  `configuration_schema` json NULL,
  `status` varchar(24) NOT NULL DEFAULT 'draft',
  `published_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `bot_templates_slug_unique` (`slug`), UNIQUE KEY `bot_templates_identifier_unique` (`identifier`), KEY `bot_templates_module_status_index` (`module_identifier`,`status`), KEY `bot_templates_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bot_instances` (
  `id` char(36) NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `template_id` bigint unsigned NOT NULL,
  `public_identifier` char(36) NOT NULL,
  `internal_name` varchar(255) NOT NULL,
  `telegram_bot_id` bigint unsigned NOT NULL,
  `telegram_username` varchar(255) NULL,
  `telegram_first_name` varchar(255) NULL,
  `encrypted_bot_token` longtext NOT NULL,
  `encrypted_webhook_secret` longtext NULL,
  `status` varchar(24) NOT NULL DEFAULT 'pending',
  `webhook_status` varchar(24) NOT NULL DEFAULT 'pending',
  `last_update_at` timestamp NULL DEFAULT NULL,
  `last_error_at` timestamp NULL DEFAULT NULL,
  `last_error_message` text NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `bot_instances_public_identifier_unique` (`public_identifier`), UNIQUE KEY `bot_instances_telegram_bot_id_unique` (`telegram_bot_id`), KEY `bot_instances_user_status_index` (`user_id`,`status`), KEY `bot_instances_template_status_index` (`template_id`,`status`), CONSTRAINT `bot_instances_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `bot_instances_template_id_foreign` FOREIGN KEY (`template_id`) REFERENCES `bot_templates` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bot_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `bot_instance_id` char(36) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` longtext NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `bot_settings_instance_key_unique` (`bot_instance_id`,`setting_key`), CONSTRAINT `bot_settings_bot_instance_id_foreign` FOREIGN KEY (`bot_instance_id`) REFERENCES `bot_instances` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `telegram_updates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `bot_instance_id` char(36) NOT NULL,
  `update_id` bigint unsigned NOT NULL,
  `update_type` varchar(64) NULL,
  `payload` longtext NOT NULL,
  `processed_at` timestamp NULL DEFAULT NULL,
  `status` varchar(24) NOT NULL DEFAULT 'received',
  `error_message` text NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `telegram_updates_instance_update_unique` (`bot_instance_id`,`update_id`), KEY `telegram_updates_status_index` (`status`), CONSTRAINT `telegram_updates_bot_instance_id_foreign` FOREIGN KEY (`bot_instance_id`) REFERENCES `bot_instances` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NULL,
  `admin_id` bigint unsigned NULL,
  `bot_instance_id` char(36) NULL,
  `action` varchar(80) NOT NULL,
  `description` varchar(255) NOT NULL,
  `metadata` json NULL,
  `ip_address` varchar(45) NULL,
  `user_agent` text NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `activity_logs_action_index` (`action`), KEY `activity_logs_user_created_index` (`user_id`,`created_at`), KEY `activity_logs_admin_created_index` (`admin_id`,`created_at`), CONSTRAINT `activity_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `activity_logs_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL, CONSTRAINT `activity_logs_bot_instance_id_foreign` FOREIGN KEY (`bot_instance_id`) REFERENCES `bot_instances` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(255) NOT NULL,
  `setting_value` longtext NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `system_settings_setting_key_unique` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint unsigned NULL,
  `ip_address` varchar(45) NULL,
  `user_agent` text NULL,
  `payload` longtext NOT NULL,
  `last_activity` int 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;

CREATE TABLE IF NOT EXISTS `cache` (`key` varchar(255) NOT NULL, `value` mediumtext NOT NULL, `expiration` int NOT NULL, PRIMARY KEY (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `cache_locks` (`key` varchar(255) NOT NULL, `owner` varchar(255) NOT NULL, `expiration` int NOT NULL, PRIMARY KEY (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `jobs` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `queue` varchar(255) NOT NULL, `payload` longtext NOT NULL, `attempts` tinyint unsigned NOT NULL, `reserved_at` int unsigned NULL, `available_at` int unsigned NOT NULL, `created_at` int unsigned NOT NULL, PRIMARY KEY (`id`), KEY `jobs_queue_index` (`queue`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `job_batches` (`id` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `total_jobs` int NOT NULL, `pending_jobs` int NOT NULL, `failed_jobs` int NOT NULL, `failed_job_ids` longtext NOT NULL, `options` mediumtext NULL, `cancelled_at` int NULL, `created_at` int NOT NULL, `finished_at` int NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `failed_jobs` (`id` bigint 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `system_settings` (`setting_key`,`setting_value`,`created_at`,`updated_at`) VALUES
('website_name','BOTz Cloner',NOW(),NOW()),
('website_description','Create and manage your Telegram bots without complicated setup.',NOW(),NOW()),
('maintenance_mode','false',NOW(),NOW()),
('max_bots_per_user','10',NOW(),NOW()),
('disable_bots_on_suspend','true',NOW(),NOW()),
('google_auth_enabled','true',NOW(),NOW())
ON DUPLICATE KEY UPDATE `setting_value`=VALUES(`setting_value`),`updated_at`=NOW();

INSERT INTO `bot_templates` (`name`,`slug`,`identifier`,`description`,`icon`,`module_identifier`,`version`,`default_settings`,`configuration_schema`,`status`,`published_at`,`created_at`,`updated_at`) VALUES
('Telegram Auto Reaction Bot','telegram-auto-reaction-bot','auto_reaction','Automatically reacts to new Telegram messages with a configurable reaction.','sparkles','auto_reaction','1.0.0','{"enabled":true,"reaction":"👍","internal_name":"My reaction bot"}','{"reaction":{"type":"text","label":"Reaction","description":"Use one emoji, for example 👍 or 🔥.","default":"👍","max":8},"enabled":{"type":"boolean","label":"React to new messages","default":true}}','published',NOW(),NOW(),NOW()),
('Telegram Group Link Remover','telegram-group-link-remover','link_remover','Removes links from Telegram group messages automatically.','link','link_remover','1.0.0','{"enabled":true,"delete_links":true}','{"enabled":{"type":"boolean","label":"Enable link moderation","description":"Process new group messages.","default":true},"delete_links":{"type":"boolean","label":"Delete detected links","description":"The bot must be an administrator with delete permission.","default":true}}','published',NOW(),NOW(),NOW()),
('AI Chat Bot','ai-chat-bot','ai_chat','Replies to Telegram messages using the configured AI gem.','message','ai_chat','1.0.0','{"enabled":true,"reply_to_commands":false}','{"enabled":{"type":"boolean","label":"Enable AI replies","description":"Send AI responses to incoming messages.","default":true},"reply_to_commands":{"type":"boolean","label":"Reply to slash commands","description":"Also process messages such as /help.","default":false}}','published',NOW(),NOW(),NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`description`=VALUES(`description`),`configuration_schema`=VALUES(`configuration_schema`),`status`='published',`updated_at`=NOW();

SET FOREIGN_KEY_CHECKS=1;

-- After importing, create the hashed bootstrap admin and migration record with:
-- php artisan db:seed
-- The seeder creates admin@gmail.com with a Laravel-hashed bootstrap password and must_change_password=1.