"""
Outlook ↔ vault.db sync.
Sincronizza calendario e task da Microsoft Graph a vault.db (SQLite locale).

Usage: venv/bin/python3 outlook_sync.py [--dry-run]
"""
import sys
import os
import json
import hashlib
import sqlite3
import re as _re
from datetime import datetime, date, timedelta
from pathlib import Path

import httpx
import pytz as _pytz
from dotenv import load_dotenv, set_key

load_dotenv('/mnt/ssd/adrian/.env')

CLIENT_ID     = os.getenv('OUTLOOK_CLIENT_ID')
CLIENT_SECRET = os.getenv('OUTLOOK_CLIENT_SECRET')
REFRESH_TOKEN = os.getenv('OUTLOOK_REFRESH_TOKEN')
ENV_FILE      = '/mnt/ssd/adrian/.env'

VAULT_DB  = Path('/mnt/ssd/data/adrian-ops/adrian.db')
TOKEN_URL = 'https://login.microsoftonline.com/consumers/oauth2/v2.0/token'
GRAPH_URL = 'https://graph.microsoft.com/v1.0'
_LOCAL_TZ = _pytz.timezone('Europe/Rome')

DRY_RUN = '--dry-run' in sys.argv


def log(msg):
    print(f'[outlook_sync] {msg}')


# ─── vault.db helpers ────────────────────────────────────────────────────────

def _db():
    conn = sqlite3.connect(VAULT_DB)
    conn.row_factory = sqlite3.Row
    return conn


def _item_id(prefix: str, outlook_id: str) -> str:
    h = hashlib.sha256(outlook_id.encode()).hexdigest()[:16]
    return f'{prefix}-{h}'


def vault_get(item_id: str) -> dict | None:
    conn = _db()
    row = conn.execute('SELECT * FROM items WHERE id=?', (item_id,)).fetchone()
    conn.close()
    if not row:
        return None
    d = dict(row)
    d['meta'] = json.loads(d['meta'] or '{}')
    return d


def vault_upsert(item_id: str, item_type: str, body: str, meta: dict) -> bool:
    meta_str = json.dumps(meta, ensure_ascii=False)
    conn = _db()
    existing = conn.execute('SELECT id FROM items WHERE id=?', (item_id,)).fetchone()
    if existing:
        conn.execute('UPDATE items SET body=?, meta=? WHERE id=?', (body, meta_str, item_id))
        is_new = False
    else:
        conn.execute(
            'INSERT INTO items(id, type, body, file, meta) VALUES(?,?,?,NULL,?)',
            (item_id, item_type, body, meta_str)
        )
        is_new = True
    conn.commit()
    conn.close()
    return is_new


def vault_delete(item_id: str) -> None:
    conn = _db()
    conn.execute('DELETE FROM items WHERE id=?', (item_id,))
    conn.commit()
    conn.close()


def vault_query(item_type: str) -> list[dict]:
    conn = _db()
    rows = conn.execute('SELECT * FROM items WHERE type=?', (item_type,)).fetchall()
    conn.close()
    result = []
    for row in rows:
        d = dict(row)
        d['meta'] = json.loads(d['meta'] or '{}')
        result.append(d)
    return result


# ─── Microsoft Graph helpers ─────────────────────────────────────────────────

def get_access_token() -> str:
    r = httpx.post(TOKEN_URL, data={
        'client_id': CLIENT_ID,
        'refresh_token': REFRESH_TOKEN,
        'grant_type': 'refresh_token',
        'scope': 'Calendars.ReadWrite Tasks.ReadWrite offline_access',
    }, timeout=15)
    r.raise_for_status()
    data = r.json()
    if 'refresh_token' in data and data['refresh_token'] != REFRESH_TOKEN:
        set_key(ENV_FILE, 'OUTLOOK_REFRESH_TOKEN', data['refresh_token'])
    return data['access_token']


def graph_get(token: str, path: str, params: dict = None) -> dict:
    r = httpx.get(f'{GRAPH_URL}{path}',
                  headers={'Authorization': f'Bearer {token}'},
                  params=params, timeout=30)
    r.raise_for_status()
    return r.json()


def graph_post(token: str, path: str, body: dict) -> dict:
    r = httpx.post(f'{GRAPH_URL}{path}',
                   headers={'Authorization': f'Bearer {token}', 'Content-Type': 'application/json'},
                   json=body, timeout=30)
    r.raise_for_status()
    return r.json()


def graph_patch(token: str, path: str, body: dict) -> None:
    r = httpx.patch(f'{GRAPH_URL}{path}',
                    headers={'Authorization': f'Bearer {token}', 'Content-Type': 'application/json'},
                    json=body, timeout=30)
    r.raise_for_status()


def graph_delete(token: str, path: str) -> bool:
    try:
        r = httpx.delete(f'{GRAPH_URL}{path}',
                         headers={'Authorization': f'Bearer {token}'}, timeout=30)
        return r.status_code in (204, 404)
    except Exception as e:
        log(f'  [ERR] graph_delete {path}: {e}')
        return False


# ─── Parsing helpers ──────────────────────────────────────────────────────────

def parse_datetime(dt_str: str) -> tuple[str | None, str | None]:
    if not dt_str:
        return None, None
    if 'T' in dt_str:
        dt = datetime.fromisoformat(dt_str.replace('Z', '+00:00'))
        if dt.tzinfo is None:
            dt = _pytz.utc.localize(dt)
        dt_local = dt.astimezone(_LOCAL_TZ)
        return dt_local.strftime('%Y-%m-%d'), dt_local.strftime('%H:%M')
    return dt_str[:10], None


def clean_html(text: str) -> str | None:
    if not text:
        return None
    import html as _html
    cleaned = _re.sub(r'<[^>]+>', '', text)
    prev = None
    while prev != cleaned:
        prev = cleaned
        cleaned = _html.unescape(cleaned)
    cleaned = _re.sub(r'[\xa0]+', ' ', cleaned).strip()
    return cleaned if cleaned else None


OUTLOOK_RRULE_MAP = {
    ('weekly', 1): 'weekly',
    ('weekly', 2): 'biweekly',
    ('absoluteMonthly', 1): 'monthly',
    ('absoluteYearly', 1): 'annual',
    ('daily', 1): 'daily',
}

RRULE_TO_OUTLOOK = {
    'weekly': ('weekly', 1),
    'biweekly': ('weekly', 2),
    'monthly': ('absoluteMonthly', 1),
    'annual': ('absoluteYearly', 1),
    'daily': ('daily', 1),
}


# ─── Calendario ───────────────────────────────────────────────────────────────

def _upsert_event(oid, title, date_str, start_time, end_time, location, description,
                  category, rrule=None, rrule_until=None, is_all_day=False,
                  outlook_last_modified=None) -> bool:
    item_id = _item_id('event', oid)
    now = datetime.utcnow().isoformat()
    meta = {
        'title': title, 'date': date_str, 'start_time': start_time, 'end_time': end_time,
        'location': location, 'description': description, 'category': category,
        'rrule': rrule, 'rrule_until': rrule_until, 'is_all_day': is_all_day,
        'outlook_id': oid, 'outlook_last_modified': outlook_last_modified,
        'source': 'outlook', 'updated_at': now,
    }
    return vault_upsert(item_id, 'event', title, meta)


def _build_event_body(meta: dict) -> dict:
    body: dict = {
        'subject': meta['title'],
        'body': {'contentType': 'text', 'content': meta.get('description') or ''},
    }
    if meta.get('is_all_day') or (not meta.get('start_time') and not meta.get('end_time')):
        body['isAllDay'] = True
        body['start'] = {'dateTime': f"{meta['date']}T00:00:00", 'timeZone': 'UTC'}
        end_dt = datetime.strptime(str(meta['date']), '%Y-%m-%d') + timedelta(days=1)
        body['end'] = {'dateTime': f"{end_dt.strftime('%Y-%m-%d')}T00:00:00", 'timeZone': 'UTC'}
    else:
        start_t = meta.get('start_time') or '00:00'
        end_t = meta.get('end_time') or '00:00'
        if end_t <= start_t:
            h, m = int(start_t[:2]), int(start_t[3:5])
            end_dt_t = datetime(2000, 1, 1, h, m) + timedelta(hours=1)
            end_t = end_dt_t.strftime('%H:%M')
        body['start'] = {'dateTime': f"{meta['date']}T{start_t}:00", 'timeZone': 'Europe/Rome'}
        body['end'] = {'dateTime': f"{meta['date']}T{end_t}:00", 'timeZone': 'Europe/Rome'}
    if meta.get('location'):
        body['location'] = {'displayName': meta['location']}
    if meta.get('rrule'):
        pat_type, interval = RRULE_TO_OUTLOOK.get(meta['rrule'], (None, None))
        if pat_type:
            event_date = datetime.strptime(str(meta['date']), '%Y-%m-%d')
            pattern: dict = {'type': pat_type, 'interval': interval}
            if pat_type == 'weekly':
                day_names = ['monday', 'tuesday', 'wednesday', 'thursday',
                             'friday', 'saturday', 'sunday']
                pattern['daysOfWeek'] = [day_names[event_date.weekday()]]
            elif pat_type == 'absoluteMonthly':
                pattern['dayOfMonth'] = event_date.day
            elif pat_type == 'absoluteYearly':
                pattern['dayOfMonth'] = event_date.day
                pattern['month'] = event_date.month
            if meta.get('rrule_until'):
                rec_range = {'type': 'endDate', 'startDate': str(meta['date']),
                             'endDate': str(meta['rrule_until'])}
            else:
                rec_range = {'type': 'noEnd', 'startDate': str(meta['date'])}
            body['recurrence'] = {'pattern': pattern, 'range': rec_range}
    return body


def sync_calendar(token: str) -> tuple[int, int]:
    created = updated = 0

    # Snapshot pre-sync: eventi locali senza outlook_id da pushare + modificati da pushare
    pre_sync_events = vault_query('event')
    local_only = [e for e in pre_sync_events if not e['meta'].get('outlook_id')]
    local_modified = [
        e for e in pre_sync_events
        if e['meta'].get('outlook_id')
        and e['meta'].get('updated_at')
        and e['meta'].get('outlook_last_modified')
        and e['meta']['updated_at'] > e['meta']['outlook_last_modified']
    ]

    # ── 1. CalendarView — occorrenze concrete (singoli + espansioni ricorrenti) ─
    # Graph espande automaticamente le ricorrenze: nessuna logica rrule locale necessaria.
    today = date.today()
    active_outlook_ids: set[str] = set()
    cv_data = graph_get(token, '/me/calendarView', {
        'startDateTime': f'{today}T00:00:00Z',
        'endDateTime': f'{today + timedelta(days=90)}T23:59:59Z',
        '$top': '500',
    })
    for ev in cv_data.get('value', []):
        if ev.get('type') == 'seriesMaster':
            continue  # vogliamo solo occorrenze concrete
        oid = ev['id']
        active_outlook_ids.add(oid)
        title = ev.get('subject') or '(senza titolo)'
        date_str, start_time = parse_datetime(ev['start'].get('dateTime') or ev['start'].get('date', ''))
        _, end_time = parse_datetime(ev['end'].get('dateTime') or ev['end'].get('date', ''))
        location = ev.get('location', {}).get('displayName') or None
        description = clean_html(ev.get('body', {}).get('content'))
        categories = ev.get('categories', [])
        category = categories[0].lower() if categories else 'personale'

        if DRY_RUN:
            existing = vault_get(_item_id('event', oid))
            log(f'  [DRY] {"UPDATE" if existing else "CREATE"} [{ev.get("type","?")}]: {date_str} {title}')
            created += 0 if existing else 1
            updated += 1 if existing else 0
            continue

        is_all_day = ev.get('isAllDay', False)
        outlook_lm = ev.get('lastModifiedDateTime', '')[:19] if ev.get('lastModifiedDateTime') else None
        is_new = _upsert_event(oid, title, date_str, start_time, end_time, location, description,
                               category, is_all_day=is_all_day, outlook_last_modified=outlook_lm)
        created += 1 if is_new else 0
        updated += 0 if is_new else 1

    # ── 2. Push locale → Outlook ─────────────────────────────────────────────
    push_created = push_updated = 0

    for item in local_only:
        m = item['meta']
        if DRY_RUN:
            log(f'  [DRY] PUSH CREATE: {m.get("date")} {m.get("title")}')
            push_created += 1
            continue
        try:
            result = graph_post(token, '/me/events', _build_event_body(m))
            new_oid = result.get('id')
            if new_oid:
                m['outlook_id'] = new_oid
                m['outlook_last_modified'] = datetime.utcnow().isoformat()
                m['updated_at'] = datetime.utcnow().isoformat()
                vault_upsert(item['id'], 'event', item['body'], m)
                push_created += 1
                log(f'  → PUSH CREATE: {m.get("date")} {m.get("title")}')
        except Exception as e:
            log(f'  [ERR] PUSH CREATE "{m.get("title")}": {e}')

    for item in local_modified:
        m = item['meta']
        if DRY_RUN:
            log(f'  [DRY] PUSH UPDATE: {m.get("date")} {m.get("title")}')
            push_updated += 1
            continue
        try:
            graph_patch(token, f'/me/events/{m["outlook_id"]}', _build_event_body(m))
            m['outlook_last_modified'] = datetime.utcnow().isoformat()
            vault_upsert(item['id'], 'event', item['body'], m)
            push_updated += 1
            log(f'  → PUSH UPDATE: {m.get("date")} {m.get("title")}')
        except Exception as e:
            log(f'  [ERR] PUSH UPDATE "{m.get("title")}": {e}')

    if push_created or push_updated:
        log(f'  Locale→Outlook: {push_created} creati, {push_updated} aggiornati')

    # ── 3. Cancellazioni: rimuovi da vault ciò che non è più in Outlook ───────
    if not DRY_RUN:
        del_count = 0
        for item in pre_sync_events:
            oid = item['meta'].get('outlook_id')
            if oid and oid not in active_outlook_ids:
                vault_delete(item['id'])
                del_count += 1
        if del_count:
            log(f'  Cancellati da vault.db: {del_count}')

    return created, updated


# ─── Task ────────────────────────────────────────────────────────────────────

def sync_tasks(token: str) -> tuple[int, int]:
    lists_data = graph_get(token, '/me/todo/lists')
    created = updated = 0
    active_outlook_ids: set[str] = set()

    for lst in lists_data.get('value', []):
        list_id = lst['id']
        list_name = lst.get('displayName', '')
        tasks_data = graph_get(token, f'/me/todo/lists/{list_id}/tasks', {'$top': 100})

        for task in tasks_data.get('value', []):
            if task.get('status') == 'completed':
                continue
            oid = task['id']
            title = task.get('title') or '(senza titolo)'
            importance = task.get('importance', 'normal')
            priority = {'high': 'high', 'normal': 'medium', 'low': 'low'}.get(importance, 'medium')
            due_date = None
            if task.get('dueDateTime'):
                dt_str = task['dueDateTime'].get('dateTime', '')
                tz_str = task['dueDateTime'].get('timeZone', 'UTC')
                try:
                    dt = datetime.fromisoformat(dt_str.rstrip('Z').split('.')[0])
                    src_tz = _pytz.timezone(tz_str) if tz_str != 'UTC' else _pytz.utc
                    dt = src_tz.localize(dt).astimezone(_pytz.timezone('Europe/Rome'))
                    due_date = dt.strftime('%Y-%m-%d')
                except Exception:
                    due_date = dt_str[:10]
            description = task.get('body', {}).get('content') or None
            if description:
                description = _re.sub(r'<[^>]+>', '', description).strip() or None
            category = list_name.lower() if list_name.lower() not in ('tasks', 'attività') else ''

            active_outlook_ids.add(oid)
            item_id = _item_id('todo', oid)
            existing = vault_get(item_id)

            if DRY_RUN:
                log(f'  [DRY] {"UPDATE" if existing else "CREATE"} task: {title}')
                created += 0 if existing else 1
                updated += 1 if existing else 0
                continue

            now = datetime.utcnow().isoformat()
            meta = {
                'title': title, 'description': description, 'status': 'pending',
                'priority': priority, 'due_date': due_date, 'category': category,
                'outlook_id': oid, 'updated_at': now,
            }
            is_new = vault_upsert(item_id, 'todo', title, meta)
            created += 1 if is_new else 0
            updated += 0 if is_new else 1

    # Task rimossi da Outlook → cancella da vault
    if not DRY_RUN:
        all_todos = vault_query('todo')
        closed = 0
        for item in all_todos:
            oid = item['meta'].get('outlook_id')
            if oid and oid not in active_outlook_ids:
                vault_delete(item['id'])
                closed += 1
        if closed:
            log(f'  Task cancellati da Outlook: {closed}')

    return created, updated


def push_tasks(token: str) -> int:
    lists_data = graph_get(token, '/me/todo/lists')
    default_list_id = None
    for lst in lists_data.get('value', []):
        if lst.get('isOwner') and lst.get('displayName', '').lower() in ('tasks', 'attività', 'task'):
            default_list_id = lst['id']
            break
    if not default_list_id and lists_data.get('value'):
        default_list_id = lists_data['value'][0]['id']
    if not default_list_id:
        log('  push_tasks: nessuna lista To Do trovata')
        return 0

    priority_map = {'high': 'high', 'medium': 'normal', 'low': 'low'}
    created = 0

    for item in vault_query('todo'):
        m = item['meta']
        if m.get('outlook_id') or m.get('status') != 'pending':
            continue
        if DRY_RUN:
            log(f'  [DRY] CREATE task→Outlook: {m.get("title")}')
            created += 1
            continue
        importance = priority_map.get(m.get('priority', 'medium'), 'normal')
        body: dict = {'title': m['title'], 'importance': importance}
        if m.get('due_date'):
            body['dueDateTime'] = {'dateTime': f"{m['due_date']}T00:00:00", 'timeZone': 'Europe/Rome'}
        if m.get('description'):
            body['body'] = {'content': m['description'], 'contentType': 'text'}
        try:
            result = graph_post(token, f'/me/todo/lists/{default_list_id}/tasks', body)
            oid = result.get('id')
            if oid:
                m['outlook_id'] = oid
                m['updated_at'] = datetime.utcnow().isoformat()
                vault_upsert(item['id'], 'todo', item['body'], m)
            created += 1
            log(f'  Task→Outlook: {m["title"]}')
        except Exception as e:
            log(f'  [ERR] push task "{m.get("title")}": {e}')

    return created


# ─── Main ─────────────────────────────────────────────────────────────────────

def main():
    if not all([CLIENT_ID, CLIENT_SECRET, REFRESH_TOKEN]):
        log('Errore: credenziali Outlook mancanti in .env')
        sys.exit(1)
    if DRY_RUN:
        log('Modalità DRY RUN — nessuna modifica')

    log('Ottengo access token...')
    token = get_access_token()
    log('Token ottenuto.')

    log('Sync calendario (prossimi 90 giorni)...')
    ec, eu = sync_calendar(token)
    log(f'  → {ec} creati, {eu} aggiornati')

    log('Sync task (To Do)...')
    tc, tu = sync_tasks(token)
    log(f'  → {tc} creati, {tu} aggiornati')

    log('Push task →Outlook...')
    tpc = push_tasks(token)
    if tpc:
        log(f'  → {tpc} creati')

    total = ec + eu + tc + tu
    log(f'Sync completato. Totale operazioni: {total}')
    if DRY_RUN:
        log('(DRY RUN — nessuna modifica applicata)')


if __name__ == '__main__':
    main()