CREATE TABLE IF NOT EXISTS cwa_enforcement( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, timestamp TEXT NOT NULL, book_id INTEGER NOT NULL, book_title TEXT NOT NULL, author TEXT NOT NULL, file_path TEXT NOT NULL, trigger_type TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS cwa_import( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, timestamp TEXT NOT NULL, filename TEXT NOT NULL, original_backed_up TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS cwa_conversions( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, timestamp TEXT NOT NULL, filename TEXT NOT NULL, original_format TEXT NOT NULL, original_backed_up TEXT NOT NULL, end_format TEXT DEFAULT "" NOT NULL ); CREATE TABLE IF NOT EXISTS epub_fixes( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, timestamp TEXT NOT NULL, filename TEXT NOT NULL, manually_triggered TEXT NOT NULL, num_of_fixes_applied TEXT NOT NULL, original_backed_up TEXT NOT NULL, file_path TEXT NOT NULL, fixes_applied TEXT DEFAULT "" ); CREATE TABLE IF NOT EXISTS cwa_settings( default_settings SMALLINT DEFAULT 1 NOT NULL, auto_backup_imports SMALLINT DEFAULT 1 NOT NULL, auto_backup_conversions SMALLINT DEFAULT 1 NOT NULL, auto_zip_backups SMALLINT DEFAULT 1 NOT NULL, cwa_update_notifications SMALLINT DEFAULT 1 NOT NULL, contribute_translations_notifications SMALLINT DEFAULT 1 NOT NULL, auto_convert SMALLINT DEFAULT 1 NOT NULL, auto_convert_target_format TEXT DEFAULT "epub" NOT NULL, auto_convert_ignored_formats TEXT DEFAULT "" NOT NULL, auto_ingest_ignored_formats TEXT DEFAULT "" NOT NULL, auto_convert_retained_formats TEXT DEFAULT "" NOT NULL, auto_ingest_automerge TEXT DEFAULT "new_record" NOT NULL, ingest_timeout_minutes INTEGER DEFAULT 15 NOT NULL, ingest_stale_temp_minutes INTEGER DEFAULT 120 NOT NULL, ingest_stale_temp_interval INTEGER DEFAULT 600 NOT NULL, auto_metadata_enforcement SMALLINT DEFAULT 1 NOT NULL, kindle_epub_fixer SMALLINT DEFAULT 1 NOT NULL, kindle_epub_fixer_aggressive SMALLINT DEFAULT 0 NOT NULL, auto_backup_epub_fixes SMALLINT DEFAULT 1 NOT NULL, archived_cleanup_enabled SMALLINT DEFAULT 1 NOT NULL, archived_cleanup_schedule TEXT DEFAULT 'daily' NOT NULL, archived_cleanup_schedule_day TEXT DEFAULT 'sunday' NOT NULL, archived_cleanup_schedule_hour INTEGER DEFAULT 3 NOT NULL, enable_mobile_blur SMALLINT DEFAULT 1 NOT NULL, auto_metadata_fetch_enabled SMALLINT DEFAULT 0 NOT NULL, auto_metadata_smart_application SMALLINT DEFAULT 0 NOT NULL, auto_metadata_update_title SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_authors SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_description SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_publisher SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_tags SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_series SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_rating SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_published_date SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_identifiers SMALLINT DEFAULT 1 NOT NULL, auto_metadata_update_cover SMALLINT DEFAULT 1 NOT NULL, metadata_provider_hierarchy TEXT DEFAULT '["ibdb","google","dnb"]' NOT NULL, metadata_providers_enabled TEXT DEFAULT '{}' NOT NULL, auto_send_delay_minutes INTEGER DEFAULT 5 NOT NULL, duplicate_detection_title SMALLINT DEFAULT 1 NOT NULL, duplicate_detection_author SMALLINT DEFAULT 1 NOT NULL, duplicate_detection_language SMALLINT DEFAULT 1 NOT NULL, duplicate_detection_series SMALLINT DEFAULT 0 NOT NULL, duplicate_detection_publisher SMALLINT DEFAULT 0 NOT NULL, duplicate_detection_format SMALLINT DEFAULT 0 NOT NULL, hardcover_auto_fetch_enabled SMALLINT DEFAULT 0 NOT NULL, hardcover_auto_fetch_schedule TEXT DEFAULT 'weekly' NOT NULL, hardcover_auto_fetch_schedule_day TEXT DEFAULT 'sunday' NOT NULL, hardcover_auto_fetch_schedule_hour INTEGER DEFAULT 2 NOT NULL, hardcover_auto_fetch_min_confidence REAL DEFAULT 0.85 NOT NULL, hardcover_auto_fetch_batch_size INTEGER DEFAULT 50 NOT NULL, hardcover_auto_fetch_rate_limit REAL DEFAULT 5.0 NOT NULL, -- Duplicate notification and auto-resolution settings duplicate_detection_enabled SMALLINT DEFAULT 1 NOT NULL, duplicate_notifications_enabled SMALLINT DEFAULT 1 NOT NULL, duplicate_auto_resolve_enabled SMALLINT DEFAULT 0 NOT NULL, duplicate_auto_resolve_strategy TEXT DEFAULT 'newest' NOT NULL, duplicate_auto_resolve_cooldown_minutes INTEGER DEFAULT 0 NOT NULL, -- 0 = disabled, >0 = minutes between auto-resolutions duplicate_format_priority TEXT DEFAULT '{"EPUB":100,"KEPUB":95,"AZW3":90,"MOBI":80,"AZW":75,"PDF":60,"TXT":40,"CBZ":35,"CBR":35,"FB2":30,"DJVU":25,"HTML":20,"RTF":15,"DOC":10,"DOCX":10}' NOT NULL, -- Duplicate scanning performance settings duplicate_detection_use_sql SMALLINT DEFAULT 1 NOT NULL, -- Enable SQL prefilter for hybrid by default duplicate_scan_method TEXT DEFAULT 'hybrid' NOT NULL, -- Use hybrid prefilter by default duplicate_scan_enabled SMALLINT DEFAULT 1 NOT NULL, duplicate_scan_frequency TEXT DEFAULT 'after_import' NOT NULL, duplicate_scan_cron TEXT DEFAULT '' NOT NULL, duplicate_scan_hour INTEGER DEFAULT 3 NOT NULL, duplicate_scan_chunk_size INTEGER DEFAULT 5000 NOT NULL, duplicate_scan_debounce_seconds INTEGER DEFAULT 5 NOT NULL ); -- Persisted scheduled jobs (initial focus: auto-send). Rows remain until dispatched or manually cleared. CREATE TABLE IF NOT EXISTS cwa_scheduled_jobs( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, job_type TEXT NOT NULL, -- e.g., 'auto_send' book_id INTEGER, user_id INTEGER, username TEXT, title TEXT, scheduler_job_id TEXT DEFAULT '', -- APScheduler job id for cancellation run_at_utc TEXT NOT NULL, -- ISO8601 UTC timestamp created_at_utc TEXT NOT NULL, -- ISO8601 UTC timestamp state TEXT NOT NULL DEFAULT 'scheduled', -- 'scheduled' | 'dispatched' | 'cancelled' last_error TEXT DEFAULT '' ); CREATE TABLE IF NOT EXISTS cwa_user_activity ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, user_name TEXT, event_type TEXT, item_id INTEGER, item_title TEXT, extra_data TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX IF NOT EXISTS idx_activity_user ON cwa_user_activity(user_id); CREATE INDEX IF NOT EXISTS idx_activity_event ON cwa_user_activity(event_type); CREATE INDEX IF NOT EXISTS idx_activity_time ON cwa_user_activity(timestamp); -- Hardcover auto-fetch match queue for manual review of ambiguous matches CREATE TABLE IF NOT EXISTS hardcover_match_queue( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, book_id INTEGER NOT NULL, book_title TEXT NOT NULL, book_authors TEXT NOT NULL, search_query TEXT NOT NULL, hardcover_results TEXT NOT NULL, -- JSON array of MetaRecord candidates confidence_scores TEXT NOT NULL, -- JSON array of [score, reason] tuples created_at TEXT NOT NULL, reviewed INTEGER DEFAULT 0 NOT NULL, -- 0=pending, 1=reviewed selected_result_id TEXT DEFAULT NULL, -- Hardcover ID if manually selected review_action TEXT DEFAULT NULL, -- 'accept', 'reject', 'skip' reviewed_at TEXT DEFAULT NULL, reviewed_by TEXT DEFAULT NULL ); CREATE INDEX IF NOT EXISTS idx_hardcover_queue_book ON hardcover_match_queue(book_id); CREATE INDEX IF NOT EXISTS idx_hardcover_queue_reviewed ON hardcover_match_queue(reviewed); -- Stats for hardcover auto-fetch operations CREATE TABLE IF NOT EXISTS hardcover_auto_fetch_stats( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, timestamp TEXT NOT NULL, books_processed INTEGER DEFAULT 0 NOT NULL, auto_matched INTEGER DEFAULT 0 NOT NULL, queued_for_review INTEGER DEFAULT 0 NOT NULL, skipped_no_results INTEGER DEFAULT 0 NOT NULL, errors INTEGER DEFAULT 0 NOT NULL, avg_confidence REAL DEFAULT 0.0 NOT NULL ); -- Duplicate detection cache table CREATE TABLE IF NOT EXISTS cwa_duplicate_cache ( id INTEGER PRIMARY KEY CHECK (id = 1), -- Singleton table, only one row scan_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, duplicate_groups_json TEXT, -- JSON serialized duplicate groups total_count INTEGER DEFAULT 0, scan_pending INTEGER DEFAULT 1, -- 1=needs scan, 0=cache valid last_scanned_book_id INTEGER DEFAULT 0, -- Track last scanned book for incremental updates scan_duration_seconds REAL DEFAULT 0, -- Performance tracking scan_method_used TEXT DEFAULT 'python' -- Track which method was used: 'sql', 'python', 'hybrid' ); -- Insert default row for cache table INSERT OR IGNORE INTO cwa_duplicate_cache (id, scan_pending) VALUES (1, 1); -- Auto-resolution audit log CREATE TABLE IF NOT EXISTS cwa_duplicate_resolutions ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, group_hash TEXT NOT NULL, group_title TEXT, group_author TEXT, kept_book_id INTEGER NOT NULL, deleted_book_ids TEXT NOT NULL, -- JSON array of deleted IDs strategy TEXT NOT NULL, -- 'newest', 'highest_quality_format', 'most_metadata', 'largest_file_size' trigger_type TEXT NOT NULL, -- 'manual', 'scheduled', 'automatic' backed_up INTEGER DEFAULT 1, -- 1=yes, 0=no user_id INTEGER, -- NULL for automatic, admin user ID for manual notes TEXT ); CREATE INDEX IF NOT EXISTS idx_duplicate_resolutions_timestamp ON cwa_duplicate_resolutions(timestamp); CREATE INDEX IF NOT EXISTS idx_duplicate_resolutions_group_hash ON cwa_duplicate_resolutions(group_hash);