-- V007: Create Notifications Table
-- Created: 2025-10-01
-- Purpose: Store persistent notifications with status tracking for smart alerts

CREATE TYPE notification_type AS ENUM (
  'feeding_reminder',
  'sleep_reminder',
  'diaper_reminder',
  'medication_reminder',
  'milestone_alert',
  'growth_tracking',
  'appointment_reminder',
  'pattern_anomaly'
);

CREATE TYPE notification_status AS ENUM (
  'pending',
  'sent',
  'read',
  'dismissed',
  'failed'
);

CREATE TYPE notification_priority AS ENUM (
  'low',
  'medium',
  'high',
  'urgent'
);

CREATE TABLE notifications (
  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,
  type notification_type NOT NULL,
  status notification_status NOT NULL DEFAULT 'pending',
  priority notification_priority NOT NULL DEFAULT 'medium',
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  metadata JSONB,
  scheduled_for TIMESTAMP,
  sent_at TIMESTAMP,
  read_at TIMESTAMP,
  dismissed_at TIMESTAMP,
  device_token VARCHAR(255),
  error_message TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for efficient queries
CREATE INDEX idx_notifications_user_status ON notifications(user_id, status, created_at);
CREATE INDEX idx_notifications_child_type ON notifications(child_id, type);
CREATE INDEX idx_notifications_scheduled ON notifications(scheduled_for) WHERE scheduled_for IS NOT NULL;
CREATE INDEX idx_notifications_status ON notifications(status);

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

CREATE TRIGGER trigger_update_notifications_updated_at
  BEFORE UPDATE ON notifications
  FOR EACH ROW
  EXECUTE FUNCTION update_notifications_updated_at();

-- Comments
COMMENT ON TABLE notifications IS 'Stores persistent notifications for users with status tracking';
COMMENT ON COLUMN notifications.id IS 'Unique notification ID (ntf_xxxxx)';
COMMENT ON COLUMN notifications.user_id IS 'User who receives the notification';
COMMENT ON COLUMN notifications.child_id IS 'Child related to the notification (optional)';
COMMENT ON COLUMN notifications.type IS 'Type of notification (feeding, sleep, milestone, etc.)';
COMMENT ON COLUMN notifications.status IS 'Current status (pending, sent, read, dismissed, failed)';
COMMENT ON COLUMN notifications.priority IS 'Priority level (low, medium, high, urgent)';
COMMENT ON COLUMN notifications.metadata IS 'Additional notification data (estimatedTime, reason, etc.)';
COMMENT ON COLUMN notifications.scheduled_for IS 'When the notification should be sent (for scheduled notifications)';
COMMENT ON COLUMN notifications.device_token IS 'Device token for push notifications';
COMMENT ON COLUMN notifications.error_message IS 'Error message if notification failed to send';
