-- ==========================================================
-- Enterprise App Update Center & Distribution System Schema
-- Compatible with MySQL 5.7+ / MariaDB 10.3+
-- ==========================================================

CREATE DATABASE IF NOT EXISTS `tokosson_apps_center_manager` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `tokosson_apps_center_manager`;

-- 1. Tabel Users (Admin & IT Staff)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) NOT NULL,
  `role` ENUM('superadmin', 'developer', 'viewer') DEFAULT 'developer',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Tabel Apps (Aplikasi Kantor: Warehouse, POS, Kurir, dll)
CREATE TABLE IF NOT EXISTS `apps` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `app_id` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Misal: com.sjmart.warehouse',
  `name` VARCHAR(100) NOT NULL COMMENT 'Nama Tampilan Aplikasi',
  `description` TEXT NULL,
  `icon_url` VARCHAR(255) NULL,
  `api_secret` VARCHAR(64) NULL COMMENT 'Opsional static secret key',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_app_id` (`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tabel App Releases (Riwayat File & Versi APK)
CREATE TABLE IF NOT EXISTS `app_releases` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `app_id` VARCHAR(100) NOT NULL,
  `version_code` INT NOT NULL COMMENT 'Nomor versi Android misal: 2',
  `version_name` VARCHAR(50) NOT NULL COMMENT 'Nama versi rilis misal: 1.0.1',
  `min_version_code` INT DEFAULT 1,
  `force_update` TINYINT(1) DEFAULT 0 COMMENT '1 jika wajib update, 0 jika opsional',
  `apk_file_name` VARCHAR(255) NOT NULL,
  `apk_file_size` BIGINT UNSIGNED DEFAULT 0 COMMENT 'Ukuran file dalam bytes',
  `apk_url` VARCHAR(500) NOT NULL COMMENT 'URL publik unduhan file APK',
  `changelog` TEXT NULL COMMENT 'Catatan rilis (bisa format JSON array atau teks berbaris)',
  `is_active` TINYINT(1) DEFAULT 1 COMMENT '1 = versi aktif yang disajikan ke client, 0 = nonaktif',
  `download_count` INT UNSIGNED DEFAULT 0,
  `created_by` INT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`app_id`) REFERENCES `apps` (`app_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  INDEX `idx_app_version` (`app_id`, `version_code`),
  INDEX `idx_app_active` (`app_id`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================================
-- SEED DATA AWAL (Initial Admin & Default Warehouse App)
-- ==========================================================

-- Password default: 'Admin123!' (BCRYPT hash)
INSERT INTO `users` (`username`, `password_hash`, `full_name`, `role`, `is_active`)
VALUES (
  'admin',
  '$2y$10$wT0H77vH26bQZf57XqZzB.t/Gf1O4Z3Y3QY.lS2u1W4y5n9p7B4K2',
  'IT Administrator',
  'superadmin',
  1
) ON DUPLICATE KEY UPDATE `id`=`id`;

-- Pendaftaran Aplikasi Pertama: Warehouse V2
INSERT INTO `apps` (`app_id`, `name`, `description`, `is_active`)
VALUES (
  'com.sjmart.warehouse',
  'Warehouse V2',
  'Aplikasi Operasional Tim Gudang (Picking, Checking, Loading, Route Group)',
  1
) ON DUPLICATE KEY UPDATE `id`=`id`;