-- V015: Create deletion_requests table for GDPR-compliant account deletion
-- This table tracks account deletion requests with a 30-day grace period

CREATE TABLE deletion_requests (
  id VARCHAR(20) PRIMARY KEY,
  user_id VARCHAR(20) NOT NULL REFERENCES users(id) ON DELETE CASCADE,

  -- Deletion request details
  requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  scheduled_deletion_at TIMESTAMP NOT NULL, -- 30 days from requested_at
  reason TEXT,

  -- Cancellation tracking
  cancelled_at TIMESTAMP,
  cancelled_by VARCHAR(20) REFERENCES users(id),
  cancellation_reason TEXT,

  -- Completion tracking
  completed_at TIMESTAMP,
  status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'cancelled', 'completed')),

  -- Audit fields
  ip_address VARCHAR(45),
  user_agent TEXT,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for efficient querying
CREATE INDEX idx_deletion_requests_user_id ON deletion_requests(user_id);
CREATE INDEX idx_deletion_requests_status ON deletion_requests(status);
CREATE INDEX idx_deletion_requests_scheduled_deletion_at ON deletion_requests(scheduled_deletion_at) WHERE status = 'pending';

-- Only one active deletion request per user
CREATE UNIQUE INDEX idx_deletion_requests_active_user ON deletion_requests(user_id) WHERE status = 'pending';

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

CREATE TRIGGER trigger_deletion_requests_updated_at
  BEFORE UPDATE ON deletion_requests
  FOR EACH ROW
  EXECUTE FUNCTION update_deletion_requests_updated_at();

-- Comments
COMMENT ON TABLE deletion_requests IS 'Tracks user account deletion requests with 30-day grace period (GDPR compliance)';
COMMENT ON COLUMN deletion_requests.scheduled_deletion_at IS 'Date when account will be permanently deleted (30 days from request)';
COMMENT ON COLUMN deletion_requests.status IS 'pending: awaiting deletion, cancelled: user cancelled, completed: deletion executed';
