-- Version20260605120000
-- Create mobile_app and mobile_app_version tables for centralized mobile version control

-- =========================
-- UP
-- =========================

CREATE TABLE `mobile_app` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT DEFAULT NULL,
    `app_type` VARCHAR(20) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `app_key` VARCHAR(100) NOT NULL,
    `platform` VARCHAR(10) NOT NULL,
    `bundle_id` VARCHAR(255) DEFAULT NULL,
    `package_name` VARCHAR(255) DEFAULT NULL,
    `store_url` VARCHAR(1000) DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1 NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `IDX_mobile_app_maincompany` (`maincompany_id`),
    UNIQUE INDEX `uniq_mobile_app_scope` (`app_key`, `platform`, `maincompany_id`),
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE `mobile_app_version` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `mobile_app_id` INT NOT NULL,
    `version_name` VARCHAR(50) NOT NULL,
    `build_number` INT NOT NULL,
    `release_type` VARCHAR(20) DEFAULT 'UPDATE' NOT NULL,
    `is_current` TINYINT(1) DEFAULT 0 NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1 NOT NULL,
    `release_notes` LONGTEXT DEFAULT NULL,
    `released_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `idx_mobile_app_version_current` (`mobile_app_id`, `is_current`),
    INDEX `idx_mobile_app_version_build` (`mobile_app_id`, `build_number`),
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE `mobile_app`
    ADD CONSTRAINT `FK_mobile_app_maincompany`
    FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`) ON DELETE CASCADE;

ALTER TABLE `mobile_app_version`
    ADD CONSTRAINT `FK_mobile_app_version_mobile_app`
    FOREIGN KEY (`mobile_app_id`) REFERENCES `mobile_app` (`id`) ON DELETE CASCADE;

-- =========================
-- DOWN
-- =========================

ALTER TABLE `mobile_app_version` DROP FOREIGN KEY `FK_mobile_app_version_mobile_app`;
ALTER TABLE `mobile_app` DROP FOREIGN KEY `FK_mobile_app_maincompany`;

DROP TABLE `mobile_app_version`;
DROP TABLE `mobile_app`;