watch-finished-turbo/data/migrations/2026-01-06T00-12-00_initial_schema.sql

-- Migration: initial_schema
-- Created at: 2026-01-06T00:12:00.129Z

-- Initial database schema for Watch Finished Turbo

-- Files table for tracking processed video files
CREATE TABLE IF NOT EXISTS files (
  dataset TEXT,
  input TEXT,
  output TEXT,
  date TEXT,
  status TEXT DEFAULT 'pending',
  PRIMARY KEY (dataset, input)
);

-- Tasks table for video processing queue
CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  type TEXT NOT NULL,
  status TEXT DEFAULT 'pending',
  progress INTEGER DEFAULT 0,
  dataset TEXT,
  input TEXT,
  output TEXT,
  preset TEXT,
  priority INTEGER DEFAULT 0,
  retry_count INTEGER DEFAULT 0,
  max_retries INTEGER,
  error_message TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Duplicate files table for duplicate detection
CREATE TABLE IF NOT EXISTS duplicate_files (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  dataset TEXT,
  destination TEXT,
  hash TEXT,
  size INTEGER,
  files TEXT,
  status TEXT DEFAULT 'pending',
  note TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  reviewed_at TEXT
);

-- Settings table for application configuration
CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT
);

-- Indexes for performance
CREATE UNIQUE INDEX IF NOT EXISTS idx_duplicate_files_key
  ON duplicate_files(dataset, destination, hash, size);