CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` BIGINT UNSIGNED NULL,
  `action` VARCHAR(20) NOT NULL, `route_name` VARCHAR(255) NULL, `method` VARCHAR(10) NOT NULL,
  `url` VARCHAR(1000) NOT NULL, `ip_address` VARCHAR(45) NULL, `response_status` SMALLINT UNSIGNED NULL,
  `changes` JSON NULL, `created_at` TIMESTAMP NULL, `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`), KEY `audit_created_user_idx` (`created_at`,`user_id`), KEY `audit_route_idx` (`route_name`),
  CONSTRAINT `audit_logs_user_id_foreign` 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 `playout_devices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL,
  `token_prefix` VARCHAR(16) NOT NULL, `token_hash` VARCHAR(64) NOT NULL, `active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_seen_at` TIMESTAMP NULL, `last_ip` VARCHAR(45) NULL, `app_version` VARCHAR(50) NULL, `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL, `updated_at` TIMESTAMP NULL, PRIMARY KEY (`id`),
  UNIQUE KEY `playout_devices_token_prefix_unique` (`token_prefix`), UNIQUE KEY `playout_devices_token_hash_unique` (`token_hash`),
  KEY `playout_devices_active_idx` (`active`), KEY `playout_devices_last_seen_idx` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `playout_exports` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `playout_device_id` BIGINT UNSIGNED NULL,
  `play_date` DATE NOT NULL, `block_template_id` BIGINT UNSIGNED NULL, `file_name` VARCHAR(255) NULL,
  `content_hash` VARCHAR(64) NULL, `status` VARCHAR(30) NOT NULL DEFAULT 'requested',
  `generated_at` TIMESTAMP NULL, `downloaded_at` TIMESTAMP NULL, `error_message` TEXT NULL,
  `created_at` TIMESTAMP NULL, `updated_at` TIMESTAMP NULL, PRIMARY KEY (`id`),
  KEY `playout_exports_date_idx` (`play_date`), KEY `playout_exports_status_idx` (`status`),
  CONSTRAINT `playout_exports_device_foreign` FOREIGN KEY (`playout_device_id`) REFERENCES `playout_devices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `playout_exports_block_foreign` FOREIGN KEY (`block_template_id`) REFERENCES `broadcast_block_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;