"""
Sheet management — PostgreSQL-backed.
All functions are async.
"""

import logging
import re
from datetime import datetime, timezone
from typing import List, Optional, Dict
import time
import random

from ..db.pool import get_pool

logger = logging.getLogger(__name__)


async def get_user_sheets(user_id: str) -> List[Dict]:
    pool = get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            'SELECT id, name, client_id, item_count, created_at, modified_at '
            'FROM sheets WHERE user_id = $1 ORDER BY modified_at DESC',
            user_id
        )
        return [_row_to_meta(r, user_id) for r in rows]


async def create_sheet(user_id: str, name: str, data: List[dict] = None, client_id: str = '') -> Dict:
    if data is None:
        data = []
    sheet_id = str(int(time.time())) + str(random.randint(100, 999))
    sheet_name = name or f"Untitled Sheet — {datetime.now().strftime('%Y-%m-%d %H:%M')}"
    client_id_val = client_id or None

    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow('''
            INSERT INTO sheets (id, user_id, name, client_id, data, item_count)
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING id, name, client_id, item_count, created_at, modified_at
        ''', sheet_id, user_id, sheet_name, client_id_val, data, len(data))
        return _row_to_meta(row, user_id)


async def load_sheet_data(user_id: str, sheet_id: str) -> Optional[List[dict]]:
    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            'SELECT data FROM sheets WHERE id = $1 AND user_id = $2',
            sheet_id, user_id
        )
        if row is None:
            return None
        return row['data'] if row['data'] is not None else []


async def update_sheet(user_id: str, sheet_id: str, data: List[dict]) -> bool:
    pool = get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute('''
            UPDATE sheets SET data = $3, item_count = $4, modified_at = NOW()
            WHERE id = $1 AND user_id = $2
        ''', sheet_id, user_id, data, len(data))
        return result != 'UPDATE 0'


async def delete_sheet(user_id: str, sheet_id: str):
    pool = get_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            'DELETE FROM sheets WHERE id = $1 AND user_id = $2',
            sheet_id, user_id
        )


async def rename_sheet(user_id: str, sheet_id: str, new_name: str) -> bool:
    pool = get_pool()
    async with pool.acquire() as conn:
        result = await conn.execute('''
            UPDATE sheets SET name = $3, modified_at = NOW()
            WHERE id = $1 AND user_id = $2
        ''', sheet_id, user_id, new_name)
        return result != 'UPDATE 0'


async def duplicate_sheet(user_id: str, sheet_id: str) -> Optional[Dict]:
    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            'SELECT name, data FROM sheets WHERE id = $1 AND user_id = $2',
            sheet_id, user_id
        )
        if row is None:
            return None
        return await create_sheet(user_id, f"Copy of {row['name']}", row['data'])


async def get_sheet_client_id(user_id: str, sheet_id: str) -> Optional[str]:
    pool = get_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            'SELECT client_id FROM sheets WHERE id = $1 AND user_id = $2',
            sheet_id, user_id
        )
        if row is None:
            return None
        return row['client_id']


async def set_sheet_client_id(user_id: str, sheet_id: str, client_id: str):
    pool = get_pool()
    async with pool.acquire() as conn:
        await conn.execute('''
            UPDATE sheets SET client_id = $3, modified_at = NOW()
            WHERE id = $1 AND user_id = $2
        ''', sheet_id, user_id, client_id or None)


def generate_next_id(data: List[dict]) -> str:
    """Generate the next DEL-NNN id. Remains sync — operates on in-memory data."""
    max_id = 0
    for row in data:
        num_str = row.get('Number', '').replace('DEL-', '')
        try:
            n = int(num_str)
            if n > max_id:
                max_id = n
        except (ValueError, AttributeError):
            pass
    return f"DEL-{str(max_id + 1).zfill(3)}"


def _row_to_meta(row, user_id: str) -> Dict:
    return {
        'id': row['id'],
        'name': row['name'],
        'client_id': row['client_id'],
        'itemCount': row['item_count'],
        'user': user_id,
        'created': row['created_at'].isoformat() if row['created_at'] else None,
        'modified': row['modified_at'].isoformat() if row['modified_at'] else None,
    }