#!/usr/bin/env python3
"""Prepare and publish AI persona reactions using each channel's publisher."""

import asyncio
import json
import re
import subprocess
import sys
from pathlib import Path

import requests
from telethon.errors import FloodWaitError, RPCError
import telegram_publisher as publisher

BASE = Path(__file__).resolve().parent
PHP = '/usr/local/bin/php'


def query(db, sql, args=()):
    with db.cursor() as cur:
        cur.execute(sql, args)
        return cur.fetchall()


def execute(db, sql, args=()):
    with db.cursor() as cur:
        cur.execute(sql, args)
        changed = cur.rowcount
    db.commit()
    return changed


def lock(db, name):
    return int(query(db, 'SELECT GET_LOCK(%s, 0) AS acquired',
                     (name,))[0]['acquired'] or 0) == 1


def unlock(db, name):
    query(db, 'SELECT RELEASE_LOCK(%s)', (name,))


def set_status(db, comment_id, status):
    execute(db, """
        UPDATE fan_editor_comments SET status=%s
        WHERE id=%s AND status='sending'
    """, (status, comment_id))


def block_publisher(db, publisher_id, seconds):
    execute(db, """
        UPDATE publishers
        SET flood_status='blocked',
            flood_until=DATE_ADD(NOW(), INTERVAL %s SECOND),
            last_error='Editor sender: Telegram rate limit'
        WHERE id=%s
    """, (max(1, int(seconds)), publisher_id))


def load_job(db, comment_id):
    rows = query(db, """
        SELECT e.*, c.publisher_id, c.username, c.telegram_chat_id,
               q.published_message_id AS news_message_id,
               p.publisher_type, p.telegram_bot_token,
               (p.flood_until IS NOT NULL AND p.flood_until>NOW()) AS blocked
        FROM fan_editor_comments e
        JOIN channels c ON c.id=e.channel_id
        JOIN publish_queue q
          ON q.id=e.news_queue_id AND q.channel_id=e.channel_id
        JOIN publishers p ON p.id=c.publisher_id
        JOIN fan_channel_editors ce
          ON ce.channel_id=e.channel_id AND ce.persona_id=e.persona_id
        JOIN fan_editor_personas persona ON persona.id=e.persona_id
        JOIN fan_editor_auto_state s ON s.id=1
        WHERE e.id=%s AND e.status='draft'
          AND c.channel_type='football_fan'
          AND c.is_active=1 AND c.auto_publish=1
          AND p.is_active=1 AND ce.is_active=1 AND persona.is_active=1
          AND q.status='published' AND q.published_message_id>0
          AND q.published_at>=s.started_at
    """, (comment_id,))
    return rows[0] if rows else None


def is_due(db, job):
    # These are delivery intervals, not daily post limits.
    rows = query(db, """
        SELECT
          EXISTS(
            SELECT 1 FROM fan_editor_comments
            WHERE news_queue_id=%s AND turn_no<%s
              AND status<>'published'
          ) AS unfinished_turn,
          EXISTS(
            SELECT 1 FROM fan_editor_comments e
            JOIN channels c ON c.id=e.channel_id
            WHERE c.publisher_id=%s AND e.status='published'
              AND e.published_at>DATE_SUB(NOW(), INTERVAL 2 SECOND)
          ) AS publisher_busy,
          EXISTS(
            SELECT 1 FROM fan_editor_comments
            WHERE channel_id=%s AND status='published'
              AND published_at>DATE_SUB(NOW(), INTERVAL 30 SECOND)
          ) AS channel_busy,
          EXISTS(
            SELECT 1 FROM fan_editor_comments e
            JOIN channels c ON c.id=e.channel_id
            WHERE c.publisher_id=%s AND e.status='sending'
          ) AS uncertain_delivery
    """, (job['news_queue_id'], job['turn_no'], job['publisher_id'],
          job['channel_id'], job['publisher_id']))
    return not any(int(v or 0) for v in rows[0].values())


async def send_comment(db, comment_id, preview=False):
    job = load_job(db, comment_id)
    if not job:
        return

    pub_id = int(job['publisher_id'])
    lock_name = 'fan-editor-publisher:' + str(pub_id)
    if not lock(db, lock_name):
        return
    client = None
    claimed = False
    try:
        job = load_job(db, comment_id)
        if not job or int(job['blocked'] or 0) or not is_due(db, job):
            return

        pub = publisher.get_publisher(db, pub_id)
        channel = publisher.get_channel(db, int(job['channel_id']))
        if (not pub or not channel or int(pub['is_active']) != 1
                or int(channel['publisher_id'] or 0) != pub_id):
            return

        target = str(channel.get('telegram_chat_id') or '').strip()
        if not target:
            username = str(channel.get('username') or '').strip().lstrip('@')
            if not username:
                raise RuntimeError('Channel has no Telegram target.')
            target = '@' + username

        hashtag = str(job['hashtag']).strip().lstrip('#')
        body = str(job['comment_text']).strip()
        if not body or len(body) > 500 or not re.fullmatch(r'[A-Za-z0-9_]+', hashtag):
            raise RuntimeError('Invalid prepared comment or hashtag.')
        text = body + '\n#' + hashtag

        previous = query(db, """
            SELECT published_message_id
            FROM fan_editor_comments
            WHERE news_queue_id=%s AND turn_no<%s AND status='published'
            ORDER BY turn_no DESC LIMIT 1
        """, (job['news_queue_id'], job['turn_no']))
        reply_id = int(previous[0]['published_message_id'] if previous
                       else job['news_message_id'])
        kind = pub['publisher_type']
        if kind not in ('telegram_bot', 'telegram_user'):
            raise RuntimeError('Unsupported channel publisher type.')

        if preview:
            print(f"PREVIEW comment={comment_id} channel={job['channel_id']} "
                  f"publisher={pub_id} type={kind} reply={reply_id}")
            print(text)
            return

        if kind == 'telegram_bot':
            token = str(pub.get('telegram_bot_token') or '').strip()
            if not token:
                raise RuntimeError('Selected bot has no token.')
        else:
            publisher.validate_assignment(pub_id, pub, channel)
            client = publisher.build_client(pub_id, pub)
            await publisher.ensure_authorized(db, pub_id, client)
            target = publisher.resolve_target(channel)
            parent = await client.get_messages(target, ids=reply_id)
            if not parent:
                raise RuntimeError('Reply message no longer exists.')

        # Recheck selection immediately before claiming the delivery.
        current = publisher.get_channel(db, int(job['channel_id']))
        if (int(current.get('publisher_id') or 0) != pub_id
                or int(current['is_active']) != 1
                or int(current['auto_publish']) != 1):
            return

        changed = execute(db, """
            UPDATE fan_editor_comments SET status='sending'
            WHERE id=%s AND status='draft'
        """, (comment_id,))
        if changed != 1:
            return
        claimed = True

        if kind == 'telegram_bot':
            try:
                response = requests.post(
                    f'https://api.telegram.org/bot{token}/sendMessage',
                    json={
                        'chat_id': target,
                        'text': text,
                        'reply_parameters': {
                            'message_id': reply_id,
                            'allow_sending_without_reply': False,
                        },
                        'link_preview_options': {'is_disabled': True},
                    },
                    timeout=(10, 40),
                )
                payload = response.json()
            except Exception:
                # Never print a requests exception: its URL can contain the token.
                print(f"DELIVERY UNKNOWN comment={comment_id}; retained as sending")
                return
            if not isinstance(payload, dict):
                print(f"DELIVERY UNKNOWN comment={comment_id}")
                return
            if not payload.get('ok'):
                code = int(payload.get('error_code') or response.status_code)
                retry = int((payload.get('parameters') or {}).get('retry_after') or 0)
                if code == 429:
                    block_publisher(db, pub_id, retry or 60)
                    set_status(db, comment_id, 'draft')
                    print(f"RATE LIMIT publisher={pub_id}; queued for later")
                elif 400 <= code < 500:
                    set_status(db, comment_id, 'failed')
                    print(f"TELEGRAM REJECTED comment={comment_id} code={code}")
                else:
                    print(f"DELIVERY UNKNOWN comment={comment_id} code={code}")
                return
            result = payload.get('result') or {}
            message_id = int(result.get('message_id') or 0)
        else:
            try:
                sent = await client.send_message(
                    target, text, reply_to=reply_id,
                    parse_mode=None, link_preview=False,
                )
                message_id = int(sent.id)
            except FloodWaitError as exc:
                block_publisher(db, pub_id, exc.seconds)
                set_status(db, comment_id, 'draft')
                print(f"RATE LIMIT publisher={pub_id}; queued for later")
                return
            except RPCError as exc:
                set_status(db, comment_id, 'failed')
                print(f"TELEGRAM REJECTED comment={comment_id} type={type(exc).__name__}")
                return

        if message_id < 1:
            print(f"DELIVERY UNKNOWN comment={comment_id}")
            return

        execute(db, """
            UPDATE fan_editor_comments
            SET status='published', published_message_id=%s, published_at=NOW()
            WHERE id=%s AND status='sending'
        """, (message_id, comment_id))
        print(f"EDITOR SENT comment={comment_id} channel={job['channel_id']} "
              f"publisher={pub_id} message={message_id}")
    except BaseException as exc:
        if isinstance(exc, (KeyboardInterrupt, asyncio.CancelledError)):
            raise
        # If sending was claimed, leave it pending reconciliation, never resend blindly.
        print(f"EDITOR SEND ERROR comment={comment_id} "
              f"type={type(exc).__name__} claimed={int(claimed)}")
    finally:
        if client:
            try:
                await client.disconnect()
            except Exception:
                pass
        unlock(db, lock_name)


async def main():
    preview = len(sys.argv) > 1 and sys.argv[1] == 'preview'
    if len(sys.argv) > 1 and sys.argv[1] not in ('preview', 'run'):
        raise RuntimeError('Usage: preview|run')
    db = publisher.get_database()
    global_lock = 'fan-editor-auto:v1'
    try:
        if not lock(db, global_lock):
            print('Editor worker already running.')
            return

        if not preview:
            # Bounded cleanup: persistent season facts are retained.
            execute(db, """
                DELETE FROM fan_editor_football_cache
                WHERE expires_at<NOW() LIMIT 1000
            """)
            # Process all new published news in order, rather than just the latest.
            # Drain existing comments in a channel before generating its next thread.
            jobs = query(db, """
                SELECT q.id, q.channel_id
                FROM publish_queue q
                JOIN posts p ON p.id=q.post_id
                JOIN channels c ON c.id=q.channel_id
                JOIN publishers pub ON pub.id=c.publisher_id
                JOIN fan_editor_auto_state s ON s.id=1
                LEFT JOIN fan_editor_threads t ON t.news_queue_id=q.id
                WHERE q.status='published' AND q.published_message_id>0
                  AND q.published_at>=s.started_at
                  AND c.channel_type='football_fan'
                  AND c.is_active=1 AND c.auto_publish=1 AND pub.is_active=1
                  AND t.news_queue_id IS NULL
                  AND EXISTS (
                    SELECT 1 FROM fan_channel_editors ce
                    JOIN fan_editor_personas e ON e.id=ce.persona_id
                    WHERE ce.channel_id=c.id AND ce.is_active=1 AND e.is_active=1
                  )
                  AND EXISTS (
                    SELECT 1 FROM fan_news_review_queue r
                    WHERE r.channel_id=q.channel_id
                      AND p.dedupe_hash=SHA2(CONCAT(
                        'fan_news:',r.feed_item_id,':channel:',r.channel_id),256)
                  )
                ORDER BY q.published_at,q.id
                LIMIT 20
            """)
            handled_channels = set()
            for job in jobs:
                channel_id = int(job['channel_id'])
                if channel_id in handled_channels:
                    continue
                handled_channels.add(channel_id)
                pending = query(db, """
                    SELECT e.id
                    FROM fan_editor_comments e
                    JOIN publish_queue q ON q.id=e.news_queue_id
                    JOIN fan_editor_auto_state s ON s.id=1
                    WHERE e.channel_id=%s AND e.status<>'published'
                      AND q.published_at>=s.started_at
                    LIMIT 1
                """, (channel_id,))
                if pending:
                    continue
                print(f"PREPARING channel={channel_id} news={job['id']}", flush=True)
                try:
                    completed = subprocess.run([
                        PHP, str(BASE / 'fan_editor_preview.php'),
                        str(channel_id), 'prepare',
                        '--queue-id=' + str(job['id']),
                    ], timeout=240)
                    if completed.returncode:
                        print(f"PREPARATION FAILED news={job['id']}; will retry")
                except subprocess.TimeoutExpired:
                    print(f"PREPARATION TIMEOUT news={job['id']}; will retry")

        comments = query(db, """
            SELECT e.id
            FROM fan_editor_comments e
            JOIN publish_queue q ON q.id=e.news_queue_id
            JOIN fan_editor_auto_state s ON s.id=1
            WHERE e.status='draft' AND q.status='published'
              AND q.published_at>=s.started_at
            ORDER BY q.published_at,e.news_queue_id,e.turn_no
            LIMIT 100
        """)
        for comment in comments:
            await send_comment(db, int(comment['id']), preview)
        print('EDITOR CYCLE COMPLETE' if not preview else
              'PREVIEW COMPLETE — NOTHING GENERATED OR SENT')
    finally:
        try:
            unlock(db, global_lock)
        finally:
            db.close()


if __name__ == '__main__':
    try:
        asyncio.run(main())
    except Exception as exc:
        print('EDITOR WORKER ERROR: ' + type(exc).__name__, file=sys.stderr)
        sys.exit(1)