-- ArtFinder owns this database. Never run this file against the Accolay database.
CREATE DATABASE IF NOT EXISTS `artfinder`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `artfinder`;

CREATE TABLE IF NOT EXISTS `artfinder_settings` (
  `setting_key` VARCHAR(80) NOT NULL,
  `value_json` JSON NOT NULL,
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A theme is created and named only by the user. Marketplace queries belong
-- to the selected engine in ArtSearchCore and are never entered per theme.
CREATE TABLE IF NOT EXISTS `artfinder_themes` (
  `id` CHAR(36) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `corpus_kind` VARCHAR(40) NOT NULL DEFAULT 'ceramics',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_artfinder_themes_active` (`active`, `updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `artfinder_theme_runs` (
  `id` CHAR(36) NOT NULL,
  `theme_id` CHAR(36) NOT NULL,
  `mode` VARCHAR(24) NOT NULL,
  `source` VARCHAR(24) NOT NULL,
  `requested_count` INT UNSIGNED NOT NULL,
  `collected_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `analyzed_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` VARCHAR(24) NOT NULL DEFAULT 'running',
  `started_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `finished_at` DATETIME(3) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_artfinder_theme_runs_theme` (`theme_id`, `started_at`),
  KEY `idx_artfinder_theme_runs_status` (`status`),
  CONSTRAINT `fk_artfinder_theme_runs_theme`
    FOREIGN KEY (`theme_id`) REFERENCES `artfinder_themes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `artfinder_theme_candidates` (
  `theme_id` CHAR(36) NOT NULL,
  `candidate_id` VARCHAR(512) NOT NULL,
  `run_id` CHAR(36) NOT NULL,
  `mode` VARCHAR(24) NOT NULL,
  `title` VARCHAR(500) NOT NULL,
  `listing_url` TEXT NOT NULL,
  `image_url` TEXT NOT NULL,
  `source` VARCHAR(40) NOT NULL,
  `price` VARCHAR(80) NULL,
  `base_similarity` DECIMAL(8,7) NOT NULL DEFAULT 0,
  `similarity` DECIMAL(8,7) NOT NULL DEFAULT 0,
  `closest_references` JSON NOT NULL,
  `decision_label` VARCHAR(16) NULL,
  `first_discovered_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `last_discovered_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`theme_id`, `candidate_id`),
  KEY `idx_artfinder_theme_candidates_run` (`run_id`),
  KEY `idx_artfinder_theme_candidates_review` (`theme_id`, `mode`, `decision_label`, `last_discovered_at`),
  KEY `idx_artfinder_theme_candidates_similarity` (`theme_id`, `similarity`),
  CONSTRAINT `fk_artfinder_theme_candidates_theme`
    FOREIGN KEY (`theme_id`) REFERENCES `artfinder_themes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_artfinder_theme_candidates_run`
    FOREIGN KEY (`run_id`) REFERENCES `artfinder_theme_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Both yes and no decisions are scoped by theme. Only positive rows form the visual library.
CREATE TABLE IF NOT EXISTS `artfinder_theme_decisions` (
  `theme_id` CHAR(36) NOT NULL,
  `candidate_id` VARCHAR(512) NOT NULL,
  `title` VARCHAR(500) NOT NULL,
  `image_url` TEXT NOT NULL,
  `listing_url` TEXT NULL,
  `source` VARCHAR(40) NOT NULL,
  `price` VARCHAR(80) NULL,
  `label` VARCHAR(16) NOT NULL,
  `created_at_ms` BIGINT UNSIGNED NOT NULL,
  `updated_at_ms` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`theme_id`, `candidate_id`),
  KEY `idx_artfinder_theme_decisions_library` (`theme_id`, `label`, `updated_at_ms`),
  CONSTRAINT `fk_artfinder_theme_decisions_theme`
    FOREIGN KEY (`theme_id`) REFERENCES `artfinder_themes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `artfinder_theme_seen` (
  `theme_id` CHAR(36) NOT NULL,
  `candidate_id` VARCHAR(512) NOT NULL,
  `title` VARCHAR(500) NOT NULL DEFAULT '',
  `source` VARCHAR(40) NOT NULL DEFAULT '',
  `first_seen_at_ms` BIGINT UNSIGNED NOT NULL,
  `last_seen_at_ms` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`theme_id`, `candidate_id`),
  KEY `idx_artfinder_theme_seen_last` (`theme_id`, `last_seen_at_ms`),
  CONSTRAINT `fk_artfinder_theme_seen_theme`
    FOREIGN KEY (`theme_id`) REFERENCES `artfinder_themes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
