-- V008: Create Photos Table
-- Created: 2025-10-01
-- Purpose: Store photo attachments for activities and milestones

CREATE TYPE photo_type AS ENUM (
  'milestone',
  'activity',
  'profile',
  'general'
);

CREATE TABLE photos (
  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 SET NULL,
  type photo_type NOT NULL DEFAULT 'general',
  original_filename VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size INTEGER NOT NULL,
  storage_key VARCHAR(255) NOT NULL UNIQUE,
  thumbnail_key VARCHAR(255),
  width INTEGER,
  height INTEGER,
  caption VARCHAR(255),
  description TEXT,
  taken_at TIMESTAMP,
  metadata JSONB,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for efficient queries
CREATE INDEX idx_photos_child_created ON photos(child_id, created_at DESC);
CREATE INDEX idx_photos_activity ON photos(activity_id);
CREATE INDEX idx_photos_user ON photos(user_id);
CREATE INDEX idx_photos_type ON photos(type);
CREATE INDEX idx_photos_taken_at ON photos(taken_at) WHERE taken_at IS NOT NULL;

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_photos_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_photos_updated_at
  BEFORE UPDATE ON photos
  FOR EACH ROW
  EXECUTE FUNCTION update_photos_updated_at();

-- Comments
COMMENT ON TABLE photos IS 'Stores photo attachments for activities, milestones, and profiles';
COMMENT ON COLUMN photos.id IS 'Unique photo ID (pho_xxxxx)';
COMMENT ON COLUMN photos.user_id IS 'User who uploaded the photo';
COMMENT ON COLUMN photos.child_id IS 'Child associated with the photo (optional)';
COMMENT ON COLUMN photos.activity_id IS 'Activity associated with the photo (optional)';
COMMENT ON COLUMN photos.type IS 'Photo type (milestone, activity, profile, general)';
COMMENT ON COLUMN photos.storage_key IS 'S3/MinIO storage key for the original photo';
COMMENT ON COLUMN photos.thumbnail_key IS 'S3/MinIO storage key for the thumbnail';
COMMENT ON COLUMN photos.metadata IS 'Additional metadata (location, tags, milestone type, etc.)';
COMMENT ON COLUMN photos.taken_at IS 'When the photo was taken (may differ from uploaded date)';
