-- =====================================================
-- Cloud Bridge Enterprise Database Schema (cPanel MySQL)
-- Roles: Owner, Admin, Member | Live Heartbeat | Billing & Rates
-- =====================================================

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `telegram_id` BIGINT NOT NULL UNIQUE,
  `username` VARCHAR(64) DEFAULT '',
  `full_name` VARCHAR(128) DEFAULT '',
  `role` ENUM('owner', 'admin', 'member') NOT NULL DEFAULT 'member',
  `custom_price_per_number` INT DEFAULT NULL COMMENT 'Harga khusus per nomor (jika null, pakai default)',
  `status` ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (`telegram_id`),
  INDEX (`role`),
  INDEX (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` VARCHAR(64) PRIMARY KEY,
  `setting_value` TEXT NOT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Nilai awal default jika belum ada
INSERT INTO `system_settings` (`setting_key`, `setting_value`) VALUES 
('DEFAULT_PRICE_PER_NUMBER', '1500')
ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_code` VARCHAR(32) NOT NULL UNIQUE,
  `user_id` INT DEFAULT NULL,
  `telegram_user_id` BIGINT NOT NULL,
  `telegram_username` VARCHAR(64) DEFAULT '',
  `dealer_msisdn` VARCHAR(32) DEFAULT '',
  `dealer_access_key` VARCHAR(128) DEFAULT '',
  `total_numbers` INT NOT NULL DEFAULT 0,
  `processed_numbers` INT NOT NULL DEFAULT 0,
  `success_count` INT NOT NULL DEFAULT 0,
  `failed_count` INT NOT NULL DEFAULT 0,
  `price_per_number` INT NOT NULL DEFAULT 1500,
  `total_price` INT NOT NULL DEFAULT 0,
  `payment_status` ENUM('unpaid', 'paid') NOT NULL DEFAULT 'unpaid',
  `paid_at` DATETIME DEFAULT NULL,
  `status` ENUM('pending', 'pulled', 'processing', 'completed', 'partial', 'cancelled') NOT NULL DEFAULT 'pending',
  `pulled_by` VARCHAR(64) DEFAULT '' COMMENT 'Worker/PC yang mengunci order',
  `pulled_at` DATETIME DEFAULT NULL,
  `notes` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`status`),
  INDEX (`payment_status`),
  INDEX (`order_code`),
  INDEX (`pulled_by`),
  INDEX (`telegram_user_id`),
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `phone` VARCHAR(32) NOT NULL,
  `iccid` VARCHAR(32) DEFAULT '',
  `status` ENUM('pending', 'running', 'success', 'inactive', 'error') NOT NULL DEFAULT 'pending',
  `result_msg` TEXT,
  `worker_id` VARCHAR(64) DEFAULT '' COMMENT 'PC lokal yang mengeksekusi',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`order_id`),
  INDEX (`phone`),
  INDEX (`status`),
  CONSTRAINT `fk_order_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `worker_nodes` (
  `worker_id` VARCHAR(64) PRIMARY KEY,
  `ip_address` VARCHAR(64) DEFAULT '',
  `system_mode` VARCHAR(32) DEFAULT 'isimple',
  `active_niks_count` INT DEFAULT 0,
  `total_available_slots` INT DEFAULT 0,
  `running_tasks_count` INT DEFAULT 0,
  `dealer_account` VARCHAR(64) DEFAULT '',
  `last_heartbeat` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`last_heartbeat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_sessions` (
  `telegram_id` BIGINT PRIMARY KEY,
  `current_step` VARCHAR(64) DEFAULT '',
  `session_data` TEXT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crosscheck_tasks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `batch_id` VARCHAR(64) NOT NULL,
  `telegram_chat_id` BIGINT NOT NULL,
  `telegram_message_id` INT DEFAULT NULL,
  `phone` VARCHAR(32) NOT NULL,
  `iccid` VARCHAR(32) DEFAULT '',
  `status` ENUM('pending', 'pulled', 'running', 'completed', 'error') NOT NULL DEFAULT 'pending',
  `result_status` ENUM('unknown', 'active', 'inactive', 'error') NOT NULL DEFAULT 'unknown',
  `result_msg` TEXT,
  `worker_id` VARCHAR(64) DEFAULT '',
  `pulled_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (`batch_id`),
  INDEX (`status`),
  INDEX (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dealer_records` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `worker_id` VARCHAR(64) DEFAULT '',
  `timestamp` DATETIME NOT NULL,
  `date` DATE NOT NULL,
  `mode` VARCHAR(32) NOT NULL DEFAULT 'isimple',
  `dealer_id` VARCHAR(64) DEFAULT '',
  `dealer_name` VARCHAR(128) DEFAULT '',
  `dealer_code` VARCHAR(64) DEFAULT '',
  `phone` VARCHAR(32) NOT NULL,
  `iccid` VARCHAR(32) DEFAULT '',
  `nik` VARCHAR(32) DEFAULT '',
  `message` VARCHAR(255) DEFAULT 'Registrasi berhasil',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_dealer_phone_date` (`phone`, `date`),
  INDEX (`date`),
  INDEX (`mode`),
  INDEX (`dealer_name`),
  INDEX (`worker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

