from slack_sdk.web.async_client import AsyncWebClient
from datetime import datetime
import aiosqlite

DB_PATH = "botnet.db"

async def init_db():
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("""
            CREATE TABLE IF NOT EXISTS Users (
                slack_id TEXT PRIMARY KEY,
                oauth_token TEXT NOT NULL,
                joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        await db.execute("""
             CREATE TABLE IF NOT EXISTS Deployments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                message TEXT NOT NULL,
                submitted_by TEXT NOT NULL,
                status TEXT DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                deployed_at TIMESTAMP
             )
         """)
        await db.execute("""
             CREATE TABLE IF NOT EXISTS DeploymentMessages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                deployment_id INTEGER NOT NULL,
                user_id TEXT NOT NULL,
                message_id TEXT NOT NULL,
                channel_id TEXT NOT NULL,
                sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                voting_message_ts TEXT,
                FOREIGN KEY (deployment_id) REFERENCES Deployments(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES Users(slack_id)
             )
         """)
        await db.execute("""
             CREATE TABLE IF NOT EXISTS Votes (
                deployment_id INTEGER NOT NULL,
                voter_id TEXT NOT NULL,
                vote TEXT NOT NULL,
                voted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (deployment_id) REFERENCES Deployments(id) ON DELETE CASCADE,
                FOREIGN KEY (voter_id) REFERENCES Users(slack_id)
                PRIMARY KEY (deployment_id, voter_id)
             )
         """)
        await db.commit()

async def add_user(slack_id: str, oauth_token: str):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "INSERT OR REPLACE INTO Users (slack_id, oauth_token) VALUES (?, ?)",
            (slack_id, oauth_token)
        )
        await db.commit()
    client = AsyncWebClient(token=oauth_token)
    try:
        await client.chat_postMessage(
            channel="C0BGMV1CAQG",
            text="I've joined the botnet!",
            as_user=True
        )
    except Exception as e:
        print(f"Failed to send welcome message: {e}")

async def create_deployment(user_id: str, message: str) -> int:
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "INSERT INTO Deployments (message, submitted_by) VALUES (?, ?)",
            (message, user_id)
        )
        await db.commit()
        return cursor.lastrowid

async def get_daily_user_deployment_count(user_id: str) -> int:
    from datetime import datetime
    today = datetime.now().date().isoformat()
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            """SELECT COUNT(*) FROM Deployments 
               WHERE submitted_by = ? AND date(created_at) = ?""",
            (user_id, today)
        )
        row = await cursor.fetchone()
        return row[0] if row else 0

async def get_deployment(deployment_id: int) -> dict | None:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM Deployments WHERE id = ?",
            (deployment_id,)
        )
        row = await cursor.fetchone()
        return dict(row) if row else None

async def update_deployment_status(deployment_id: int, status: str):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "UPDATE Deployments SET status = ? WHERE id = ?",
            (status, deployment_id)
        )
        await db.commit()

async def add_deployment_message(deployment_id: int, user_id: str, channel_id: str, message_id: str):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            """INSERT INTO DeploymentMessages 
               (deployment_id, user_id, channel_id, message_id) 
               VALUES (?, ?, ?, ?)""",
            (deployment_id, user_id, channel_id, message_id)
        )
        await db.commit()

async def get_deployment_messages(deployment_id: int) -> list[dict]:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM DeploymentMessages WHERE deployment_id = ?",
            (deployment_id,)
        )
        rows = await cursor.fetchall()
        return [dict(row) for row in rows]

async def get_user(slack_id: str) -> dict | None:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute(
            "SELECT * FROM Users WHERE slack_id = ?",
            (slack_id,)
        )
        row = await cursor.fetchone()
        return dict(row) if row else None

async def get_active_users() -> list[dict]:
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT * FROM Users")
        rows = await cursor.fetchall()
        return [dict(row) for row in rows]


async def add_vote(deployment_id: int, voter_id: str, vote: str):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("INSERT OR REPLACE INTO Votes (deployment_id, voter_id, vote) VALUES (?, ?, ?)", (deployment_id, voter_id, vote))
        await db.commit()

async def get_votes(deployment_id: int):
    async with aiosqlite.connect(DB_PATH) as db:
        db.row_factory = aiosqlite.Row
        cursor = await db.execute("SELECT voter_id, vote FROM Votes WHERE deployment_id = ?", (deployment_id,))
        rows = await cursor.fetchall()
        
        votes = {"approve": [], "reject": []}
        for row in rows:
            votes[row["vote"]].append(row["voter_id"])
        return votes

async def get_user_vote(deployment_id: int, voter_id: str):
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute("SELECT vote from Votes WHERE deployment_id = ? AND voter_id = ?", (deployment_id, voter_id))
        row = await cursor.fetchone()
        return row[0] if row else None

async def get_vote_counts(deployment_id: int):
    votes = await get_votes(deployment_id)
    return len(votes["approve"]), len(votes["reject"])