"""Wyszukiwanie powiazan plikow z wpisami, tematami forum i komentarzami Drupal 6.

Powiazania 'bezposrednie' to identyfikatory fid (Upload, CCK/FileField/ImageField).
Powiazania z tekstu to poszlaki: pelna sciezka (mocniejsze) lub sama nazwa.
"""
from __future__ import annotations

import html
import os
import re
from collections import defaultdict
from urllib.parse import quote


IDENT = re.compile(r'^[A-Za-z_][A-Za-z0-9_]*$')


def sql_ident(value):
    if not IDENT.fullmatch(value):
        raise ValueError(f'Niedozwolona nazwa tabeli/kolumny: {value!r}')
    return f'`{value}`'


def chunks(seq, size=250):
    seq = list(seq)
    for start in range(0, len(seq), size):
        yield seq[start:start + size]


def node_url(base_url, nid, cid=None, *, clean_urls=True, comment_permalinks=False):
    if cid is not None and comment_permalinks:
        base = (base_url.rstrip('/') + '/') if base_url else ''
        if clean_urls:
            return f'{base}comment/{int(cid)}#comment-{int(cid)}'
        return f'{base}?q=comment/{int(cid)}#comment-{int(cid)}'
    if not base_url:
        result = f'node/{int(nid)}'
    elif clean_urls:
        result = f'{base_url.rstrip("/")}/node/{int(nid)}'
    else:
        result = f'{base_url.rstrip("/")}/?q=node/{int(nid)}'
    if cid is not None:
        result += f'#comment-{int(cid)}'
    return result


def search_url(base_url, filename, *, clean_urls=True):
    if not base_url:
        return ''
    term = quote(filename, safe='')
    if clean_urls:
        return f'{base_url.rstrip("/")}/search/node/{term}'
    return f'{base_url.rstrip("/")}/?q=search/node/{term}'


class ReferenceMatcher:
    """Jedno przejscie przez tresc na liste interesujacych plikow.

    Dopasowanie tylko nazwy pliku ma pewnosc 'mozliwe', nigdy 'pewne'.
    """

    def __init__(self, images, basename_match=True):
        variants = defaultdict(list)
        for image in images:
            fid = int(image['fid'])
            file_path = (image.get('db_filepath') or '').replace('\\', '/').lstrip('/')
            relative = (image.get('rel_path') or '').replace('\\', '/').lstrip('/')
            for path in (file_path, relative):
                if path and '/' in path:
                    for variant in (path, quote(path, safe='/')):
                        variants[variant.casefold()].append((fid, 'sciezka'))
            name = os.path.basename(relative or file_path)
            if basename_match and name and len(name) >= 5:
                for variant in (name, quote(name, safe='')):
                    variants[variant.casefold()].append((fid, 'nazwa'))
        self.variants = dict(variants)
        # Najpierw dluzsze wzorce, np. 'uploads/scan.jpg' przed 'scan.jpg'.
        self.pattern = None
        if variants:
            ordered = sorted(variants, key=lambda x: (-len(x), x))
            self.pattern = re.compile('|'.join(re.escape(x) for x in ordered), re.IGNORECASE)

    def match(self, text):
        if not self.pattern or not text:
            return {}
        text = html.unescape(str(text))
        found = {}
        for occurrence in self.pattern.finditer(text):
            word = occurrence.group(0).casefold()
            for fid, kind in self.variants.get(word, []):
                if kind == 'nazwa':
                    before = text[occurrence.start() - 1] if occurrence.start() else ''
                    after = text[occurrence.end()] if occurrence.end() < len(text) else ''
                    if before and (before.isalnum() or before in '._-'):
                        continue
                    if after and (after.isalnum() or after in '._-'):
                        continue
                # Preferuj trafienie przez pelna sciezke od samej nazwy.
                if fid not in found or (found[fid] == 'nazwa' and kind == 'sciezka'):
                    found[fid] = kind
        return found


class DrupalReferenceLookup:
    def __init__(self, connection, *, table_prefix='', base_url='', clean_urls=True, comment_permalinks=False):
        self.connection = connection
        self.prefix = table_prefix
        self.base_url = base_url
        self.clean_urls = clean_urls
        self.comment_permalinks = comment_permalinks
        if table_prefix and not IDENT.fullmatch(table_prefix):
            raise ValueError('Nieprawidlowy prefix tabel')
        self.tables = set()
        self.columns = {}

    def table(self, name):
        return f'{self.prefix}{name}'

    def inspect_schema(self):
        with self.connection.cursor() as cursor:
            cursor.execute('SHOW TABLES')
            self.tables = {str(next(iter(row.values()))) for row in cursor.fetchall()}
            if self.table('node') not in self.tables:
                raise RuntimeError('Nie znaleziono tabeli node; sprawdz --table-prefix')
            for table in sorted(self.tables):
                if self.prefix and not table.startswith(self.prefix):
                    continue
                if not IDENT.fullmatch(table):
                    continue
                cursor.execute(f'SHOW COLUMNS FROM {sql_ident(table)}')
                self.columns[table] = {row['Field']: row['Type'].lower() for row in cursor.fetchall()}

    def _add(self, refs, fid, row, source, confidence='pewne', cid=None):
        fid = int(fid)
        nid = int(row['nid'])
        cid = int(cid) if cid is not None else None
        key = (nid, cid)
        if key not in refs[fid]:
            refs[fid][key] = {
                'nid': nid,
                'cid': cid,
                'title': row.get('title') or '(bez tytulu)',
                'node_type': row.get('node_type') or '',
                'published': int(row.get('node_status', 1)) == 1,
                'comment_published': (int(row['comment_status']) == 0 if row.get('comment_status') is not None else None),
                'url': node_url(self.base_url, nid, cid, clean_urls=self.clean_urls,
                                comment_permalinks=self.comment_permalinks),
                'edit_url': node_url(self.base_url, nid, clean_urls=self.clean_urls) + '/edit' if self.clean_urls else (
                    f'{self.base_url.rstrip("/")}/?q=node/{nid}/edit' if self.base_url else f'node/{nid}/edit'),
                'sources': [],
                'confidence': confidence,
            }
        ref = refs[fid][key]
        if source not in ref['sources']:
            ref['sources'].append(source)
        if confidence == 'pewne':
            ref['confidence'] = 'pewne'

    def _direct_relations(self, refs, fids):
        node = sql_ident(self.table('node'))
        # Upload + kazda tabela z nid i fid lub XXX_fid (CCK, filefield...).
        for table, cols in sorted(self.columns.items()):
            if not table.startswith(self.prefix):
                continue
            if 'nid' not in cols or table == self.table('node'):
                continue
            fid_cols = [col for col in cols if col == 'fid' or col.endswith('_fid')]
            if not fid_cols:
                continue
            if not (table == self.table('upload') or table.startswith(self.table('content_')) or 'fid' in cols or any(col.startswith('field_') for col in fid_cols)):
                continue
            try:
                with self.connection.cursor() as cursor:
                    for fid_column in fid_cols:
                        revision_join = ' AND n.vid = t.vid' if 'vid' in cols else ''
                        for batch in chunks(fids):
                            placeholders = ','.join(['%s'] * len(batch))
                            query = (
                                f'SELECT n.nid AS nid, n.title AS title, n.type AS node_type, '
                                f'n.status AS node_status, t.{sql_ident(fid_column)} AS fid, '
                                f'{"t.cid" if "cid" in cols else "NULL"} AS cid '
                                f'FROM {sql_ident(table)} t JOIN {node} n ON n.nid = t.nid'
                                f'{revision_join} WHERE t.{sql_ident(fid_column)} IN ({placeholders})'
                            )
                            cursor.execute(query, tuple(batch))
                            for row in cursor.fetchall():
                                if row.get('fid') is not None:
                                    source = f'{table}.{fid_column}'
                                    self._add(refs, row['fid'], row, source, cid=row.get('cid'))
            except Exception as exc:
                print(f'[WARN] Nie udalo sie odczytac powiazan {table}: {exc}')

    def _comment_direct_relations(self, refs, fids):
        # W niektorych instalacjach zalaczniki do komentarzy maja tylko cid+fid.
        comments = self.table('comments')
        if comments not in self.columns:
            return
        for table, cols in sorted(self.columns.items()):
            if table in (comments, self.table('node')) or 'cid' not in cols or 'nid' in cols:
                continue
            fid_cols = [col for col in cols if col == 'fid' or col.endswith('_fid')]
            if not fid_cols:
                continue
            # Nie interpretuj losowych tabel jako zalacznikow do komentarzy.
            if 'comment' not in table:
                continue
            try:
                with self.connection.cursor() as cursor:
                    for fid_column in fid_cols:
                        for batch in chunks(fids):
                            placeholders = ','.join(['%s'] * len(batch))
                            query = (
                                f'SELECT n.nid AS nid, n.title AS title, n.type AS node_type, '
                                f'n.status AS node_status, c.status AS comment_status, '
                                f'c.cid AS cid, t.{sql_ident(fid_column)} AS fid '
                                f'FROM {sql_ident(table)} t '
                                f'JOIN {sql_ident(comments)} c ON c.cid = t.cid '
                                f'JOIN {sql_ident(self.table("node"))} n ON n.nid = c.nid '
                                f'WHERE t.{sql_ident(fid_column)} IN ({placeholders})'
                            )
                            cursor.execute(query, tuple(batch))
                            for row in cursor.fetchall():
                                if row.get('fid') is not None:
                                    self._add(refs, row['fid'], row, f'{table}.{fid_column}', cid=row['cid'])
            except Exception as exc:
                print(f'[WARN] Nie udalo sie odczytac zalacznikow do komentarzy {table}: {exc}')

    def _stream_query(self, query):
        # Kursor strumieniowy nie pobiera do RAM calego body z wielkiej bazy.
        import pymysql
        cursor = self.connection.cursor(pymysql.cursors.SSDictCursor)
        try:
            cursor.execute(query)
            while True:
                batch = cursor.fetchmany(200)
                if not batch:
                    break
                for row in batch:
                    yield row
        finally:
            cursor.close()

    def _scan_body(self, refs, matcher):
        revision = self.table('node_revisions')
        if revision not in self.columns or 'body' not in self.columns[revision]:
            print('[WARN] Brak node_revisions.body - pomijam tekst wpisow')
            return
        teaser_field = ('r.teaser AS teaser' if 'teaser' in self.columns[revision]
                        else 'NULL AS teaser')
        query = (
            f'SELECT n.nid, n.title, n.type AS node_type, n.status AS node_status, '
            f'r.body AS body, {teaser_field} FROM {sql_ident(self.table("node"))} n '
            f'JOIN {sql_ident(revision)} r ON r.vid = n.vid AND r.nid = n.nid '
            f'WHERE (r.body IS NOT NULL AND r.body <> \'\')'
            + (" OR (r.teaser IS NOT NULL AND r.teaser <> '')" if 'teaser' in self.columns[revision] else '')
        )
        for row in self._stream_query(query):
            for field, source in (('body', 'tresc wpisu'), ('teaser', 'zajawka wpisu')):
                for fid, kind in matcher.match(row.get(field)).items():
                    confidence = 'pewne' if kind == 'sciezka' else 'mozliwe'
                    self._add(refs, fid, row, f'{source} ({kind})', confidence)

    def _scan_comments(self, refs, matcher):
        table = self.table('comments')
        if table not in self.columns or 'comment' not in self.columns[table]:
            return
        subject_field = ('c.subject AS comment_subject' if 'subject' in self.columns[table]
                         else 'NULL AS comment_subject')
        query = (
            f'SELECT n.nid, n.title, n.type AS node_type, n.status AS node_status, '
            f'c.cid, c.status AS comment_status, c.comment, {subject_field} '
            f'FROM {sql_ident(table)} c '
            f'JOIN {sql_ident(self.table("node"))} n ON n.nid = c.nid '
        )
        for row in self._stream_query(query):
            for field, source in (('comment', 'komentarz'), ('comment_subject', 'tytul komentarza')):
                for fid, kind in matcher.match(row.get(field)).items():
                    self._add(refs, fid, row, f'{source} ({kind})',
                              'pewne' if kind == 'sciezka' else 'mozliwe', row['cid'])

    def _scan_cck_text(self, refs, matcher):
        # Opcjonalne: tekst moze byc w polach niestandardowych CCK.
        for table, cols in sorted(self.columns.items()):
            if not table.startswith(self.table('content_')) or 'nid' not in cols:
                continue
            columns = [col for col, typ in cols.items() if (
                'text' in typ or 'varchar' in typ
            ) and col not in ('nid', 'vid')]
            if not columns:
                continue
            revision_join = ' AND n.vid = t.vid' if 'vid' in cols else ''
            for col in columns:
                query = (
                    f'SELECT n.nid, n.title, n.type AS node_type, n.status AS node_status, '
                    f't.{sql_ident(col)} AS value FROM {sql_ident(table)} t '
                    f'JOIN {sql_ident(self.table("node"))} n ON n.nid = t.nid'
                    f'{revision_join} WHERE t.{sql_ident(col)} IS NOT NULL'
                )
                try:
                    for row in self._stream_query(query):
                        for fid, kind in matcher.match(row.get('value')).items():
                            self._add(refs, fid, row, f'{table}.{col} ({kind})', 'pewne' if kind == 'sciezka' else 'mozliwe')
                except Exception as exc:
                    print(f'[WARN] Nie udalo sie przeskanowac pola {table}.{col}: {exc}')

    def lookup(self, images, *, scan_text=True, scan_cck_text=False, basename_match=True):
        if not images:
            return {}
        self.inspect_schema()
        fids = sorted({int(img['fid']) for img in images})
        refs = defaultdict(dict)
        self._direct_relations(refs, fids)
        self._comment_direct_relations(refs, fids)
        if scan_text:
            matcher = ReferenceMatcher(images, basename_match=basename_match)
            if matcher.pattern:
                print('[INFO] Szukam odnosnikow w aktualnych tresciach wpisow i komentarzy...')
                self._scan_body(refs, matcher)
                self._scan_comments(refs, matcher)
                if scan_cck_text:
                    self._scan_cck_text(refs, matcher)
        return {
            fid: sorted(per_location.values(), key=lambda ref: (ref['nid'], ref['cid'] or 0))
            for fid, per_location in refs.items()
        }