-- MEGEOL Database Setup

START TRANSACTION;

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_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_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_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;
