import { createClient, SupabaseClient } from '@supabase/supabase-js';

type EnvResolution = {
  value: string | null;
  envName: string | null;
  candidates: string[];
};

type DbConnectionMode = 'dedicated' | 'shared_fallback' | 'inactive';

type ResolvedDbConnection = {
  url: string | null;
  key: string | null;
  urlEnv: string | null;
  keyEnv: string | null;
  urlCandidates: string[];
  keyCandidates: string[];
  mode: DbConnectionMode;
};

type RedactedDbConnection = Omit<ResolvedDbConnection, 'url' | 'key'> & {
  urlPresent: boolean;
  keyPresent: boolean;
};

type ResolvedGlobalDbConnection = Omit<ResolvedDbConnection, 'mode'> & { mode: 'shared' | 'inactive' };
type RedactedGlobalDbConnection = Omit<RedactedDbConnection, 'mode'> & { mode: 'shared' | 'inactive' };

export type DualDbRoutingStatus = {
  global: RedactedGlobalDbConnection;
  dedicated: RedactedDbConnection;
  memoryPlane: {
    schema: 'memory_plane';
    table: 'items';
  };
  docsSyncQueue: {
    table: 'docs_sync_queue';
  };
};

export type MemoryPlaneInsertInput = {
  content: string;
  summary?: string;
  metadata?: Record<string, unknown>;
  memoryType?: 'semantic' | 'episodic';
  provenanceType?: string;
  sourceRunId?: string;
  confidence?: number;
};

export type DocsSyncQueueInsertInput = {
  targetAgent: string;
  docType: 'connector' | 'framework' | 'repo_internal';
  docSourceUrl: string;
  metadata?: Record<string, unknown>;
  requestedBy?: string;
};

const GLOBAL_URL_ENV_CANDIDATES = ['SIN_SUPABASE_URL', 'SUPABASE_URL'];
const GLOBAL_KEY_ENV_CANDIDATES = ['SIN_SUPABASE_SERVICE_ROLE_KEY', 'SUPABASE_SERVICE_ROLE_KEY'];
const DEDICATED_URL_ENV_CANDIDATES = [
  'SUPABASE_FRONTEND_URL',
  'SUPABASE_CODER_URL',
  'SIN_FRONTEND_DEDICATED_SUPABASE_URL',
  'SIN_FRONTEND_SUPABASE_URL',
];
const DEDICATED_KEY_ENV_CANDIDATES = [
  'SUPABASE_FRONTEND_KEY',
  'SUPABASE_CODER_KEY',
  'SIN_FRONTEND_DEDICATED_SUPABASE_KEY',
  'SIN_FRONTEND_SUPABASE_KEY',
];

let globalSupabaseInstance: SupabaseClient | null = null;
let dedicatedSupabaseInstance: SupabaseClient | null = null;

function resolveEnv(candidates: string[]): EnvResolution {
  for (const candidate of candidates) {
    const value = process.env[candidate]?.trim();
    if (value) {
      return { value, envName: candidate, candidates };
    }
  }

  return { value: null, envName: null, candidates };
}

function resolveGlobalDbConnection(): ResolvedGlobalDbConnection {
  const url = resolveEnv(GLOBAL_URL_ENV_CANDIDATES);
  const key = resolveEnv(GLOBAL_KEY_ENV_CANDIDATES);

  return {
    url: url.value,
    key: key.value,
    urlEnv: url.envName,
    keyEnv: key.envName,
    urlCandidates: url.candidates,
    keyCandidates: key.candidates,
    mode: url.value && key.value ? 'shared' : 'inactive',
  };
}

function resolveDedicatedDbConnection(): ResolvedDbConnection {
  const dedicatedUrl = resolveEnv(DEDICATED_URL_ENV_CANDIDATES);
  const dedicatedKey = resolveEnv(DEDICATED_KEY_ENV_CANDIDATES);

  if (dedicatedUrl.value && dedicatedKey.value) {
    return {
      url: dedicatedUrl.value,
      key: dedicatedKey.value,
      urlEnv: dedicatedUrl.envName,
      keyEnv: dedicatedKey.envName,
      urlCandidates: dedicatedUrl.candidates,
      keyCandidates: dedicatedKey.candidates,
      mode: 'dedicated',
    };
  }

  const global = resolveGlobalDbConnection();
  if (global.url && global.key) {
    return {
      url: global.url,
      key: global.key,
      urlEnv: global.urlEnv,
      keyEnv: global.keyEnv,
      urlCandidates: dedicatedUrl.candidates,
      keyCandidates: dedicatedKey.candidates,
      mode: 'shared_fallback',
    };
  }

  return {
    url: null,
    key: null,
    urlEnv: null,
    keyEnv: null,
    urlCandidates: dedicatedUrl.candidates,
    keyCandidates: dedicatedKey.candidates,
    mode: 'inactive',
  };
}

function getOrCreateClient(existing: SupabaseClient | null, connection: { url: string | null; key: string | null }) {
  if (existing) return existing;
  if (!connection.url || !connection.key) {
    throw new Error('missing_supabase_credentials');
  }
  return createClient(connection.url, connection.key, { auth: { persistSession: false } });
}

export function getGlobalSupabaseClient(): SupabaseClient {
  const connection = resolveGlobalDbConnection();
  globalSupabaseInstance = getOrCreateClient(globalSupabaseInstance, connection);
  return globalSupabaseInstance;
}

export function getDedicatedSupabaseClient(): SupabaseClient {
  const connection = resolveDedicatedDbConnection();
  dedicatedSupabaseInstance = getOrCreateClient(dedicatedSupabaseInstance, connection);
  return dedicatedSupabaseInstance;
}

export function getFrontendDualDbRoutingStatus(): DualDbRoutingStatus {
  const global = resolveGlobalDbConnection();
  const dedicated = resolveDedicatedDbConnection();
  return {
    global: redactGlobalConnection(global),
    dedicated: redactConnection(dedicated),
    memoryPlane: {
      schema: 'memory_plane',
      table: 'items',
    },
    docsSyncQueue: {
      table: 'docs_sync_queue',
    },
  };
}

function redactGlobalConnection(connection: ResolvedGlobalDbConnection): RedactedGlobalDbConnection {
  return {
    urlPresent: Boolean(connection.url),
    keyPresent: Boolean(connection.key),
    urlEnv: connection.urlEnv,
    keyEnv: connection.keyEnv,
    urlCandidates: connection.urlCandidates,
    keyCandidates: connection.keyCandidates,
    mode: connection.mode,
  };
}

function redactConnection(connection: ResolvedDbConnection): RedactedDbConnection {
  return {
    urlPresent: Boolean(connection.url),
    keyPresent: Boolean(connection.key),
    urlEnv: connection.urlEnv,
    keyEnv: connection.keyEnv,
    urlCandidates: connection.urlCandidates,
    keyCandidates: connection.keyCandidates,
    mode: connection.mode,
  };
}

export async function insertDedicatedMemoryItem(input: MemoryPlaneInsertInput) {
  const connection = resolveDedicatedDbConnection();
  const rows = await runPgQuery(
    connection,
    `insert into memory_plane.items (memory_type, provenance_type, source_run_id, content, summary, metadata, confidence)
     values (
       ${sqlString(input.memoryType || 'episodic')},
       ${sqlString(input.provenanceType || 'agent_scratchpad')},
       ${sqlNullableString(input.sourceRunId)},
       ${sqlString(input.content)},
       ${sqlNullableString(input.summary)},
       ${sqlJson(input.metadata || {})}::jsonb,
       ${sqlNullableNumber(input.confidence)}
     )
     returning id, memory_type, provenance_type, source_run_id, content, summary, metadata, created_at`,
  );
  const item = Array.isArray(rows) ? rows[0] : null;
  if (!item) {
    throw new Error('memory_plane_insert_failed:no_row_returned');
  }

  return {
    ok: true,
    routing: getFrontendDualDbRoutingStatus().dedicated,
    item,
  };
}

export async function enqueueDocsSyncRequest(input: DocsSyncQueueInsertInput) {
  const client = getGlobalSupabaseClient();
  const { data, error } = await client
    .from('docs_sync_queue')
    .insert({
      target_agent: input.targetAgent,
      doc_type: input.docType,
      doc_source_url: input.docSourceUrl,
      metadata: input.metadata || {},
      status: 'pending',
      requested_by: input.requestedBy || 'sin-frontend',
    })
    .select('id,target_agent,doc_type,doc_source_url,status,requested_by,created_at')
    .single();

  if (error) {
    throw new Error(`docs_sync_queue_insert_failed:${error.message}`);
  }

  return {
    ok: true,
    routing: getFrontendDualDbRoutingStatus().global,
    queue: data,
  };
}

export async function listRecentDedicatedMemoryItems(limit = 5) {
  const connection = resolveDedicatedDbConnection();
  const rows = await runPgQuery(
    connection,
    `select id, memory_type, provenance_type, source_run_id, content, metadata, created_at
     from memory_plane.items
     order by created_at desc
     limit ${Math.min(Math.max(limit, 1), 20)}`,
  );
  return Array.isArray(rows) ? rows : [];
}

export async function listRecentDocsSyncQueueRows(targetAgent = 'sin-frontend', limit = 5) {
  const client = getGlobalSupabaseClient();
  const { data, error } = await client
    .from('docs_sync_queue')
    .select('id,target_agent,doc_type,doc_source_url,status,requested_by,created_at,error_message')
    .eq('target_agent', targetAgent)
    .order('created_at', { ascending: false })
    .limit(Math.min(Math.max(limit, 1), 20));

  if (error) {
    throw new Error(`docs_sync_queue_select_failed:${error.message}`);
  }

  return Array.isArray(data) ? data : [];
}

async function runPgQuery(connection: { url: string | null; key: string | null }, sql: string) {
  if (!connection.url || !connection.key) {
    throw new Error('missing_pg_query_credentials');
  }

  const response = await fetch(`${connection.url.replace(/\/+$/, '')}/pg/query`, {
    method: 'POST',
    headers: {
      apikey: connection.key,
      Authorization: `Bearer ${connection.key}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ query: sql }),
  });

  const payload = await response.json().catch(() => null);
  if (!response.ok) {
    throw new Error(`pg_query_failed:${response.status}:${JSON.stringify(payload ?? {})}`);
  }

  return Array.isArray(payload) ? payload : [];
}

function sqlString(value: string) {
  return `'${String(value).replace(/'/g, "''")}'`;
}

function sqlNullableString(value: string | undefined) {
  return value ? sqlString(value) : 'null';
}

function sqlNullableNumber(value: number | undefined) {
  return typeof value === 'number' && Number.isFinite(value) ? String(value) : 'null';
}

function sqlJson(value: unknown) {
  return sqlString(JSON.stringify(value ?? {}));
}