-- Enable RLS on table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Set tenant context (done by API middleware)
SET app.current_tenant_id = 'tenant-uuid';

Request → Auth Middleware → Extract Tenant → SET app.current_tenant_id → Query

CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  settings JSONB DEFAULT '{}',
  billing_plan VARCHAR(50) DEFAULT 'free',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  role VARCHAR(50) DEFAULT 'member',
  stytch_user_id VARCHAR(255),
  last_login_at TIMESTAMP,

  -- Phase 2 additions (Migration 175)
  user_type user_type NOT NULL DEFAULT 'individual',
  organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(tenant_id, email)
);

-- RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  status VARCHAR(50) DEFAULT 'active',
  phase VARCHAR(50) DEFAULT 'CREATED',
  template VARCHAR(100),

  -- Phase timing columns
  discovery_started_at TIMESTAMP,
  discovery_completed_at TIMESTAMP,
  feature_selection_started_at TIMESTAMP,
  feature_selection_completed_at TIMESTAMP,
  plan_generation_started_at TIMESTAMP,
  plan_generation_completed_at TIMESTAMP,
  planning_started_at TIMESTAMP,
  planning_completed_at TIMESTAMP,
  visual_discovery_started_at TIMESTAMP,
  visual_discovery_completed_at TIMESTAMP,
  specification_started_at TIMESTAMP,
  specification_completed_at TIMESTAMP,

  -- Metadata
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_projects_tenant ON projects(tenant_id);
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_phase ON projects(phase);

CREATE TABLE question_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_key VARCHAR(100) UNIQUE NOT NULL,
  category VARCHAR(100) NOT NULL,
  question_text TEXT NOT NULL,
  description TEXT,
  answer_type VARCHAR(50) NOT NULL,
  options JSONB,
  validation_rules JSONB,
  display_order INTEGER DEFAULT 0,
  is_required BOOLEAN DEFAULT true,
  applicable_templates VARCHAR(100)[] DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE graph_nodes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  parent_id UUID REFERENCES graph_nodes(id) ON DELETE CASCADE,
  type VARCHAR(50) NOT NULL, -- 'section', 'question', 'suggestion'
  template_key VARCHAR(100),

  content JSONB NOT NULL,
  -- content: {
  --   question_text: string,
  --   description: string,
  --   variants: [{type, text, confidence}],
  --   answer_type: string,
  --   options: [{value, label}]
  -- }

  status VARCHAR(50) DEFAULT 'pending',
  display_order INTEGER DEFAULT 0,
  dependencies UUID[] DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_graph_nodes_project ON graph_nodes(project_id);
CREATE INDEX idx_graph_nodes_parent ON graph_nodes(parent_id);
CREATE INDEX idx_graph_nodes_status ON graph_nodes(project_id, status);

CREATE TABLE plan_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  question_plan_id UUID REFERENCES question_plans(id) ON DELETE CASCADE,
  feature_id UUID REFERENCES feature_selections(id),

  question_text TEXT NOT NULL,
  description TEXT,
  answer_type VARCHAR(50) NOT NULL,
  options JSONB,

  -- Prefill data
  prefilled_answer TEXT,
  confidence_score DECIMAL(3,2) DEFAULT 0,
  prefill_source VARCHAR(50), -- 'persona', 'pattern', 'cascade', 'inference'
  prefill_explanation TEXT,

  -- User response
  user_answer TEXT,
  status VARCHAR(50) DEFAULT 'pending',

  -- Organization
  group_key VARCHAR(100),
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_plan_questions_project ON plan_questions(project_id);
CREATE INDEX idx_plan_questions_plan ON plan_questions(question_plan_id);
CREATE INDEX idx_plan_questions_status ON plan_questions(project_id, status);

CREATE TABLE answers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  question_id UUID NOT NULL, -- Can be graph_node or plan_question
  question_type VARCHAR(50) NOT NULL, -- 'discovery' or 'plan'

  value JSONB NOT NULL,
  -- value: {
  --   raw: string | string[] | boolean | number,
  --   formatted: string,
  --   selectedVariant: string,
  --   confidence: number,
  --   notes: string
  -- }

  source VARCHAR(50) DEFAULT 'user', -- 'user', 'prefill_accepted', 'agent'
  answered_by UUID REFERENCES users(id),
  answered_at TIMESTAMP DEFAULT NOW(),

  -- Processing status
  processed BOOLEAN DEFAULT false,
  processing_results JSONB,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_answers_project ON answers(project_id);
CREATE INDEX idx_answers_question ON answers(question_id);

CREATE TABLE question_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  total_questions INTEGER DEFAULT 0,
  questions_by_feature JSONB, -- {feature_id: count}
  questions_by_group JSONB,   -- {group_key: count}

  prefill_stats JSONB,
  -- prefill_stats: {
  --   total: number,
  --   high_confidence: number,
  --   medium_confidence: number,
  --   low_confidence: number,
  --   by_source: {persona: n, pattern: n, ...}
  -- }

  status VARCHAR(50) DEFAULT 'generating',
  generated_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE question_pools (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  category VARCHAR(100),
  industry VARCHAR(100)[],
  scenario_type VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_question_pools_category ON question_pools(category);
CREATE INDEX idx_question_pools_industry ON question_pools USING GIN(industry);

CREATE TABLE pool_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pool_id UUID REFERENCES question_pools(id) ON DELETE CASCADE,
  base_prompt TEXT NOT NULL,
  question_type VARCHAR(50),
  priority INTEGER DEFAULT 0,
  display_order INTEGER,
  prerequisite_questions UUID[],
  metadata JSONB,
  embedding vector(1536), -- For semantic search
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_pool_questions_pool ON pool_questions(pool_id);
CREATE INDEX idx_pool_questions_type ON pool_questions(question_type);
CREATE INDEX idx_pool_questions_display_order ON pool_questions(pool_id, display_order);

CREATE TABLE pool_activation_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pool_id UUID REFERENCES question_pools(id) ON DELETE CASCADE,
  trigger_type VARCHAR(50) NOT NULL, -- 'keyword', 'pattern', 'domain', 'entity'
  trigger_value TEXT NOT NULL,
  confidence_threshold NUMERIC(3,2) DEFAULT 0.80,
  industry_filter VARCHAR(100)[],
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_pool_activation_pool ON pool_activation_rules(pool_id);
CREATE INDEX idx_pool_activation_trigger ON pool_activation_rules(trigger_type);

CREATE TABLE ontology_domain_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  domain VARCHAR(50) NOT NULL, -- 'ecommerce', 'saas', 'healthcare', 'internal-tools', 'general'
  category VARCHAR(50) NOT NULL, -- 'entity', 'workflow', 'integration'
  canonical_name VARCHAR(255) NOT NULL,
  display_name VARCHAR(255),
  template JSONB NOT NULL,
  -- template: {
  --   suggestedAttributes: [{name, type, required, description}],
  --   statusEnum: string[],
  --   transitions: [{from, to, trigger, conditions}],
  --   businessRules: string[],
  --   requiredQuestions: string[], // ← Questions embedded here!
  --   relatedEntities: string[]
  -- }
  keywords TEXT[],
  embedding vector(1536),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_domain_templates_domain ON ontology_domain_templates(domain);
CREATE INDEX idx_domain_templates_category ON ontology_domain_templates(category);
CREATE INDEX idx_domain_templates_keywords ON ontology_domain_templates USING GIN(keywords);

CREATE TABLE ontology_pattern_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pattern_name VARCHAR(255) NOT NULL,
  display_name VARCHAR(255),
  category VARCHAR(100),
  complexity VARCHAR(50), -- 'simple', 'moderate', 'complex'
  template JSONB NOT NULL,
  -- template: {
  --   description: string,
  --   steps: [{name, description}],
  --   requiredQuestions: string[],
  --   technicalConsiderations: string[],
  --   bestPractices: string[]
  -- }
  keywords TEXT[],
  compatible_domains TEXT[],
  embedding vector(1536),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_pattern_templates_category ON ontology_pattern_templates(category);
CREATE INDEX idx_pattern_templates_complexity ON ontology_pattern_templates(complexity);

CREATE TABLE ontology_integration_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider VARCHAR(255) NOT NULL, -- 'stripe', 'sendgrid', 'twilio', etc.
  display_name VARCHAR(255),
  category VARCHAR(100), -- 'payment', 'email', 'auth', 'storage'
  capabilities TEXT[],
  required_config TEXT[],
  template JSONB NOT NULL,
  -- template: {
  --   apiEndpoints: [{method, path, description}],
  --   webhookEvents: string[],
  --   authMethods: string[],
  --   requiredQuestions: string[],
  --   configurationSteps: string[]
  -- }
  docs_reference TEXT,
  embedding vector(1536),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_integration_templates_provider ON ontology_integration_templates(provider);
CREATE INDEX idx_integration_templates_category ON ontology_integration_templates(category);
CREATE INDEX idx_integration_templates_capabilities ON ontology_integration_templates USING GIN(capabilities);

CREATE TABLE feature_driven_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  curation_id UUID REFERENCES feature_curations(id) ON DELETE CASCADE,
  agent_id VARCHAR(255),
  status VARCHAR(50) DEFAULT 'active', -- 'active', 'completed', 'archived'
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_feature_driven_plans_project ON feature_driven_plans(project_id);
CREATE INDEX idx_feature_driven_plans_curation ON feature_driven_plans(curation_id);
CREATE INDEX idx_feature_driven_plans_status ON feature_driven_plans(status);

CREATE TABLE plan_sections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id UUID REFERENCES feature_driven_plans(id) ON DELETE CASCADE,
  feature_id UUID REFERENCES feature_catalog(id),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  display_order INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_plan_sections_plan ON plan_sections(plan_id);
CREATE INDEX idx_plan_sections_feature ON plan_sections(feature_id);
CREATE INDEX idx_plan_sections_order ON plan_sections(plan_id, display_order);

CREATE TABLE canonical_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_id UUID,
  question_text TEXT NOT NULL,
  category VARCHAR(100),
  priority INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_canonical_questions_entity ON canonical_questions(entity_id);
CREATE INDEX idx_canonical_questions_category ON canonical_questions(category);

CREATE TABLE entity_questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_id UUID REFERENCES kb_canonical_entities(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type VARCHAR(50),
  context JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_entity_questions_entity ON entity_questions(entity_id);
CREATE INDEX idx_entity_questions_type ON entity_questions(question_type);

CREATE TABLE kb_naics_standard_mappings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  naics_code VARCHAR(6) REFERENCES kb_naics_codes(code) ON DELETE CASCADE,
  entity_id UUID REFERENCES kb_canonical_entities(id) ON DELETE CASCADE,
  relevance_score NUMERIC(3,2),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(naics_code, entity_id)
);

CREATE INDEX idx_kb_naics_mappings_code ON kb_naics_standard_mappings(naics_code);
CREATE INDEX idx_kb_naics_mappings_entity ON kb_naics_standard_mappings(entity_id);

CREATE TABLE kb_canonical_entities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_name VARCHAR(255) NOT NULL,
  entity_type VARCHAR(100), -- 'user', 'resource', 'action', 'concept'
  synonyms TEXT[],
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_kb_canonical_entities_type ON kb_canonical_entities(entity_type);
CREATE INDEX idx_kb_canonical_entities_synonyms ON kb_canonical_entities USING GIN(synonyms);

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  type VARCHAR(50) NOT NULL, -- 'PROVIDE', 'DECIDE', 'REVIEW', 'CONNECT'
  title VARCHAR(255) NOT NULL,
  description TEXT,
  status VARCHAR(50) DEFAULT 'pending',
  priority VARCHAR(20) DEFAULT 'medium',

  assignee_type VARCHAR(20) DEFAULT 'user', -- 'user' or 'agent'
  assignee_id UUID,
  due_date TIMESTAMP,

  -- Task Mode UI routing
  component_key VARCHAR(100), -- Routes to TaskComponent (e.g., 'permission-editor')

  -- Dependencies
  dependencies UUID[] DEFAULT '{}',
  blocked_by UUID[] DEFAULT '{}',

  -- Completion
  completed_at TIMESTAMP,
  completed_by UUID,
  completion_notes TEXT,

  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_status ON tasks(project_id, status);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_type, assignee_id);

CREATE TABLE task_actions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,

  type VARCHAR(50) NOT NULL,
  -- Types: FORM_SUBMIT, SINGLE_SELECT, MULTI_SELECT, TEXT_INPUT,
  --        FILE_UPLOAD, APPROVE_REJECT, EXTERNAL_LINK

  label VARCHAR(255) NOT NULL,
  description TEXT,
  config JSONB NOT NULL,
  -- config varies by type:
  -- FORM_SUBMIT: {formId, submitUrl}
  -- SINGLE_SELECT: {options: [{value, label}]}
  -- APPROVE_REJECT: {approveLabel, rejectLabel}

  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_task_actions_task ON task_actions(task_id);

CREATE TABLE task_targets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
  target_type VARCHAR(50) NOT NULL, -- 'question', 'feature', 'spec_section'
  target_id UUID NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_task_targets_task ON task_targets(task_id);
CREATE INDEX idx_task_targets_target ON task_targets(target_type, target_id);

CREATE TABLE action_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
  action_id UUID REFERENCES task_actions(id) ON DELETE CASCADE,

  idempotency_key VARCHAR(255) UNIQUE NOT NULL,
  submitted_by UUID REFERENCES users(id),
  submitted_at TIMESTAMP DEFAULT NOW(),

  data JSONB NOT NULL,
  result JSONB,

  -- Output binding results
  bindings_executed JSONB,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_action_submissions_task ON action_submissions(task_id);
CREATE INDEX idx_action_submissions_idempotency ON action_submissions(idempotency_key);

CREATE TABLE task_activity (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,

  activity_type VARCHAR(50) NOT NULL,
  -- Types: created, assigned, status_changed, action_submitted,
  --        completed, blocked, unblocked

  actor_type VARCHAR(20), -- 'user', 'agent', 'system'
  actor_id UUID,
  details JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_task_activity_task ON task_activity(task_id);
CREATE INDEX idx_task_activity_type ON task_activity(activity_type);

CREATE TABLE agent_memory (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  session_id UUID NOT NULL,
  agent_name VARCHAR(100) NOT NULL,

  message_role VARCHAR(20) NOT NULL, -- 'user', 'assistant', 'system'
  message_content TEXT NOT NULL,
  tokens_used INTEGER DEFAULT 0,

  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_agent_memory_session ON agent_memory(session_id);
CREATE INDEX idx_agent_memory_tenant ON agent_memory(tenant_id);
CREATE INDEX idx_agent_memory_agent ON agent_memory(agent_name);
CREATE INDEX idx_agent_memory_created ON agent_memory(created_at);

CREATE TABLE agent_executor_state (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  execution_id UUID UNIQUE NOT NULL,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  status VARCHAR(50) DEFAULT 'pending',
  -- Status: pending, running, paused, completed, failed

  strategy VARCHAR(50) NOT NULL,
  agents JSONB NOT NULL, -- [{name, role, status}]

  current_step INTEGER DEFAULT 0,
  total_steps INTEGER,

  started_at TIMESTAMP,
  completed_at TIMESTAMP,

  -- Budget tracking
  tokens_used INTEGER DEFAULT 0,
  tokens_limit INTEGER,
  cost_incurred DECIMAL(10,4) DEFAULT 0,
  cost_limit DECIMAL(10,4),

  -- Results
  intermediate_results JSONB DEFAULT '{}',
  final_result JSONB,
  error JSONB,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_executor_state_project ON agent_executor_state(project_id);
CREATE INDEX idx_executor_state_status ON agent_executor_state(status);

CREATE TABLE checkpoint_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  execution_id UUID REFERENCES agent_executor_state(execution_id) ON DELETE CASCADE,

  step_index INTEGER NOT NULL,
  step_name VARCHAR(100) NOT NULL,

  state JSONB NOT NULL,
  -- state: {
  --   completedSteps: string[],
  --   pendingSteps: string[],
  --   intermediateResults: {},
  --   agentStates: {},
  --   tokensUsed: number,
  --   costIncurred: number
  -- }

  created_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP -- Auto-cleanup after expiry
);

CREATE INDEX idx_checkpoints_execution ON checkpoint_snapshots(execution_id);
CREATE INDEX idx_checkpoints_expires ON checkpoint_snapshots(expires_at);

CREATE TABLE budget_limits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE UNIQUE,

  max_tokens INTEGER DEFAULT 100000,
  max_cost_usd DECIMAL(10,4) DEFAULT 5.00,
  warning_threshold DECIMAL(3,2) DEFAULT 0.80,
  on_limit_reached VARCHAR(20) DEFAULT 'pause', -- 'pause', 'fail', 'warn'

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE feature_candidate_sets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  candidates JSONB NOT NULL,
  -- candidates: [{
  --   id: uuid,
  --   name: string,
  --   description: string,
  --   category: string,
  --   complexity: string,
  --   score: number,
  --   signals_matched: string[]
  -- }]

  generation_metadata JSONB,
  generated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_feature_candidates_project ON feature_candidate_sets(project_id);

CREATE TABLE feature_selections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  candidate_set_id UUID REFERENCES feature_candidate_sets(id),

  feature_id UUID NOT NULL, -- From candidate set
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(100),
  complexity VARCHAR(20),

  selected BOOLEAN DEFAULT true,
  selection_order INTEGER,
  user_notes TEXT,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_feature_selections_project ON feature_selections(project_id);
CREATE INDEX idx_feature_selections_selected ON feature_selections(project_id, selected);

CREATE TABLE pattern_detections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  pattern_id VARCHAR(100) NOT NULL, -- From pattern library
  pattern_name VARCHAR(255) NOT NULL,
  confidence_score DECIMAL(3,2) NOT NULL,

  signals_matched JSONB, -- Which signals triggered this
  questions_suppressed UUID[], -- Questions made unnecessary
  spec_additions JSONB, -- Additions to spec

  applied BOOLEAN DEFAULT false,
  applied_at TIMESTAMP,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_pattern_detections_project ON pattern_detections(project_id);
CREATE INDEX idx_pattern_detections_pattern ON pattern_detections(pattern_id);

CREATE TABLE mistake_patterns (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  pattern_type VARCHAR(50) NOT NULL,
  -- Types: circular_reference, missing_dependency, invalid_state,
  --        timeout, budget_exceeded, validation_failure

  pattern_signature VARCHAR(255) UNIQUE NOT NULL, -- Hash of error context
  description TEXT,

  occurrences INTEGER DEFAULT 1,
  first_seen TIMESTAMP DEFAULT NOW(),
  last_seen TIMESTAMP DEFAULT NOW(),

  auto_fix_available BOOLEAN DEFAULT false,
  fix_strategy JSONB,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_mistake_patterns_type ON mistake_patterns(pattern_type);
CREATE INDEX idx_mistake_patterns_signature ON mistake_patterns(pattern_signature);

CREATE TABLE compiled_specs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  version VARCHAR(20) NOT NULL,
  format VARCHAR(20) DEFAULT 'v1', -- 'v1' or 'v2'

  spec JSONB NOT NULL, -- Full SpecV1/V2 object
  validation_result JSONB,

  compiled_at TIMESTAMP DEFAULT NOW(),
  compiled_by VARCHAR(50), -- 'user' or agent name

  -- Source tracking
  source_hash VARCHAR(64), -- Hash of inputs for cache invalidation
  inputs_snapshot JSONB
);

CREATE INDEX idx_compiled_specs_project ON compiled_specs(project_id);
CREATE INDEX idx_compiled_specs_version ON compiled_specs(project_id, version);

CREATE TABLE discovery_output (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE UNIQUE,

  summary TEXT,
  extracted_entities JSONB,
  -- extracted_entities: [{
  --   type: 'user' | 'resource' | 'action',
  --   name: string,
  --   attributes: {},
  --   confidence: number,
  --   source_questions: uuid[]
  -- }]

  ontology_coverage DECIMAL(3,2),
  key_decisions JSONB,
  compiled_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE planning_artifacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  artifact_type VARCHAR(50) NOT NULL, -- 'blueprint', 'implementation_plan'
  content JSONB NOT NULL,

  status VARCHAR(50) DEFAULT 'draft',
  approved_at TIMESTAMP,
  approved_by UUID,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_planning_artifacts_project ON planning_artifacts(project_id);
CREATE INDEX idx_planning_artifacts_type ON planning_artifacts(artifact_type);

CREATE TABLE design_discovery_output (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE UNIQUE,

  design_preferences JSONB,
  -- design_preferences: {
  --   colorPalette: {},
  --   typography: {},
  --   layoutPreferences: {},
  --   stylePreferences: []
  -- }

  research_findings JSONB,
  -- research_findings: {
  --   competitors: [],
  --   inspirations: [],
  --   patterns: []
  -- }

  compiled_design_system JSONB,
  compiled_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE permission_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  capabilities TEXT[] DEFAULT '{}',
  -- capabilities: ['user:read', 'user:write', 'project:create', etc.]

  is_system_role BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(tenant_id, name)
);

CREATE TABLE permission_policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,

  effect VARCHAR(10) NOT NULL, -- 'allow' or 'deny'
  actions TEXT[] NOT NULL,     -- ['user:read', 'user:write']
  resources TEXT[] NOT NULL,   -- ['user:*', 'project:123']
  conditions JSONB,
  -- conditions: {
  --   "user.department": {"$eq": "engineering"},
  --   "resource.status": {"$in": ["active", "pending"]}
  -- }

  priority INTEGER DEFAULT 0,
  enabled BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE role_assignments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  role_id UUID REFERENCES permission_roles(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,

  scope_type VARCHAR(50), -- 'tenant', 'project', 'resource'
  scope_id UUID,          -- Tenant, project, or resource ID

  assigned_at TIMESTAMP DEFAULT NOW(),
  assigned_by UUID REFERENCES users(id),
  expires_at TIMESTAMP,

  UNIQUE(role_id, user_id, scope_type, scope_id)
);

CREATE TABLE policy_evaluations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,

  user_id UUID REFERENCES users(id),
  action VARCHAR(100) NOT NULL,
  resource_type VARCHAR(100) NOT NULL,
  resource_id UUID,

  result VARCHAR(20) NOT NULL, -- 'allow' or 'deny'
  matched_policies UUID[],     -- Policy IDs that matched
  evaluation_time_ms INTEGER,

  evaluated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_policy_evaluations_user ON policy_evaluations(user_id);
CREATE INDEX idx_policy_evaluations_resource ON policy_evaluations(resource_type, resource_id);

CREATE TABLE permission_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,

  event_type VARCHAR(50) NOT NULL,
  -- Types: role_created, role_assigned, policy_evaluated, access_denied

  actor_id UUID REFERENCES users(id),
  target_user_id UUID REFERENCES users(id),
  target_role_id UUID REFERENCES permission_roles(id),
  target_policy_id UUID REFERENCES permission_policies(id),

  details JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE document_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  name VARCHAR(255) NOT NULL,
  description TEXT,
  template_type VARCHAR(50) NOT NULL, -- 'contract', 'invoice', 'report', etc.
  format VARCHAR(20) NOT NULL,        -- 'docx', 'pdf', 'html', 'markdown'

  file_path VARCHAR(500) NOT NULL,
  file_size INTEGER,

  status VARCHAR(50) DEFAULT 'draft',
  version INTEGER DEFAULT 1,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE template_variables (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id UUID REFERENCES document_templates(id) ON DELETE CASCADE,

  variable_name VARCHAR(255) NOT NULL,
  variable_type VARCHAR(50) NOT NULL, -- 'text', 'number', 'date', 'boolean', 'list'
  description TEXT,

  default_value TEXT,
  is_required BOOLEAN DEFAULT false,
  validation_rules JSONB,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE template_mappings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id UUID REFERENCES document_templates(id) ON DELETE CASCADE,
  variable_id UUID REFERENCES template_variables(id) ON DELETE CASCADE,

  source_type VARCHAR(50) NOT NULL,
  -- Types: 'entity_field', 'api_response', 'workflow_output', 'constant', 'computed'

  source_path VARCHAR(500), -- JSONPath or dot notation
  transform_expression TEXT, -- Optional transform logic

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE template_renders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id UUID REFERENCES document_templates(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  output_format VARCHAR(20) NOT NULL, -- 'pdf', 'html', 'docx', 'email'
  output_path VARCHAR(500),

  variables_used JSONB,
  render_status VARCHAR(50) DEFAULT 'pending',
  error_message TEXT,

  rendered_at TIMESTAMP DEFAULT NOW(),
  rendered_by UUID REFERENCES users(id)
);

CREATE TABLE workflow_definitions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  name VARCHAR(255) NOT NULL,
  description TEXT,

  bpmn_xml TEXT NOT NULL,
  bpmn_file_path VARCHAR(500),

  status VARCHAR(50) DEFAULT 'draft',
  version INTEGER DEFAULT 1,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE workflow_models (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_id UUID REFERENCES workflow_definitions(id) ON DELETE CASCADE,

  normalized_model JSONB NOT NULL,
  -- normalized_model: {
  --   nodes: [{id, type, name, properties}],
  --   edges: [{id, source, target, condition}],
  --   metadata: {}
  -- }

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE workflow_validations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_id UUID REFERENCES workflow_definitions(id) ON DELETE CASCADE,

  validation_level VARCHAR(50) NOT NULL, -- 'structural', 'semantic', 'platform'
  passed BOOLEAN NOT NULL,

  errors JSONB,   -- [{code, message, elementId, severity}]
  warnings JSONB, -- [{code, message, elementId}]

  validated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE workflow_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_id UUID REFERENCES workflow_definitions(id) ON DELETE CASCADE,

  version INTEGER NOT NULL,
  bpmn_xml TEXT NOT NULL,
  change_notes TEXT,

  created_at TIMESTAMP DEFAULT NOW(),
  created_by UUID REFERENCES users(id),

  UNIQUE(workflow_id, version)
);

CREATE TABLE workflow_bindings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_id UUID REFERENCES workflow_definitions(id) ON DELETE CASCADE,

  element_id VARCHAR(255) NOT NULL, -- BPMN element ID
  element_type VARCHAR(50) NOT NULL, -- 'UserTask', 'ServiceTask', etc.

  binding_type VARCHAR(50) NOT NULL, -- 'template', 'integration', 'permission'
  binding_target_id UUID NOT NULL,

  config JSONB, -- Element-specific configuration

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE gap_runs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  run_type VARCHAR(50) NOT NULL, -- 'full', 'incremental'
  detectors_run TEXT[],          -- Detector IDs that ran

  findings_count INTEGER DEFAULT 0,
  tasks_generated INTEGER DEFAULT 0,

  status VARCHAR(50) DEFAULT 'running',
  started_at TIMESTAMP DEFAULT NOW(),
  completed_at TIMESTAMP,
  error_message TEXT
);

CREATE TABLE gap_findings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  run_id UUID REFERENCES gap_runs(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  finding_type VARCHAR(50) NOT NULL, -- 'workflow_gap', 'permission_gap', etc.
  detector_id VARCHAR(100) NOT NULL,

  severity VARCHAR(20) NOT NULL, -- 'blocker', 'warning', 'info'
  title VARCHAR(255) NOT NULL,
  description TEXT,

  artifact_type VARCHAR(50),
  artifact_id UUID,

  resolution_task_id UUID REFERENCES tasks(id),
  resolved BOOLEAN DEFAULT false,
  resolved_at TIMESTAMP,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE standards_artifacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,

  kind VARCHAR(50) NOT NULL, -- 'json-schema', 'openapi', 'asyncapi', 'c4'
  namespace VARCHAR(100) NOT NULL, -- 'core', 'domain', 'project:{id}'
  artifact_id VARCHAR(255) NOT NULL, -- '$id' from schema

  name VARCHAR(255) NOT NULL,
  description TEXT,

  content JSONB NOT NULL,
  format VARCHAR(50) NOT NULL, -- 'json', 'yaml'

  version VARCHAR(50) DEFAULT '1.0.0',
  status VARCHAR(50) DEFAULT 'active', -- 'draft', 'active', 'deprecated'

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),

  UNIQUE(namespace, artifact_id, version)
);

CREATE TABLE standards_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  artifact_id UUID REFERENCES standards_artifacts(id) ON DELETE CASCADE,

  version VARCHAR(50) NOT NULL,
  content JSONB NOT NULL,

  change_summary TEXT,
  breaking_change BOOLEAN DEFAULT false,

  created_at TIMESTAMP DEFAULT NOW(),
  created_by UUID REFERENCES users(id),

  UNIQUE(artifact_id, version)
);

CREATE TABLE standards_references (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  source_artifact_id UUID REFERENCES standards_artifacts(id) ON DELETE CASCADE,
  target_artifact_id UUID REFERENCES standards_artifacts(id) ON DELETE CASCADE,

  reference_type VARCHAR(50) NOT NULL, -- '$ref', 'extends', 'imports'
  reference_path VARCHAR(500), -- JSONPath to reference location

  created_at TIMESTAMP DEFAULT NOW(),

  UNIQUE(source_artifact_id, target_artifact_id, reference_path)
);

CREATE TABLE integration_artifacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  integration_type VARCHAR(50) NOT NULL, -- 'curated', 'contract'
  provider VARCHAR(100) NOT NULL, -- 'stripe', 'gmail', 'custom', etc.

  contract_standard_id UUID REFERENCES standards_artifacts(id),
  -- For contract-first integrations

  config JSONB NOT NULL,
  -- config: {
  --   apiKey: "{{secret}}",
  --   baseUrl: "https://api.example.com",
  --   operations: [...]
  -- }

  status VARCHAR(50) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE integration_mappings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_id UUID REFERENCES integration_artifacts(id) ON DELETE CASCADE,

  operation_id VARCHAR(255) NOT NULL,

  source_schema_id UUID REFERENCES standards_artifacts(id),
  target_schema_id UUID REFERENCES standards_artifacts(id),

  mapping_rules JSONB NOT NULL,
  -- mapping_rules: [{
  --   sourcePath: "user.email",
  --   targetPath: "email",
  --   transform: "toLowerCase()"
  -- }]

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE integration_credentials (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_id UUID REFERENCES integration_artifacts(id) ON DELETE CASCADE,

  credential_type VARCHAR(50) NOT NULL, -- 'api_key', 'oauth', 'jwt'
  encrypted_value TEXT NOT NULL,

  expires_at TIMESTAMP,
  last_used_at TIMESTAMP,

  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE event_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  event_type VARCHAR(100) NOT NULL,
  payload JSONB NOT NULL,

  actor_type VARCHAR(20), -- 'user', 'agent', 'system'
  actor_id UUID,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_event_log_tenant ON event_log(tenant_id);
CREATE INDEX idx_event_log_project ON event_log(project_id);
CREATE INDEX idx_event_log_type ON event_log(event_type);
CREATE INDEX idx_event_log_created ON event_log(created_at);

CREATE TABLE feedback_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id),

  feedback_type VARCHAR(50) NOT NULL,
  -- Types: answer_correction, suggestion_dismissed, rating, comment

  target_type VARCHAR(50), -- 'question', 'prefill', 'suggestion'
  target_id UUID,

  feedback_data JSONB NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_feedback_events_project ON feedback_events(project_id);
CREATE INDEX idx_feedback_events_type ON feedback_events(feedback_type);

CREATE TABLE background_tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,

  task_type VARCHAR(100) NOT NULL,
  -- Types: post_answer_processing, feature_curation, research_task,
  --        spec_compilation, etc.

  payload JSONB NOT NULL,
  priority INTEGER DEFAULT 0,

  status VARCHAR(50) DEFAULT 'pending',
  -- Status: pending, running, completed, failed

  attempts INTEGER DEFAULT 0,
  max_attempts INTEGER DEFAULT 3,

  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  error JSONB,

  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_background_tasks_status ON background_tasks(status, priority);
CREATE INDEX idx_background_tasks_project ON background_tasks(project_id);

CREATE TABLE requirements_discovery_contexts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  tenant_id UUID NOT NULL,

  -- Completion tracking
  is_complete BOOLEAN NOT NULL DEFAULT FALSE,
  completed_at TIMESTAMPTZ,

  -- Purpose & Problem Section
  purpose_and_problem JSONB NOT NULL DEFAULT '{}'::jsonb,

  -- Users & Personas Section
  users_and_personas JSONB NOT NULL DEFAULT '{}'::jsonb,

  -- Success Criteria Section
  success_criteria JSONB NOT NULL DEFAULT '{}'::jsonb,

  -- Constraints Section
  constraints JSONB NOT NULL DEFAULT '{}'::jsonb,

  -- Non-Functional Screening Section
  non_functional_screening JSONB NOT NULL DEFAULT '{}'::jsonb,

  -- Domain-Specific Section (optional)
  domain_specific JSONB,
  domain_type VARCHAR(50),

  -- Metadata
  version INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by TEXT,

  UNIQUE(project_id)
);

CREATE INDEX idx_requirements_discovery_contexts_project
  ON requirements_discovery_contexts(project_id);
CREATE INDEX idx_requirements_discovery_contexts_tenant
  ON requirements_discovery_contexts(tenant_id);
CREATE INDEX idx_requirements_discovery_contexts_complete
  ON requirements_discovery_contexts(project_id, is_complete);

-- GIN indexes for JSONB queries
CREATE INDEX idx_requirements_discovery_purpose_gin
  ON requirements_discovery_contexts USING GIN (purpose_and_problem);
CREATE INDEX idx_requirements_discovery_users_gin
  ON requirements_discovery_contexts USING GIN (users_and_personas);
CREATE INDEX idx_requirements_discovery_success_gin
  ON requirements_discovery_contexts USING GIN (success_criteria);

{
  coreProblem: { value: string, confidence: number },
  triggerEvent: { value: string, confidence?: number },
  currentSolution: { value: string, confidence?: number },
  problemImpact: { value: string, confidence?: number }
}

{
  primaryUser: { value: string, confidence: number },
  secondaryUsers: { value: string[], confidence?: number },
  jobsToBeDone: { value: string[], confidence: number },
  painPoints: { value: string[], confidence: number },
  endUserTechnicalLevel: { value: string, confidence?: number }
}

{
  measuresOfSuccess: { value: string, confidence: number },
  dealbreakers: { value: string[], confidence?: number }
}

{
  timeline: { value: string, confidence: number },
  budget: { value: string, confidence?: number },
  technicalConstraints: { value: string[], confidence?: number },
  existingSystems: { value: string[], confidence?: number }
}

{
  sensitiveData: { value: string, confidence: number },
  expectedScale: { value: string, confidence: number }
}

CREATE TABLE requirements_discovery_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  discovery_context_id UUID NOT NULL REFERENCES requirements_discovery_contexts(id) ON DELETE CASCADE,
  project_id UUID NOT NULL,
  tenant_id UUID NOT NULL,

  -- Message content
  role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'agent')),
  content TEXT NOT NULL,

  -- UI configuration for agent messages
  ui_config JSONB,

  -- Agent metadata (for agent messages)
  extraction_data JSONB,
  question_delivered VARCHAR(255),

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  INDEX idx_requirements_discovery_messages_context (discovery_context_id, created_at),
  INDEX idx_requirements_discovery_messages_project (project_id)
);

CREATE TABLE requirements_discovery_extractions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  discovery_context_id UUID NOT NULL REFERENCES requirements_discovery_contexts(id) ON DELETE CASCADE,
  message_id UUID NOT NULL REFERENCES requirements_discovery_messages(id) ON DELETE CASCADE,
  project_id UUID NOT NULL,
  tenant_id UUID NOT NULL,

  -- Extraction details
  field_path VARCHAR(255) NOT NULL,
  extracted_value JSONB NOT NULL,
  confidence NUMERIC(3,2) NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
  extraction_type VARCHAR(20) NOT NULL CHECK (extraction_type IN ('fact', 'decision', 'inference')),

  -- Source tracking
  source_turn_number INTEGER NOT NULL,
  source_message_snippet TEXT,

  -- Timestamps
  extracted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  INDEX idx_requirements_discovery_extractions_context (discovery_context_id),
  INDEX idx_requirements_discovery_extractions_field (discovery_context_id, field_path)
);

CREATE TABLE feature_seeds (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  tenant_id UUID NOT NULL,

  -- Seed metadata
  seed_type VARCHAR(50) NOT NULL CHECK (seed_type IN ('jtbd_direct', 'pain_solver', 'dealbreaker', 'constraint_driven')),
  feature_name TEXT NOT NULL,
  priority VARCHAR(20) NOT NULL CHECK (priority IN ('critical', 'high', 'medium', 'low')),
  confidence NUMERIC(3,2) NOT NULL DEFAULT 0.8 CHECK (confidence >= 0 AND confidence <= 1),

  -- Source tracking
  source_field VARCHAR(100),
  rationale TEXT NOT NULL,

  -- Discovery context snapshot
  discovery_version INTEGER NOT NULL,
  created_from_discovery_id UUID REFERENCES requirements_discovery_contexts(id) ON DELETE SET NULL,

  -- Metadata
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  INDEX idx_feature_seeds_project (project_id),
  INDEX idx_feature_seeds_priority (project_id, priority)
);

CREATE TABLE feature_curation_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  tenant_id UUID NOT NULL,

  -- Link to discovery context
  discovery_context_id UUID REFERENCES requirements_discovery_contexts(id) ON DELETE SET NULL,

  -- Cached context from discovery
  user_profiles JSONB,
  priority_signals JSONB,
  constraints JSONB,

  -- Session status
  status VARCHAR(20) NOT NULL DEFAULT 'ready' CHECK (status IN ('ready', 'in_progress', 'completed', 'abandoned')),

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,

  INDEX idx_feature_curation_sessions_project (project_id),
  INDEX idx_feature_curation_sessions_discovery (discovery_context_id)
);

CREATE TABLE kb_pattern_feature_mappings (
  pattern_id VARCHAR(100) NOT NULL REFERENCES kb_patterns(id) ON DELETE CASCADE,
  feature_id TEXT NOT NULL REFERENCES feature_catalog(id) ON DELETE CASCADE,
  relevance_score NUMERIC(3,2) NOT NULL DEFAULT 0.90
    CHECK (relevance_score >= 0 AND relevance_score <= 1),
  mapping_source VARCHAR(50) NOT NULL DEFAULT 'ai_generated'
    CHECK (mapping_source IN ('ai_generated', 'manual', 'rule_based')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  PRIMARY KEY (pattern_id, feature_id)
);

CREATE INDEX idx_kb_pfm_feature_id ON kb_pattern_feature_mappings(feature_id);
CREATE INDEX idx_kb_pfm_pattern_id ON kb_pattern_feature_mappings(pattern_id);

ALTER TABLE kb_patterns ADD COLUMN cloud_provider VARCHAR(20) DEFAULT 'agnostic';
CREATE INDEX idx_kb_patterns_cloud_provider ON kb_patterns(cloud_provider);

ALTER TABLE feature_catalog ADD COLUMN related_patterns TEXT[] DEFAULT '{}';
CREATE INDEX idx_feature_catalog_related_patterns
  ON feature_catalog USING GIN(related_patterns);