-- Migration: API Keys Table for Secure Data Access
-- Description: Creates user_api_keys table for secure REST API authentication
-- Date: 2025-10-31
-- Related: Issue #48 - GDPR-compliant webshop sync refactoring

-- ============================================================================
-- STEP 1: Create user_api_keys Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS user_api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  key_name TEXT NOT NULL,
  api_key TEXT NOT NULL UNIQUE,
  key_hash TEXT NOT NULL, -- bcrypt hash for validation (generated in application layer)
  permissions JSONB NOT NULL DEFAULT '{"webshop_data": true}'::jsonb,
  is_active BOOLEAN NOT NULL DEFAULT true,
  last_used_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Constraints
  CONSTRAINT key_name_length CHECK (char_length(key_name) >= 3 AND char_length(key_name) <= 100),
  CONSTRAINT api_key_format CHECK (api_key LIKE 'api_shopcall_%')
);

-- Create indexes for performance
CREATE INDEX idx_user_api_keys_user_id ON user_api_keys(user_id);
CREATE INDEX idx_user_api_keys_api_key ON user_api_keys(api_key);
CREATE INDEX idx_user_api_keys_is_active ON user_api_keys(is_active);
CREATE INDEX idx_user_api_keys_expires_at ON user_api_keys(expires_at) WHERE expires_at IS NOT NULL;

-- ============================================================================
-- STEP 2: Add Updated Timestamp Trigger
-- ============================================================================

CREATE OR REPLACE FUNCTION update_user_api_keys_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_user_api_keys_updated_at
  BEFORE UPDATE ON user_api_keys
  FOR EACH ROW
  EXECUTE FUNCTION update_user_api_keys_timestamp();

-- ============================================================================
-- STEP 3: Enable Row Level Security (RLS)
-- ============================================================================

ALTER TABLE user_api_keys ENABLE ROW LEVEL SECURITY;

-- Policy: Users can view their own API keys
CREATE POLICY "Users can view own API keys"
  ON user_api_keys
  FOR SELECT
  USING (auth.uid() = user_id);

-- Policy: Users can create their own API keys
CREATE POLICY "Users can create own API keys"
  ON user_api_keys
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Policy: Users can update their own API keys
CREATE POLICY "Users can update own API keys"
  ON user_api_keys
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Policy: Users can delete their own API keys
CREATE POLICY "Users can delete own API keys"
  ON user_api_keys
  FOR DELETE
  USING (auth.uid() = user_id);

-- ============================================================================
-- STEP 4: Create Helper Functions
-- ============================================================================

-- Function to check if an API key is valid and active
CREATE OR REPLACE FUNCTION is_api_key_valid(
  p_api_key TEXT
)
RETURNS TABLE (
  valid BOOLEAN,
  user_id UUID,
  permissions JSONB,
  error_message TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    CASE
      WHEN k.id IS NULL THEN false
      WHEN NOT k.is_active THEN false
      WHEN k.expires_at IS NOT NULL AND k.expires_at < NOW() THEN false
      ELSE true
    END AS valid,
    k.user_id,
    k.permissions,
    CASE
      WHEN k.id IS NULL THEN 'API key not found'
      WHEN NOT k.is_active THEN 'API key is inactive'
      WHEN k.expires_at IS NOT NULL AND k.expires_at < NOW() THEN 'API key has expired'
      ELSE NULL
    END AS error_message
  FROM user_api_keys k
  WHERE k.api_key = p_api_key
  LIMIT 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to update last_used_at timestamp
CREATE OR REPLACE FUNCTION update_api_key_last_used(
  p_api_key TEXT
)
RETURNS void AS $$
BEGIN
  UPDATE user_api_keys
  SET last_used_at = NOW()
  WHERE api_key = p_api_key AND is_active = true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to revoke an API key
CREATE OR REPLACE FUNCTION revoke_api_key(
  p_key_id UUID,
  p_user_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
  rows_affected INTEGER;
BEGIN
  UPDATE user_api_keys
  SET is_active = false, updated_at = NOW()
  WHERE id = p_key_id AND user_id = p_user_id;

  GET DIAGNOSTICS rows_affected = ROW_COUNT;
  RETURN rows_affected > 0;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to clean up expired API keys
CREATE OR REPLACE FUNCTION cleanup_expired_api_keys()
RETURNS INTEGER AS $$
DECLARE
  rows_deleted INTEGER;
BEGIN
  DELETE FROM user_api_keys
  WHERE expires_at IS NOT NULL AND expires_at < NOW() - INTERVAL '30 days';

  GET DIAGNOSTICS rows_deleted = ROW_COUNT;
  RETURN rows_deleted;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- STEP 5: Comments for Documentation
-- ============================================================================

COMMENT ON TABLE user_api_keys IS 'Stores API keys for secure access to webshop data endpoints';
COMMENT ON COLUMN user_api_keys.api_key IS 'Plain API key (format: api_shopcall_xxxxx)';
COMMENT ON COLUMN user_api_keys.key_hash IS 'Bcrypt hash of API key for validation';
COMMENT ON COLUMN user_api_keys.permissions IS 'JSON object defining what the API key can access';
COMMENT ON COLUMN user_api_keys.last_used_at IS 'Timestamp of last successful API key usage';
COMMENT ON COLUMN user_api_keys.expires_at IS 'Optional expiration date for the API key';

-- ============================================================================
-- STEP 6: Grant Necessary Permissions
-- ============================================================================

-- Grant usage on functions to authenticated users
GRANT EXECUTE ON FUNCTION is_api_key_valid(TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION update_api_key_last_used(TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION revoke_api_key(UUID, UUID) TO authenticated;

-- Service role can execute cleanup function
GRANT EXECUTE ON FUNCTION cleanup_expired_api_keys() TO service_role;
