-- MEGEOL Database Setup

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;

CREATE TABLE IF NOT EXISTS `mg_admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT 'Administrator',
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `two_factor` tinyint(1) DEFAULT 1,
  `login_alerts` tinyint(1) DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `last_ip` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_admins` (`name`, `email`, `password`) VALUES
('Administrator', 'admin@megeol.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

CREATE TABLE IF NOT EXISTS `mg_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `device_id` varchar(100) DEFAULT NULL,
  `fcm_token` varchar(255) DEFAULT NULL,
  `platform` enum('android','ios','web') DEFAULT 'web',
  `status` enum('active','suspended','blocked','pending') DEFAULT 'pending',
  `email_verified` tinyint(1) DEFAULT 0,
  `balance` decimal(15,2) DEFAULT 0.00,
  `total_earned` decimal(15,2) DEFAULT 0.00,
  `tasks_completed` int(11) DEFAULT 0,
  `ip_address` varchar(45) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `login_count` int(11) DEFAULT 0,
  `admin_note` text DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_members` (`username`, `name`, `email`, `status`, `balance`, `tasks_completed`, `created_at`) VALUES
('john_doe', 'John Doe', 'john@example.com', 'active', 250000.00, 47, '2024-01-15 10:00:00'),
('sarah_m', 'Sarah Miller', 'sarah@example.com', 'suspended', 120000.00, 23, '2024-03-22 14:30:00'),
('mike_r', 'Mike Ross', 'mike@example.com', 'active', 450000.00, 89, '2024-06-10 09:15:00');

CREATE TABLE IF NOT EXISTS `mg_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `category` enum('social','survey','review','video','download','other') DEFAULT 'social',
  `description` text DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `required_url` varchar(500) DEFAULT NULL,
  `reward` decimal(15,2) NOT NULL DEFAULT 0.00,
  `duration` int(11) DEFAULT 5,
  `max_participants` int(11) DEFAULT 100,
  `current_participants` int(11) DEFAULT 0,
  `status` enum('draft','published','unpublished','completed','expired') DEFAULT 'draft',
  `screen_recording` tinyint(1) DEFAULT 0,
  `auto_approval` tinyint(1) DEFAULT 0,
  `expiration_date` date DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_tasks` (`title`, `category`, `required_url`, `reward`, `max_participants`, `current_participants`, `status`) VALUES
('Follow Instagram', 'social', 'https://instagram.com/megeol', 5000.00, 100, 87, 'published'),
('Complete Survey', 'survey', 'https://survey.example.com', 7500.00, 50, 0, 'draft');

CREATE TABLE IF NOT EXISTS `mg_task_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `screenshot_url` varchar(500) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `scraping_result` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','failed') DEFAULT 'pending',
  `admin_note` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `submitted_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`task_id`) REFERENCES `mg_tasks`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`member_id`) REFERENCES `mg_members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_task_submissions` (`task_id`, `member_id`, `screenshot_url`, `scraping_result`, `status`) VALUES
(1, 1, 'ss1.jpg', 'Like detected', 'pending'),
(2, 2, 'ss2.jpg', NULL, 'failed');

CREATE TABLE IF NOT EXISTS `mg_rewards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reward_code` varchar(20) NOT NULL,
  `type` enum('dana','gopay','ovo','pulsa','voucher') DEFAULT 'dana',
  `amount` decimal(15,2) NOT NULL,
  `status` enum('unused','used','expired') DEFAULT 'unused',
  `assigned_to` int(11) DEFAULT NULL,
  `claimed_at` datetime DEFAULT NULL,
  `expires_at` date DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reward_code` (`reward_code`),
  FOREIGN KEY (`assigned_to`) REFERENCES `mg_members`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_rewards` (`reward_code`, `type`, `amount`, `status`, `assigned_to`) VALUES
('RWD-X8K2-M4NP', 'dana', 10000.00, 'unused', NULL),
('RWD-P9L3-Q7RT', 'dana', 25000.00, 'used', 1),
('RWD-A5B7-C3D9', 'gopay', 15000.00, 'unused', NULL),
('RWD-E2F8-G1H4', 'dana', 50000.00, 'expired', NULL);

CREATE TABLE IF NOT EXISTS `mg_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `method` enum('dana','gopay','ovo','pulsa','bank_transfer') DEFAULT 'dana',
  `destination` varchar(100) DEFAULT NULL,
  `status` enum('pending','processing','success','failed') DEFAULT 'pending',
  `payment_link` varchar(500) DEFAULT NULL,
  `reference_id` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `processed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`member_id`) REFERENCES `mg_members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mg_tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_number` varchar(20) NOT NULL,
  `member_id` int(11) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `status` enum('new','open','pending','closed','resolved') DEFAULT 'new',
  `assigned_to` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `closed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ticket_number` (`ticket_number`),
  FOREIGN KEY (`member_id`) REFERENCES `mg_members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_tickets` (`ticket_number`, `member_id`, `subject`, `message`, `priority`, `status`) VALUES
('TKT-887', 2, 'Reward not received', 'Task completed but reward not sent', 'high', 'open'),
('TKT-886', 3, 'Task submission error', 'Error when submitting survey', 'medium', 'pending');

CREATE TABLE IF NOT EXISTS `mg_ticket_replies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `message` text NOT NULL,
  `attachment` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`ticket_id`) REFERENCES `mg_tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mg_email_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `smtp_host` varchar(100) DEFAULT 'smtp.example.com',
  `smtp_port` int(11) DEFAULT 587,
  `smtp_username` varchar(100) DEFAULT NULL,
  `smtp_password` varchar(255) DEFAULT NULL,
  `smtp_encryption` enum('tls','ssl','none') DEFAULT 'tls',
  `sender_name` varchar(100) DEFAULT 'App Notifications',
  `sender_email` varchar(100) DEFAULT 'noreply@example.com',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_email_settings` (`smtp_host`, `smtp_port`, `smtp_username`, `sender_name`, `sender_email`) VALUES
('smtp.example.com', 587, 'noreply@example.com', 'MEGEOL Notifications', 'noreply@megeol.com');

CREATE TABLE IF NOT EXISTS `mg_email_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `body` text NOT NULL,
  `variables` text DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_email_templates` (`name`, `slug`, `subject`, `body`, `variables`) VALUES
('Registration Verification', 'registration', 'Verify your MEGEOL account', '<h2>Welcome {{name}}!</h2><p>Click <a href="{{verification_link}}">here</a> to verify.</p>', '{{name}},{{verification_link}}'),
('Login Magic Link', 'login_link', 'Your MEGEOL login link', '<h2>Hello {{name}}!</h2><p>Click <a href="{{login_link}}">here</a> to login.</p>', '{{name}},{{login_link}}'),
('Task Approved', 'task_approved', 'Your task has been approved!', '<h2>Congrats {{name}}!</h2><p>Task {{task_name}} approved.</p>', '{{name}},{{task_name}}'),
('Reward Ready', 'reward_ready', 'Your reward is ready!', '<h2>Hi {{name}}!</h2><p>Reward {{reward}} is ready.</p>', '{{name}},{{reward}}'),
('System Announcement', 'announcement', 'MEGEOL Announcement', '<h2>Announcement</h2><p>Dear {{name}}, system update.</p>', '{{name}}');

CREATE TABLE IF NOT EXISTS `mg_api_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `key_value` varchar(255) NOT NULL,
  `type` enum('production','development','mobile') DEFAULT 'production',
  `rate_limit` int(11) DEFAULT 1000,
  `permissions` varchar(255) DEFAULT 'read,write',
  `status` enum('active','revoked','expired') DEFAULT 'active',
  `last_used` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_value` (`key_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_api_keys` (`name`, `key_value`, `type`, `rate_limit`, `permissions`) VALUES
('Production Key', 'sk_live_8a7b3c2d1e4f5a6b7c8d9e0f1a2b3c4d5e6f7a8b', 'production', 5000, 'read,write,delete'),
('Development Key', 'sk_test_9f8e7d6c5b4a3928174655a4b3c2d1e0f9e8d7c6', 'development', 1000, 'read,write'),
('Flutter Mobile App', 'sk_mob_a1b2c3d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0', 'mobile', 3000, 'read,write');

CREATE TABLE IF NOT EXISTS `mg_webhooks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `url` varchar(500) NOT NULL,
  `secret` varchar(100) DEFAULT NULL,
  `events` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `last_triggered` datetime DEFAULT NULL,
  `success_rate` decimal(5,2) DEFAULT 100.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_webhooks` (`name`, `url`, `secret`, `events`) VALUES
('Task Completion', 'https://server.com/webhook/task', 'whsec_8a7b3c', 'task.submitted,task.approved'),
('User Registration', 'https://server.com/webhook/user', 'whsec_9f8e7d', 'user.registered,user.verified'),
('Payment/Reward', 'https://server.com/webhook/payment', 'whsec_a1b2c3', 'reward.claimed,payment.success');

CREATE TABLE IF NOT EXISTS `mg_api_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `api_key_id` int(11) DEFAULT NULL,
  `method` varchar(10) NOT NULL,
  `endpoint` varchar(255) NOT NULL,
  `status_code` int(11) NOT NULL,
  `response_time` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mg_security_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `severity` enum('info','warning','danger','critical') DEFAULT 'info',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_security_events` (`event_type`, `description`, `ip_address`, `severity`) VALUES
('failed_login', 'Failed login (5 attempts)', '103.45.67.89', 'danger'),
('captcha_failure', 'CAPTCHA failure', '192.168.2.14', 'warning');

CREATE TABLE IF NOT EXISTS `mg_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_group` varchar(50) DEFAULT 'general',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_settings` (`setting_key`, `setting_value`, `setting_group`) VALUES
('site_name', 'MEGEOL', 'general'),
('maintenance_mode', '0', 'general'),
('content_protection', '1', 'protection'),
('login_attempt_limit', '5', 'authentication'),
('default_timer', '60', 'tasks');

CREATE TABLE IF NOT EXISTS `mg_database_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `db_host` varchar(100) DEFAULT 'localhost',
  `db_port` int(11) DEFAULT 3306,
  `db_name` varchar(100) DEFAULT 'dohodevc_M3ge0L290726',
  `db_username` varchar(100) DEFAULT 'dohodevc_M3ge0L290726',
  `db_password` varchar(255) DEFAULT NULL,
  `db_charset` varchar(20) DEFAULT 'utf8mb4',
  `table_prefix` varchar(20) DEFAULT 'mg_',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_database_config` (`db_host`, `db_port`, `db_name`, `db_username`, `db_charset`, `table_prefix`) VALUES
('localhost', 3306, 'dohodevc_M3ge0L290726', 'dohodevc_M3ge0L290726', 'utf8mb4', 'mg_');

CREATE TABLE IF NOT EXISTS `mg_app_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_key` varchar(100) NOT NULL,
  `config_value` text DEFAULT NULL,
  `platform` enum('android','ios','all') DEFAULT 'all',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `mg_app_config` (`config_key`, `config_value`) VALUES
('app_version', '2.4.1'),
('min_version', '2.0.0'),
('force_update', 'false'),
('dark_mode', 'true');

CREATE TABLE IF NOT EXISTS `mg_login_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `token` varchar(255) NOT NULL,
  `used` tinyint(1) DEFAULT 0,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  FOREIGN KEY (`member_id`) REFERENCES `mg_members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mg_device_registrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `fcm_token` varchar(255) NOT NULL,
  `platform` enum('android','ios') DEFAULT 'android',
  `device_id` varchar(100) DEFAULT NULL,
  `app_version` varchar(20) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fcm_token` (`fcm_token`),
  FOREIGN KEY (`member_id`) REFERENCES `mg_members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mg_rate_limits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identifier` varchar(100) NOT NULL,
  `action` varchar(50) NOT NULL,
  `attempts` int(11) DEFAULT 1,
  `blocked_until` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;