import sqlite3, os
from datetime import datetime

DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'linkedin_monitor.db')

def get_connection():
    return sqlite3.connect(DB_PATH)

def init_db():
    conn = get_connection()
    c = conn.cursor()

    c.execute('''CREATE TABLE IF NOT EXISTS processed_comments (
        comment_id   TEXT PRIMARY KEY,
        post_id      TEXT NOT NULL,
        comment_text TEXT,
        detected_at  TEXT NOT NULL,
        status       TEXT DEFAULT 'pending'
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS post_response_counts (
        post_id        TEXT PRIMARY KEY,
        response_count INTEGER DEFAULT 0
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS pending_responses (
        id                  INTEGER PRIMARY KEY AUTOINCREMENT,
        telegram_message_id TEXT,
        comment_id          TEXT NOT NULL,
        post_id             TEXT NOT NULL,
        comment_urn         TEXT NOT NULL,
        proposed_response   TEXT NOT NULL,
        created_at          TEXT NOT NULL,
        status              TEXT DEFAULT 'waiting'
    )''')

    conn.commit()
    conn.close()

def is_comment_processed(comment_id):
    conn = get_connection()
    c = conn.cursor()
    c.execute('SELECT 1 FROM processed_comments WHERE comment_id = ?', (comment_id,))
    result = c.fetchone()
    conn.close()
    return result is not None

def add_processed_comment(comment_id, post_id, comment_text, status='pending'):
    conn = get_connection()
    c = conn.cursor()
    c.execute('''INSERT OR IGNORE INTO processed_comments
        (comment_id, post_id, comment_text, detected_at, status)
        VALUES (?, ?, ?, ?, ?)''',
        (comment_id, post_id, comment_text, datetime.now().isoformat(), status))
    conn.commit()
    conn.close()

def update_comment_status(comment_id, status):
    conn = get_connection()
    c = conn.cursor()
    c.execute('UPDATE processed_comments SET status=? WHERE comment_id=?', (status, comment_id))
    conn.commit()
    conn.close()

def get_post_response_count(post_id):
    conn = get_connection()
    c = conn.cursor()
    c.execute('SELECT response_count FROM post_response_counts WHERE post_id = ?', (post_id,))
    row = c.fetchone()
    conn.close()
    return row[0] if row else 0

def increment_post_response_count(post_id):
    conn = get_connection()
    c = conn.cursor()
    c.execute('''INSERT INTO post_response_counts (post_id, response_count) VALUES (?, 1)
        ON CONFLICT(post_id) DO UPDATE SET response_count = response_count + 1''', (post_id,))
    conn.commit()
    conn.close()

def add_pending_response(comment_id, post_id, comment_urn, proposed_response):
    conn = get_connection()
    c = conn.cursor()
    c.execute('''INSERT INTO pending_responses
        (comment_id, post_id, comment_urn, proposed_response, created_at)
        VALUES (?, ?, ?, ?, ?)''',
        (comment_id, post_id, comment_urn, proposed_response, datetime.now().isoformat()))
    pending_id = c.lastrowid
    conn.commit()
    conn.close()
    return pending_id

def get_pending_by_id(pending_id):
    conn = get_connection()
    c = conn.cursor()
    c.execute('SELECT * FROM pending_responses WHERE id = ?', (pending_id,))
    row = c.fetchone()
    conn.close()
    return row

def update_pending(pending_id, status, new_response=None, telegram_message_id=None):
    conn = get_connection()
    c = conn.cursor()
    if new_response and telegram_message_id:
        c.execute('UPDATE pending_responses SET status=?, proposed_response=?, telegram_message_id=? WHERE id=?',
                  (status, new_response, str(telegram_message_id), pending_id))
    elif new_response:
        c.execute('UPDATE pending_responses SET status=?, proposed_response=? WHERE id=?',
                  (status, new_response, pending_id))
    elif telegram_message_id:
        c.execute('UPDATE pending_responses SET status=?, telegram_message_id=? WHERE id=?',
                  (status, str(telegram_message_id), pending_id))
    else:
        c.execute('UPDATE pending_responses SET status=? WHERE id=?', (status, pending_id))
    conn.commit()
    conn.close()