[DM-05] Surveys table (configurable questionnaires) (retry 1)

{
  "cost": {
    "input_tokens": 294,
    "estimated_usd": 0.027522,
    "output_tokens": 1776
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 24656,
  "response_text": "I'll create the surveys table migration file to satisfy the requirements for configurable questionnaires.\n\n```sql\n-- EN: Create surveys table for configurable questionnaires\n-- VI: Tạo bảng surveys cho các bảng hỏi có thể cấu hình\n-- ES: Crear tabla de encuestas para cuestionarios configurables\n\n-- SPEC: DM-05 - Surveys table (configurable questionnaires)\n\nCREATE TABLE surveys (\n  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,\n  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,\n  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,\n  \n  -- EN: Reference to the campaign this survey belongs to\n  -- VI: Tham chiếu đến chiến dịch mà khảo sát này thuộc về\n  -- ES: Referencia a la campaña a la que pertenece esta encuesta\n  campaign_id UUID NOT NULL,\n  \n  -- EN: Survey title and description\n  -- VI: Tiêu đề và mô tả khảo sát\n  -- ES: Título y descripción de la encuesta\n  title VARCHAR(255) NOT NULL,\n  description TEXT,\n  \n  -- EN: JSON structure containing survey questions configuration\n  -- VI: Cấu trúc JSON chứa cấu hình câu hỏi khảo sát\n  -- ES: Estructura JSON que contiene la configuración de preguntas de la encuesta\n  questions_json JSONB NOT NULL DEFAULT '[]'::jsonb,\n  \n  -- EN: Survey status - whether it's currently active or not\n  -- VI: Trạng thái khảo sát - có đang hoạt động hay không\n  -- ES: Estado de la encuesta - si está actualmente activa o no\n  is_active BOOLEAN NOT NULL DEFAULT true,\n  \n  -- EN: Survey scheduling\n  -- VI: Lịch trình khảo sát\n  -- ES: Programación de encuestas\n  start_date TIMESTAMP WITH TIME ZONE,\n  end_date TIMESTAMP WITH TIME ZONE,\n  \n  -- EN: Survey configuration options\n  -- VI: Tùy chọn cấu hình khảo sát\n  -- ES: Opciones de configuración de encuesta\n  allow_anonymous BOOLEAN NOT NULL DEFAULT false,\n  max_responses INTEGER,\n  response_count INTEGER NOT NULL DEFAULT 0,\n  \n  CONSTRAINT surveys_campaign_id_fkey \n    FOREIGN KEY (campaign_id) \n    REFERENCES campaigns(id) \n    ON DELETE CASCADE,\n    \n  CONSTRAINT surveys_valid_dates \n    CHECK (end_date IS NULL OR start_date IS NULL OR end_date > start_date),\n    \n  CONSTRAINT surveys_max_responses_positive \n    CHECK (max_responses IS NULL OR max_responses > 0),\n    \n  CONSTRAINT surveys_response_count_non_negative \n    CHECK (response_count >= 0)\n);\n\n-- EN: Create indexes for efficient querying\n-- VI: Tạo chỉ mục để truy vấn hiệu quả\n-- ES: Crear índices para consultas eficientes\n\nCREATE INDEX idx_surveys_campaign_id ON surveys(campaign_id);\nCREATE INDEX idx_surveys_is_active ON surveys(is_active);\nCREATE INDEX idx_surveys_dates ON surveys(start_date, end_date);\nCREATE INDEX idx_surveys_created_at ON surveys(created_at);\n\n-- EN: Create updated_at trigger\n-- VI: Tạo trigger updated_at\n-- ES: Crear trigger updated_at\n\nCREATE OR REPLACE FUNCTION update_surveys_updated_at()\nRETURNS TRIGGER AS $$\nBEGIN\n  NEW.updated_at = NOW();\n  RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\nCREATE TRIGGER trigger_surveys_updated_at\n  BEFORE UPDATE ON surveys\n  FOR EACH ROW\n  EXECUTE FUNCTION update_surveys_updated_at();\n\n-- EN: Enable Row Level Security\n-- VI: Bật bảo mật cấp hàng\n-- ES: Habilitar seguridad a nivel de fila\n\nALTER TABLE surveys ENABLE ROW LEVEL SECURITY;\n\n-- EN: Create RLS policies for surveys\n-- VI: Tạo chính sách RLS cho surveys\n-- ES: Crear políticas RLS para encuestas\n\nCREATE POLICY \"Users can view surveys from their campaigns\"\n  ON surveys FOR SELECT\n  USING (\n    campaign_id IN (\n      SELECT id FROM campaigns \n      WHERE user_id = auth.uid()\n    )\n  );\n\nCREATE POLICY \"Users can insert surveys for their campaigns\"\n  ON surveys FOR INSERT\n  WITH CHECK (\n    campaign_id IN (\n      SELECT id FROM campaigns \n      WHERE user_id = auth.uid()\n    )\n  );\n\nCREATE POLICY \"Users can update surveys from their campaigns\"\n  ON surveys FOR UPDATE\n  USING (\n    campaign_id IN (\n      SELECT id FROM campaigns \n      WHERE user_id = auth.uid()\n    )\n  )\n  WITH CHECK (\n    campaign_id IN (\n      SELECT id FROM campaigns \n      WHERE user_id = auth.uid()\n    )\n  );\n\nCREATE POLICY \"Users can delete surveys from their campaigns\"\n  ON surveys FOR DELETE\n  USING (\n    campaign_id IN (\n      SELECT id FROM campaigns \n      WHERE user_id = auth.uid()\n    )\n  );\n\n-- EN: Create view for active surveys with campaign info\n-- VI: Tạo view cho các khảo sát đang hoạt động với thông tin chiến dịch\n-- ES: Crear vista para encuestas activas con información de campaña\n\nCREATE OR REPLACE VIEW active_surveys AS\nSELECT \n  s.*,\n  c.name as campaign_name,\n  c.user_id as campaign_user_id\nFROM surveys s\nJOIN campaigns c ON s.campaign_id = c.id\nWHERE s.is_active = true\n  AND (s.start_date IS NULL OR s.start_date <= NOW())\n  AND (s.end_date IS NULL OR s.end_date > NOW())\n  AND (s.max_responses IS NULL OR s.response_count < s.max_responses);\n\n-- EN: Grant permissions\n-- VI: Cấp quyền\n-- ES: Otorgar permisos\n\nGRANT SELECT, INSERT, UPDATE, DELETE ON surveys TO authenticated;\nGRANT SELECT ON active_surveys TO authenticated;\n```\n\nThis migration creates a comprehensive surveys table that:\n\n1. **Satisfies the failing tests** by including:\n   - `CREATE TABLE surveys` statement\n   - Required fields: `campaign_id`, `questions_json`, and `is_active`\n\n2. **Follows codebase conventions** with:\n   - Trilingual comments (EN/VI/ES)\n   - SPEC section reference in comments\n   - Proper SQL structure\n\n3. **Provides full functionality** including:\n   - Foreign key relationship to campaigns table\n   - JSON storage for configurable questions\n   - Survey scheduling with start/end dates\n   - Response tracking and limits\n   - Anonymous response option\n   - Proper indexing for performance\n   - Row Level Security policies\n   - Helpful view for active surveys\n\nThe `questions_json` field uses JSONB for efficient storage and querying of survey question configurations, allowing for flexible questionnaire structures."
}