-- Migration: Internal API Keys Table for System-Level Access
-- Description: Creates internal_api_keys table for non-user-based API authentication
-- Date: 2025-10-31
-- Related: Issue #50 - Implement non user based API keys

-- ============================================================================
-- STEP 1: Create internal_api_keys Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS internal_api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  key_name TEXT NOT NULL,
  api_key TEXT NOT NULL UNIQUE,
  key_hash TEXT NOT NULL, -- bcrypt hash for validation
  description TEXT,
  permissions JSONB NOT NULL DEFAULT '{"all_webshops": true, "read_products": true, "read_orders": true, "read_customers": 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(),
  created_by TEXT, -- optional: track who created this key (e.g., "admin", "system")

  -- Constraints
  CONSTRAINT internal_key_name_length CHECK (char_length(key_name) >= 3 AND char_length(key_name) <= 100),
  CONSTRAINT internal_api_key_format CHECK (api_key LIKE 'int_shopcall_%')
);

-- Create indexes for performance
CREATE INDEX idx_internal_api_keys_api_key ON internal_api_keys(api_key);
CREATE INDEX idx_internal_api_keys_is_active ON internal_api_keys(is_active);
CREATE INDEX idx_internal_api_keys_expires_at ON internal_api_keys(expires_at) WHERE expires_at IS NOT NULL;

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

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

CREATE TRIGGER trigger_internal_api_keys_updated_at
  BEFORE UPDATE ON internal_api_keys
  FOR EACH ROW
  EXECUTE FUNCTION update_internal_api_keys_timestamp();

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

ALTER TABLE internal_api_keys ENABLE ROW LEVEL SECURITY;

-- Policy: Service role can do anything (for internal management)
CREATE POLICY "Service role full access to internal API keys"
  ON internal_api_keys
  FOR ALL
  USING (auth.jwt() ->> 'role' = 'service_role');

-- Policy: No regular user access (internal only)
-- This ensures internal_api_keys are NOT visible in the UI

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

-- Function to check if an internal API key is valid and active
CREATE OR REPLACE FUNCTION is_internal_api_key_valid(
  p_api_key TEXT
)
RETURNS TABLE (
  valid BOOLEAN,
  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.permissions,
    CASE
      WHEN k.id IS NULL THEN 'Internal API key not found'
      WHEN NOT k.is_active THEN 'Internal API key is inactive'
      WHEN k.expires_at IS NOT NULL AND k.expires_at < NOW() THEN 'Internal API key has expired'
      ELSE NULL
    END AS error_message
  FROM internal_api_keys k
  WHERE k.api_key = p_api_key
  LIMIT 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to update last_used_at timestamp for internal keys
CREATE OR REPLACE FUNCTION update_internal_api_key_last_used(
  p_api_key TEXT
)
RETURNS void AS $$
BEGIN
  UPDATE internal_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 internal API key (service role only)
CREATE OR REPLACE FUNCTION revoke_internal_api_key(
  p_key_id UUID
)
RETURNS BOOLEAN AS $$
DECLARE
  rows_affected INTEGER;
BEGIN
  UPDATE internal_api_keys
  SET is_active = false, updated_at = NOW()
  WHERE id = p_key_id;

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

-- Function to clean up expired internal API keys
CREATE OR REPLACE FUNCTION cleanup_expired_internal_api_keys()
RETURNS INTEGER AS $$
DECLARE
  rows_deleted INTEGER;
BEGIN
  DELETE FROM internal_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 internal_api_keys IS 'Stores internal/system API keys for non-user-based access to all webshop data. Not visible in UI.';
COMMENT ON COLUMN internal_api_keys.api_key IS 'Plain internal API key (format: int_shopcall_xxxxx)';
COMMENT ON COLUMN internal_api_keys.key_hash IS 'Bcrypt hash of internal API key for validation';
COMMENT ON COLUMN internal_api_keys.permissions IS 'JSON object defining what the internal API key can access (typically all_webshops: true)';
COMMENT ON COLUMN internal_api_keys.last_used_at IS 'Timestamp of last successful internal API key usage';
COMMENT ON COLUMN internal_api_keys.expires_at IS 'Optional expiration date for the internal API key';
COMMENT ON COLUMN internal_api_keys.created_by IS 'Optional field to track who created this internal key';

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

-- Grant usage on functions to service role (internal management)
GRANT EXECUTE ON FUNCTION is_internal_api_key_valid(TEXT) TO service_role;
GRANT EXECUTE ON FUNCTION update_internal_api_key_last_used(TEXT) TO service_role;
GRANT EXECUTE ON FUNCTION revoke_internal_api_key(UUID) TO service_role;
GRANT EXECUTE ON FUNCTION cleanup_expired_internal_api_keys() TO service_role;

-- Also grant to anon for validation during API requests
GRANT EXECUTE ON FUNCTION is_internal_api_key_valid(TEXT) TO anon;
GRANT EXECUTE ON FUNCTION update_internal_api_key_last_used(TEXT) TO anon;
