-- CreateTable
CREATE TABLE `panel_users` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(64) NOT NULL,
    `full_name` VARCHAR(200) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `email` VARCHAR(200) NULL,
    `role` ENUM('ADMIN', 'SUPPORT', 'BILLING', 'VIEWER') NOT NULL DEFAULT 'SUPPORT',
    `totp_secret` VARCHAR(128) NULL,
    `totp_enabled` BOOLEAN NOT NULL DEFAULT false,
    `failed_attempts` INTEGER NOT NULL DEFAULT 0,
    `locked_until` DATETIME(3) NULL,
    `last_login_at` DATETIME(3) NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `deleted_at` DATETIME(3) NULL,

    UNIQUE INDEX `panel_users_username_key`(`username`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `customers` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(32) NOT NULL,
    `company_name` VARCHAR(200) NOT NULL,
    `city` VARCHAR(80) NULL,
    `country` VARCHAR(2) NOT NULL DEFAULT 'IQ',
    `status` ENUM('TRIAL', 'ACTIVE', 'PAST_DUE', 'SUSPENDED', 'CHURNED') NOT NULL DEFAULT 'TRIAL',
    `plan_code` VARCHAR(32) NOT NULL DEFAULT 'BASIC',
    `contract_start` DATETIME(3) NULL,
    `contract_end` DATETIME(3) NULL,
    `notes` TEXT NULL,
    `created_by` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `deleted_at` DATETIME(3) NULL,

    UNIQUE INDEX `customers_code_key`(`code`),
    INDEX `customers_status_idx`(`status`),
    INDEX `customers_company_name_idx`(`company_name`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `customer_contacts` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `customer_id` INTEGER NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `role` VARCHAR(80) NULL,
    `phone` VARCHAR(32) NULL,
    `whatsapp` VARCHAR(32) NULL,
    `email` VARCHAR(200) NULL,
    `is_primary` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `deleted_at` DATETIME(3) NULL,

    INDEX `customer_contacts_customer_id_idx`(`customer_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `installations` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `customer_id` INTEGER NOT NULL,
    `installation_key` VARCHAR(64) NOT NULL,
    `shared_secret_hash` VARCHAR(255) NOT NULL,
    `label` VARCHAR(120) NULL,
    `status` ENUM('PENDING', 'ACTIVE', 'SILENT', 'DECOMMISSIONED') NOT NULL DEFAULT 'PENDING',
    `app_version` VARCHAR(32) NULL,
    `os_version` VARCHAR(120) NULL,
    `companies_count` INTEGER NULL,
    `active_users` INTEGER NULL,
    `disk_free_gb` INTEGER NULL,
    `db_size_mb` INTEGER NULL,
    `errors_24h` INTEGER NULL,
    `services_ok` BOOLEAN NULL,
    `last_seen_at` DATETIME(3) NULL,
    `last_backup_at` DATETIME(3) NULL,
    `last_backup_ok` BOOLEAN NULL,
    `registered_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,
    `deleted_at` DATETIME(3) NULL,

    UNIQUE INDEX `installations_installation_key_key`(`installation_key`),
    INDEX `installations_customer_id_idx`(`customer_id`),
    INDEX `installations_status_last_seen_at_idx`(`status`, `last_seen_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `heartbeats` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `installation_id` INTEGER NOT NULL,
    `received_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `app_version` VARCHAR(32) NULL,
    `uptime_hours` INTEGER NULL,
    `disk_free_gb` INTEGER NULL,
    `errors_24h` INTEGER NULL,
    `backup_ok` BOOLEAN NULL,
    `ip_address` VARCHAR(64) NULL,

    INDEX `heartbeats_installation_id_received_at_idx`(`installation_id`, `received_at`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `panel_audit_log` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER NULL,
    `user_name` VARCHAR(200) NULL,
    `action` VARCHAR(80) NOT NULL,
    `entity_type` VARCHAR(64) NULL,
    `entity_id` VARCHAR(64) NULL,
    `detail` TEXT NULL,
    `ip_address` VARCHAR(64) NULL,
    `occurred_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `panel_audit_log_user_id_occurred_at_idx`(`user_id`, `occurred_at`),
    INDEX `panel_audit_log_action_occurred_at_idx`(`action`, `occurred_at`),
    INDEX `panel_audit_log_entity_type_entity_id_idx`(`entity_type`, `entity_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `customer_contacts` ADD CONSTRAINT `customer_contacts_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `installations` ADD CONSTRAINT `installations_customer_id_fkey` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `heartbeats` ADD CONSTRAINT `heartbeats_installation_id_fkey` FOREIGN KEY (`installation_id`) REFERENCES `installations`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;