-- V012: Create voice feedback table for storing user corrections and approvals
-- This helps improve AI accuracy by learning from user edits

CREATE TABLE IF NOT EXISTS voice_feedback (
  id VARCHAR(20) PRIMARY KEY,
  user_id VARCHAR(20) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  child_id VARCHAR(20) REFERENCES children(id) ON DELETE CASCADE,
  activity_id VARCHAR(20) REFERENCES activities(id) ON DELETE CASCADE,

  -- Original voice input
  transcript TEXT NOT NULL,
  language VARCHAR(10),

  -- AI extracted data (before user edits)
  extracted_type VARCHAR(20) NOT NULL,
  extracted_data JSONB NOT NULL DEFAULT '{}',
  confidence DECIMAL(3,2),

  -- User actions
  action VARCHAR(20) NOT NULL CHECK (action IN ('approved', 'edited', 'rejected')),

  -- Final data (after user edits, if edited)
  final_type VARCHAR(20),
  final_data JSONB,

  -- User feedback
  user_notes TEXT,

  -- Metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for analytics
CREATE INDEX idx_voice_feedback_user ON voice_feedback(user_id, created_at DESC);
CREATE INDEX idx_voice_feedback_type ON voice_feedback(extracted_type, action);
CREATE INDEX idx_voice_feedback_confidence ON voice_feedback(confidence, action);
CREATE INDEX idx_voice_feedback_activity ON voice_feedback(activity_id);

-- Add comment
COMMENT ON TABLE voice_feedback IS 'Stores user feedback on voice command accuracy for ML improvement';
COMMENT ON COLUMN voice_feedback.action IS 'User action: approved (no changes), edited (modified data), rejected (discarded)';
COMMENT ON COLUMN voice_feedback.extracted_data IS 'Original AI-extracted activity data before user edits';
COMMENT ON COLUMN voice_feedback.final_data IS 'User-modified data if action=edited';
