from db.database import conn, cursor


def create_contact_message(user, topic, message):
    cursor.execute(
        """
        INSERT INTO contact_messages (user_id, first_name, username, topic, message)
        VALUES (?, ?, ?, ?, ?)
        """,
        (user.id, user.first_name, user.username, topic, message)
    )
    conn.commit()
    return cursor.lastrowid


def get_open_contact_count():
    cursor.execute("SELECT COUNT(*) FROM contact_messages WHERE status = 'open'")
    return cursor.fetchone()[0]


def get_open_contact_messages(limit=20):
    cursor.execute(
        """
        SELECT *
        FROM contact_messages
        WHERE status = 'open'
        ORDER BY created_at DESC, id DESC
        LIMIT ?
        """,
        (limit,)
    )
    return cursor.fetchall()


def get_contact_status_counts():
    cursor.execute(
        """
        SELECT status, COUNT(*) AS total
        FROM contact_messages
        GROUP BY status
        """
    )
    return {row["status"]: row["total"] for row in cursor.fetchall()}


def get_contact_messages(limit=20):
    cursor.execute(
        """
        SELECT *
        FROM contact_messages
        WHERE status IN ('open', 'replied', 'closed')
        ORDER BY
            CASE status
                WHEN 'open' THEN 0
                WHEN 'replied' THEN 1
                ELSE 2
            END,
            created_at DESC,
            id DESC
        LIMIT ?
        """,
        (limit,)
    )
    return cursor.fetchall()


def get_contact_history(user_id, limit=5):
    cursor.execute(
        """
        SELECT *
        FROM contact_messages
        WHERE user_id = ?
        ORDER BY created_at DESC, id DESC
        LIMIT ?
        """,
        (user_id, limit)
    )
    return cursor.fetchall()


def get_contact_message(message_id):
    cursor.execute(
        "SELECT * FROM contact_messages WHERE id = ?",
        (message_id,)
    )
    return cursor.fetchone()


def mark_contact_replied(message_id):
    cursor.execute(
        """
        UPDATE contact_messages
        SET status = 'replied', replied_at = datetime('now', 'localtime')
        WHERE id = ?
        """,
        (message_id,)
    )
    conn.commit()
    return cursor.rowcount > 0


def close_contact_message(message_id):
    cursor.execute(
        """
        UPDATE contact_messages
        SET status = 'closed', replied_at = COALESCE(replied_at, datetime('now', 'localtime'))
        WHERE id = ?
        """,
        (message_id,)
    )
    conn.commit()
    return cursor.rowcount > 0


def delete_contact_message(message_id):
    cursor.execute(
        "DELETE FROM contact_messages WHERE id = ?",
        (message_id,)
    )
    conn.commit()
    return cursor.rowcount > 0