-- Reference Data Seed File for Manuscript Analyzer
-- This file populates the reference data system with all required collections
-- IMPORTANT: Run this AFTER creating the analyzer schema and tables in MyStoryFlow database

-- Ensure analyzer schema exists
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'analyzer') THEN
        RAISE EXCEPTION 'Analyzer schema does not exist. Please run F002_DATABASE_SCHEMA.sql first.';
    END IF;
END $$;

-- Insert Reference Collections
INSERT INTO analyzer.reference_collections (collection_name, version, description, is_active) VALUES
  ('manuscript-genres', '1.0.0', 'All book and manuscript genres for analysis', true),
  ('analysis-statuses', '1.0.0', 'Processing status values for manuscript analysis', true),
  ('manuscript-types', '1.0.0', 'Supported file types and formats', true),
  ('scoring-categories', '1.0.0', 'Analysis evaluation categories and weights', true),
  ('subscription-tiers', '1.0.0', 'Pricing plans and feature tiers', true),
  ('analysis-priorities', '1.0.0', 'Processing priority levels', true),
  ('manuscript-statuses', '1.0.0', 'Overall manuscript status values', true),
  ('report-formats', '1.0.0', 'Available export formats for reports', true),
  ('notification-types', '1.0.0', 'Types of notifications sent to users', true),
  ('chunk-types', '1.0.0', 'Text segmentation types for AI processing', true),
  ('ai-providers', '1.0.0', 'Available AI service providers', true),
  ('analysis-methods', '1.0.0', 'Analysis methods and evaluation types', true),
  ('export-statuses', '1.0.0', 'Export generation status values', true);

-- Get collection IDs for reference
DO $$
DECLARE
  genres_id UUID;
  statuses_id UUID;
  types_id UUID;
  categories_id UUID;
  tiers_id UUID;
  priorities_id UUID;
  manuscript_statuses_id UUID;
  formats_id UUID;
  notification_types_id UUID;
  chunk_types_id UUID;
  ai_providers_id UUID;
  analysis_methods_id UUID;
  export_statuses_id UUID;
BEGIN
  -- Get collection IDs
  SELECT id INTO genres_id FROM analyzer.reference_collections WHERE collection_name = 'manuscript-genres';
  SELECT id INTO statuses_id FROM analyzer.reference_collections WHERE collection_name = 'analysis-statuses';
  SELECT id INTO types_id FROM analyzer.reference_collections WHERE collection_name = 'manuscript-types';
  SELECT id INTO categories_id FROM analyzer.reference_collections WHERE collection_name = 'scoring-categories';
  SELECT id INTO tiers_id FROM analyzer.reference_collections WHERE collection_name = 'subscription-tiers';
  SELECT id INTO priorities_id FROM analyzer.reference_collections WHERE collection_name = 'analysis-priorities';
  SELECT id INTO manuscript_statuses_id FROM analyzer.reference_collections WHERE collection_name = 'manuscript-statuses';
  SELECT id INTO formats_id FROM analyzer.reference_collections WHERE collection_name = 'report-formats';
  SELECT id INTO notification_types_id FROM analyzer.reference_collections WHERE collection_name = 'notification-types';
  SELECT id INTO chunk_types_id FROM analyzer.reference_collections WHERE collection_name = 'chunk-types';
  SELECT id INTO ai_providers_id FROM analyzer.reference_collections WHERE collection_name = 'ai-providers';
  SELECT id INTO analysis_methods_id FROM analyzer.reference_collections WHERE collection_name = 'analysis-methods';
  SELECT id INTO export_statuses_id FROM analyzer.reference_collections WHERE collection_name = 'export-statuses';

  -- Manuscript Genres (Fiction)
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (genres_id, 'romance', 'Romance', 'Love stories and romantic relationships', 'fiction', 1, true, '{"typical_length": "50000-90000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'mystery', 'Mystery', 'Crime solving and detective stories', 'fiction', 2, true, '{"typical_length": "70000-90000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'thriller', 'Thriller', 'Suspenseful and fast-paced stories', 'fiction', 3, true, '{"typical_length": "80000-100000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'fantasy', 'Fantasy', 'Magical and supernatural elements', 'fiction', 4, true, '{"typical_length": "80000-120000", "target_audience": "all", "market_size": "large"}'),
    (genres_id, 'science-fiction', 'Science Fiction', 'Futuristic and scientific concepts', 'fiction', 5, true, '{"typical_length": "80000-120000", "target_audience": "all", "market_size": "medium"}'),
    (genres_id, 'literary-fiction', 'Literary Fiction', 'Character-driven, artistic prose', 'fiction', 6, true, '{"typical_length": "80000-100000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'historical-fiction', 'Historical Fiction', 'Stories set in the past', 'fiction', 7, true, '{"typical_length": "80000-120000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'young-adult', 'Young Adult', 'Stories for teenage readers', 'fiction', 8, true, '{"typical_length": "50000-80000", "target_audience": "teen", "market_size": "large"}'),
    (genres_id, 'middle-grade', 'Middle Grade', 'Stories for children 8-12', 'fiction', 9, true, '{"typical_length": "20000-50000", "target_audience": "children", "market_size": "medium"}'),
    (genres_id, 'horror', 'Horror', 'Scary and supernatural stories', 'fiction', 10, true, '{"typical_length": "70000-90000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'contemporary-fiction', 'Contemporary Fiction', 'Modern-day realistic stories', 'fiction', 11, true, '{"typical_length": "80000-100000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'dystopian', 'Dystopian', 'Dark future societies', 'fiction', 12, true, '{"typical_length": "70000-100000", "target_audience": "ya-adult", "market_size": "medium"}'),
    (genres_id, 'urban-fantasy', 'Urban Fantasy', 'Fantasy in modern settings', 'fiction', 13, true, '{"typical_length": "80000-100000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'paranormal-romance', 'Paranormal Romance', 'Romance with supernatural elements', 'fiction', 14, true, '{"typical_length": "70000-90000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'cozy-mystery', 'Cozy Mystery', 'Gentle mysteries without violence', 'fiction', 15, true, '{"typical_length": "60000-80000", "target_audience": "adult", "market_size": "medium"}');

  -- Manuscript Genres (Non-Fiction)
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (genres_id, 'memoir', 'Memoir', 'Personal life stories and experiences', 'non-fiction', 20, true, '{"typical_length": "70000-90000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'biography', 'Biography', 'Life stories of other people', 'non-fiction', 21, true, '{"typical_length": "80000-120000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'self-help', 'Self-Help', 'Personal development and improvement', 'non-fiction', 22, true, '{"typical_length": "50000-70000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'business', 'Business', 'Business strategies and management', 'non-fiction', 23, true, '{"typical_length": "50000-80000", "target_audience": "professional", "market_size": "large"}'),
    (genres_id, 'history', 'History', 'Historical events and analysis', 'non-fiction', 24, true, '{"typical_length": "80000-120000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'true-crime', 'True Crime', 'Real criminal cases and investigations', 'non-fiction', 25, true, '{"typical_length": "70000-100000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'health-wellness', 'Health & Wellness', 'Health, fitness, and wellness topics', 'non-fiction', 26, true, '{"typical_length": "50000-80000", "target_audience": "adult", "market_size": "large"}'),
    (genres_id, 'travel', 'Travel', 'Travel guides and experiences', 'non-fiction', 27, true, '{"typical_length": "60000-80000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'cooking', 'Cooking', 'Recipes and culinary topics', 'non-fiction', 28, true, '{"typical_length": "40000-80000", "target_audience": "adult", "market_size": "medium"}'),
    (genres_id, 'parenting', 'Parenting', 'Child-rearing and family topics', 'non-fiction', 29, true, '{"typical_length": "50000-70000", "target_audience": "parents", "market_size": "large"}');

  -- Analysis Statuses
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (statuses_id, 'pending', 'Pending', 'Analysis queued and waiting to start', 'processing', 1, true, '{"color": "gray", "icon": "clock"}'),
    (statuses_id, 'processing', 'Processing', 'Analysis currently in progress', 'processing', 2, true, '{"color": "blue", "icon": "spinner"}'),
    (statuses_id, 'completed', 'Completed', 'Analysis finished successfully', 'processing', 3, true, '{"color": "green", "icon": "check"}'),
    (statuses_id, 'failed', 'Failed', 'Analysis encountered an error', 'processing', 4, true, '{"color": "red", "icon": "x"}'),
    (statuses_id, 'cancelled', 'Cancelled', 'Analysis was cancelled by user', 'processing', 5, true, '{"color": "yellow", "icon": "stop"}'),
    (statuses_id, 'partial', 'Partial', 'Analysis completed with some errors', 'processing', 6, true, '{"color": "orange", "icon": "warning"}');

  -- Manuscript Types
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (types_id, 'pdf', 'PDF', 'Portable Document Format files', 'document', 1, true, '{"max_size": "50MB", "supports_text_extraction": true}'),
    (types_id, 'docx', 'Word Document', 'Microsoft Word DOCX files', 'document', 2, true, '{"max_size": "25MB", "supports_text_extraction": true}'),
    (types_id, 'txt', 'Text File', 'Plain text files', 'document', 3, true, '{"max_size": "10MB", "supports_text_extraction": true}'),
    (types_id, 'rtf', 'Rich Text Format', 'RTF formatted documents', 'document', 4, true, '{"max_size": "15MB", "supports_text_extraction": true}'),
    (types_id, 'google-docs', 'Google Docs', 'Google Docs shareable links', 'cloud', 5, true, '{"max_size": "unlimited", "supports_text_extraction": true}'),
    (types_id, 'odt', 'OpenDocument Text', 'OpenOffice/LibreOffice documents', 'document', 6, true, '{"max_size": "25MB", "supports_text_extraction": true}');

  -- Scoring Categories
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (categories_id, 'structure', 'Structure & Pacing', 'Overall story structure and pacing analysis', 'analysis', 1, true, '{"weight": 0.15, "evaluation_points": 25}'),
    (categories_id, 'character-development', 'Character Development', 'Character arcs, growth, and consistency', 'analysis', 2, true, '{"weight": 0.20, "evaluation_points": 30}'),
    (categories_id, 'plot-conflict', 'Plot & Conflict', 'Plot development and conflict resolution', 'analysis', 3, true, '{"weight": 0.15, "evaluation_points": 25}'),
    (categories_id, 'writing-craft', 'Writing Craft', 'Prose quality, style, and technique', 'analysis', 4, true, '{"weight": 0.15, "evaluation_points": 20}'),
    (categories_id, 'dialogue', 'Dialogue', 'Character voice and conversation quality', 'analysis', 5, true, '{"weight": 0.10, "evaluation_points": 15}'),
    (categories_id, 'pacing', 'Pacing', 'Story rhythm and tension management', 'analysis', 6, true, '{"weight": 0.10, "evaluation_points": 12}'),
    (categories_id, 'world-building', 'World Building', 'Setting development and consistency', 'analysis', 7, true, '{"weight": 0.05, "evaluation_points": 10}'),
    (categories_id, 'theme-meaning', 'Theme & Meaning', 'Thematic depth and message clarity', 'analysis', 8, true, '{"weight": 0.05, "evaluation_points": 8}'),
    (categories_id, 'market-readiness', 'Market Readiness', 'Commercial viability and genre compliance', 'analysis', 9, true, '{"weight": 0.05, "evaluation_points": 10}');

  -- Notification Types
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (notification_types_id, 'analysis-complete', 'Analysis Complete', 'Manuscript analysis has finished processing', 'analysis', 1, true, '{"priority": "high", "channels": ["email", "in-app"]}'),
    (notification_types_id, 'upload-failed', 'Upload Failed', 'Document upload encountered an error', 'error', 2, true, '{"priority": "high", "channels": ["in-app"]}'),
    (notification_types_id, 'subscription-renewed', 'Subscription Renewed', 'Monthly subscription has been renewed', 'billing', 3, true, '{"priority": "medium", "channels": ["email"]}'),
    (notification_types_id, 'report-ready', 'Report Ready', 'Analysis report is ready for download', 'report', 4, true, '{"priority": "high", "channels": ["email", "in-app"]}'),
    (notification_types_id, 'quota-warning', 'Quota Warning', 'Approaching monthly analysis limit', 'usage', 5, true, '{"priority": "medium", "channels": ["in-app"]}'),
    (notification_types_id, 'processing-started', 'Processing Started', 'Manuscript processing has begun', 'analysis', 6, true, '{"priority": "low", "channels": ["in-app"]}');

  -- Chunk Types
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (chunk_types_id, 'chapter', 'Chapter', 'Complete chapter segments', 'structural', 1, true, '{"max_tokens": 8000, "overlap": 0}'),
    (chunk_types_id, 'section', 'Section', 'Section-based segments', 'structural', 2, true, '{"max_tokens": 4000, "overlap": 0}'),
    (chunk_types_id, 'overlap', 'Overlapping', 'Overlapping segments for context', 'contextual', 3, true, '{"max_tokens": 4000, "overlap": 500}'),
    (chunk_types_id, 'paragraph', 'Paragraph', 'Paragraph-level segments', 'granular', 4, true, '{"max_tokens": 1000, "overlap": 0}'),
    (chunk_types_id, 'scene', 'Scene', 'Scene-based segments for fiction', 'narrative', 5, true, '{"max_tokens": 3000, "overlap": 200}');

  -- AI Providers
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (ai_providers_id, 'openai', 'OpenAI', 'GPT-4 and GPT-3.5 models', 'primary', 1, true, '{"models": ["gpt-4-turbo", "gpt-3.5-turbo"], "max_context": 128000, "cost_per_1k": 0.03}'),
    (ai_providers_id, 'anthropic', 'Anthropic', 'Claude 3 models', 'primary', 2, true, '{"models": ["claude-3-opus", "claude-3-sonnet"], "max_context": 200000, "cost_per_1k": 0.025}'),
    (ai_providers_id, 'gemini', 'Google Gemini', 'Gemini Pro models with large context', 'primary', 3, true, '{"models": ["gemini-1.5-pro", "gemini-1.5-flash"], "max_context": 2000000, "cost_per_1k": 0.002}'),
    (ai_providers_id, 'xai', 'xAI Grok', 'Grok models with large context windows', 'primary', 4, true, '{"models": ["grok-2", "grok-2-mini"], "max_context": 131072, "cost_per_1k": 0.02}'),
    (ai_providers_id, 'together', 'Together AI', 'Open source models', 'secondary', 5, true, '{"models": ["mixtral-8x7b", "llama-3-70b"], "max_context": 32000, "cost_per_1k": 0.001}'),
    (ai_providers_id, 'local', 'Local Models', 'Self-hosted models', 'fallback', 6, true, '{"models": ["llama-3-8b", "mistral-7b"], "max_context": 8000, "cost_per_1k": 0}');

  -- Analysis Methods
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (analysis_methods_id, 'plot-structure', 'Plot Structure Analysis', 'Three-act structure, plot points, and story arc', 'narrative', 1, true, '{"evaluation_points": 15, "ai_required": true}'),
    (analysis_methods_id, 'character-analysis', 'Character Development', 'Character arcs, motivations, and consistency', 'narrative', 2, true, '{"evaluation_points": 20, "ai_required": true}'),
    (analysis_methods_id, 'dialogue-quality', 'Dialogue Analysis', 'Voice distinctiveness and conversation flow', 'craft', 3, true, '{"evaluation_points": 10, "ai_required": true}'),
    (analysis_methods_id, 'pacing-analysis', 'Pacing & Rhythm', 'Scene length, tension curves, and momentum', 'structure', 4, true, '{"evaluation_points": 12, "ai_required": true}'),
    (analysis_methods_id, 'theme-extraction', 'Theme & Meaning', 'Central themes and symbolic elements', 'literary', 5, true, '{"evaluation_points": 8, "ai_required": true}'),
    (analysis_methods_id, 'style-analysis', 'Writing Style', 'Prose quality, readability, and voice', 'craft', 6, true, '{"evaluation_points": 15, "ai_required": false}'),
    (analysis_methods_id, 'genre-compliance', 'Genre Conventions', 'Adherence to genre expectations', 'market', 7, true, '{"evaluation_points": 10, "ai_required": true}'),
    (analysis_methods_id, 'market-readiness', 'Market Analysis', 'Commercial viability assessment', 'market', 8, true, '{"evaluation_points": 10, "ai_required": true}');

  -- Export Statuses
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (export_statuses_id, 'generating', 'Generating', 'Export file is being created', 'processing', 1, true, '{"color": "blue", "icon": "spinner"}'),
    (export_statuses_id, 'ready', 'Ready', 'Export file is ready for download', 'complete', 2, true, '{"color": "green", "icon": "download"}'),
    (export_statuses_id, 'expired', 'Expired', 'Export link has expired', 'inactive', 3, true, '{"color": "gray", "icon": "clock"}'),
    (export_statuses_id, 'failed', 'Failed', 'Export generation failed', 'error', 4, true, '{"color": "red", "icon": "x"}');

  -- Subscription Tiers
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (tiers_id, 'free', 'Free Tier', 'Limited free access for new users', 'subscription', 1, true, '{"price_cents": 0, "word_limit": 5000, "analyses_per_month": 3, "features": ["basic_analysis", "5k_word_limit", "community_access"]}'),
    (tiers_id, 'professional', 'Professional', 'Per-analysis pricing for occasional users', 'subscription', 2, true, '{"price_cents": 3900, "word_limit": 150000, "analyses_per_month": null, "features": ["full_analysis", "150k_words", "48h_turnaround", "detailed_report"]}'),
    (tiers_id, 'author-bundle', 'Author Bundle', 'Monthly subscription for active authors', 'subscription', 3, true, '{"price_cents": 14900, "word_limit": 150000, "analyses_per_month": 5, "features": ["priority_processing", "revision_tracking", "coach_access", "beta_community"]}'),
    (tiers_id, 'enterprise', 'Enterprise', 'Unlimited access for coaches and businesses', 'subscription', 4, true, '{"price_cents": 39900, "word_limit": null, "analyses_per_month": null, "features": ["unlimited_analyses", "white_label", "api_access", "priority_support"]}');

  -- Analysis Priorities
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (priorities_id, 'low', 'Low Priority', 'Standard processing queue', 'queue', 1, true, '{"priority_score": 1, "estimated_wait": "24-48h"}'),
    (priorities_id, 'normal', 'Normal Priority', 'Regular processing speed', 'queue', 2, true, '{"priority_score": 5, "estimated_wait": "12-24h"}'),
    (priorities_id, 'high', 'High Priority', 'Priority processing for subscribers', 'queue', 3, true, '{"priority_score": 8, "estimated_wait": "4-12h"}'),
    (priorities_id, 'urgent', 'Urgent Priority', 'Express processing with additional fee', 'queue', 4, true, '{"priority_score": 10, "estimated_wait": "1-4h", "additional_fee": 1900}');

  -- Manuscript Statuses
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (manuscript_statuses_id, 'uploading', 'Uploading', 'File upload in progress', 'upload', 1, true, '{"color": "blue", "icon": "upload"}'),
    (manuscript_statuses_id, 'processing', 'Processing', 'Extracting and preparing content', 'upload', 2, true, '{"color": "blue", "icon": "cog"}'),
    (manuscript_statuses_id, 'ready', 'Ready for Analysis', 'Ready to begin analysis', 'ready', 3, true, '{"color": "green", "icon": "check"}'),
    (manuscript_statuses_id, 'analyzing', 'Analyzing', 'AI analysis in progress', 'analysis', 4, true, '{"color": "purple", "icon": "brain"}'),
    (manuscript_statuses_id, 'completed', 'Analysis Complete', 'Analysis finished, report available', 'complete', 5, true, '{"color": "green", "icon": "document"}'),
    (manuscript_statuses_id, 'error', 'Error', 'Processing or analysis failed', 'error', 6, true, '{"color": "red", "icon": "exclamation"}'),
    (manuscript_statuses_id, 'archived', 'Archived', 'Manuscript archived by user', 'archived', 7, true, '{"color": "gray", "icon": "archive"}');

  -- Report Formats
  INSERT INTO analyzer.reference_data_items (collection_id, code, label, description, category, sort_order, is_active, metadata) VALUES
    (formats_id, 'pdf', 'PDF Report', 'Professional PDF format for printing', 'export', 1, true, '{"file_extension": "pdf", "supports_formatting": true}'),
    (formats_id, 'html', 'Web Report', 'Interactive HTML report for web viewing', 'export', 2, true, '{"file_extension": "html", "supports_interactive": true}'),
    (formats_id, 'json', 'Data Export', 'Raw analysis data in JSON format', 'export', 3, true, '{"file_extension": "json", "supports_api": true}'),
    (formats_id, 'docx', 'Word Document', 'Editable Word document format', 'export', 4, true, '{"file_extension": "docx", "supports_editing": true}');

END $$;