CREATE TABLE projects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(160) NOT NULL,
  policy_version VARCHAR(80) NOT NULL DEFAULT 'observation-0.1', active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE campaigns (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NOT NULL, name VARCHAR(180) NOT NULL,
  status ENUM('draft','observation','paused','closed') NOT NULL DEFAULT 'draft', starts_at DATETIME NULL, ends_at DATETIME NULL,
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX(project_id), FOREIGN KEY(project_id) REFERENCES projects(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sources (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NULL, connector VARCHAR(40) NOT NULL,
  label VARCHAR(180) NOT NULL, source_url TEXT NULL, config_json JSON NULL, enabled TINYINT(1) NOT NULL DEFAULT 0,
  last_cursor TEXT NULL, last_success_at DATETIME NULL, last_error_at DATETIME NULL, last_error TEXT NULL,
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX(connector), INDEX(project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, source_id BIGINT UNSIGNED NULL, connector VARCHAR(40) NOT NULL,
  external_id VARCHAR(255) NOT NULL DEFAULT '', url TEXT NULL, title TEXT NOT NULL, body MEDIUMTEXT NOT NULL,
  fingerprint CHAR(64) NOT NULL UNIQUE, published_at DATETIME NULL, collected_at DATETIME NOT NULL,
  raw_json JSON NULL, created_at DATETIME NOT NULL, INDEX(connector), INDEX(published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE analyses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, content_item_id BIGINT UNSIGNED NOT NULL, vera_version VARCHAR(80) NOT NULL,
  prompt_hash CHAR(64) NOT NULL, policy_hash CHAR(64) NOT NULL, model VARCHAR(120) NOT NULL,
  result_json JSON NOT NULL, confidence DECIMAL(5,2) NULL, input_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  output_tokens INT UNSIGNED NOT NULL DEFAULT 0, estimated_cost_eur DECIMAL(12,6) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL, INDEX(content_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_scores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, analysis_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL,
  score DECIMAL(5,2) NOT NULL, recommended TINYINT(1) NOT NULL DEFAULT 0, reasons_json JSON NOT NULL,
  created_at DATETIME NOT NULL, UNIQUE KEY analysis_project(analysis_id,project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, normalized_question TEXT NOT NULL, fingerprint CHAR(64) NOT NULL UNIQUE,
  occurrence_count INT UNSIGNED NOT NULL DEFAULT 1, first_seen_at DATETIME NOT NULL, last_seen_at DATETIME NOT NULL,
  project_scores_json JSON NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE response_drafts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, analysis_id BIGINT UNSIGNED NOT NULL, project_id BIGINT UNSIGNED NOT NULL,
  draft_text MEDIUMTEXT NOT NULL, profile_version VARCHAR(80) NOT NULL, status ENUM('prepared','reviewed','rejected') NOT NULL DEFAULT 'prepared',
  created_at DATETIME NOT NULL, reviewed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE briefings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, briefing_date DATE NOT NULL UNIQUE, payload_json JSON NOT NULL,
  rendered_html MEDIUMTEXT NOT NULL, generated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, type VARCHAR(80) NOT NULL, payload_json JSON NOT NULL,
  idempotency_key VARCHAR(191) NOT NULL UNIQUE, status ENUM('pending','running','retry','done','failed') NOT NULL,
  available_at DATETIME NOT NULL, leased_until DATETIME NULL, leased_by VARCHAR(120) NULL, attempts INT UNSIGNED NOT NULL DEFAULT 0,
  last_error TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX(status,available_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, trace_id CHAR(36) NOT NULL, event_type VARCHAR(100) NOT NULL,
  actor VARCHAR(120) NOT NULL, subject_type VARCHAR(80) NULL, subject_id VARCHAR(120) NULL, details_json JSON NULL,
  created_at DATETIME NOT NULL, INDEX(trace_id), INDEX(event_type,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE quota_usage (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, usage_date DATE NOT NULL, provider VARCHAR(80) NOT NULL,
  operation VARCHAR(120) NOT NULL, calls INT UNSIGNED NOT NULL DEFAULT 0, units BIGINT UNSIGNED NOT NULL DEFAULT 0,
  input_tokens BIGINT UNSIGNED NOT NULL DEFAULT 0, output_tokens BIGINT UNSIGNED NOT NULL DEFAULT 0,
  estimated_cost_eur DECIMAL(12,6) NOT NULL DEFAULT 0, UNIQUE KEY daily_provider_operation(usage_date,provider,operation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
