"""Libpq service connections; credentials never belong in CLI arguments."""
from collections.abc import Mapping
import re

SEARCH_VECTOR = "to_tsvector('english'::regconfig,coalesce(title,'') || ' ' || coalesce(abstract,''))"


def driver():
    try:
        import psycopg
    except ImportError as error:
        raise RuntimeError('Install paper-atlas[postgres] on the approved remote runner') from error
    return psycopg


def connect(service, *, readonly=False):
    if not re.fullmatch(r'[A-Za-z0-9_-]+', service):
        raise ValueError('Use a libpq service name, never a password or connection URL')
    connection = driver().connect(service=service, connect_timeout=15, autocommit=True,
                                  application_name='paper-atlas')
    connection.execute("SET timezone='UTC'")
    connection.execute("SET lock_timeout='5s'")
    connection.execute("SET work_mem='2MB'")
    connection.execute("SET maintenance_work_mem='16MB'")
    connection.execute('SET max_parallel_workers_per_gather=0')
    connection.execute('SET max_parallel_maintenance_workers=0')
    if readonly:
        connection.execute('BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY')
    return connection


class Row(Mapping):
    """The small mapping/positional interface used by existing query output."""
    def __init__(self, columns, values):
        self.columns, self.values = columns, values

    def __getitem__(self, key):
        return self.values[key if isinstance(key, int) else self.columns.index(key)]

    def __iter__(self):
        return iter(self.columns)

    def __len__(self):
        return len(self.columns)


def row_factory(cursor):
    columns = [column.name for column in cursor.description] if cursor.description else []
    return lambda values: Row(columns, values)


class ReadConnection:
    postgres = True

    def __init__(self, service, schema=None):
        if schema is not None and not re.fullmatch(r'[a-z][a-z0-9_]{0,35}', schema):
            raise ValueError('Invalid schema name')
        self.connection = connect(service, readonly=True)
        from paper_atlas.postgres.schema import quote
        if schema is not None:
            self.connection.execute('SET LOCAL search_path TO ' + quote(schema) + ',pg_catalog')
        self.connection.row_factory = row_factory
        if not self.has_table('papers'):
            self.close()
            raise RuntimeError('PostgreSQL publication is not ready in the configured search_path')

    def execute(self, query, params=()):
        # All caller SQL is static; user values are always bound parameters.
        if 'sqlite_master' in query:
            match = re.search(r"name='([a-z_]+)'", query)
            if not match or match[1] not in ('decision_sources', 'submission_sources', 'venue_aliases'):
                raise ValueError('Unsupported legacy table lookup')
            return self.connection.execute('''SELECT 1 FROM information_schema.tables
                WHERE table_schema=current_schema() AND table_name=%s''',
                (match[1],))
        return self.connection.execute(query.replace('?', '%s'), params)

    def has_table(self, name):
        return bool(self.connection.execute('''SELECT 1 FROM information_schema.tables
            WHERE table_schema=current_schema() AND table_name=%s''', (name,)).fetchone())

    def close(self):
        self.connection.close()