-- Migration: 0008_ai_cost_tracking
-- Purpose: Add AI cost tracking — per-request cost log + daily aggregate columns
-- Models: AIUsageLog (new), AssistantUsageDaily (extend)
-- Reversible: Yes (DROP TABLE ai_usage_logs + DROP COLUMNs on assistant_usage_daily)

-- ── New table: per-request AI cost log ──────────────────────────────────────

CREATE TABLE `ai_usage_logs` (
  `id` VARCHAR(191) NOT NULL,
  `day_key` VARCHAR(10) NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `user_id` INTEGER NOT NULL,
  `org_id` INTEGER NULL,
  `model` VARCHAR(80) NOT NULL,
  `transport` VARCHAR(16) NOT NULL,
  `input_tokens` INTEGER NOT NULL DEFAULT 0,
  `output_tokens` INTEGER NOT NULL DEFAULT 0,
  `estimated_cost_usd` DOUBLE NOT NULL DEFAULT 0,
  `duration_ms` INTEGER NOT NULL DEFAULT 0,
  `cost_basis` VARCHAR(30) NOT NULL,
  `outcome` VARCHAR(40) NOT NULL,
  `request_id` VARCHAR(120) NULL,

  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE INDEX `ai_usage_logs_day_key_idx` ON `ai_usage_logs`(`day_key`);
CREATE INDEX `ai_usage_logs_user_id_day_key_idx` ON `ai_usage_logs`(`user_id`, `day_key`);
CREATE INDEX `ai_usage_logs_org_id_day_key_idx` ON `ai_usage_logs`(`org_id`, `day_key`);

ALTER TABLE `ai_usage_logs` ADD CONSTRAINT `ai_usage_logs_user_id_fkey`
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- ── Extend AssistantUsageDaily with token + cost aggregates ─────────────────

ALTER TABLE `assistant_usage_daily` ADD COLUMN `total_input_tokens` INTEGER NOT NULL DEFAULT 0;
ALTER TABLE `assistant_usage_daily` ADD COLUMN `total_output_tokens` INTEGER NOT NULL DEFAULT 0;
ALTER TABLE `assistant_usage_daily` ADD COLUMN `total_cost_usd` DOUBLE NOT NULL DEFAULT 0;