import os, sqlite3
from datetime import datetime, timezone
from buffer_bridge import BufferError, channels
from buffer_publish import create_post

TOKEN_ENV = 'BUFFER_AS_API_KEY'


def ensure_schema(db_path):
    c=sqlite3.connect(db_path)
    cols={r[1] for r in c.execute('pragma table_info(content_items)')}
    for name in ('buffer_post_id','buffer_channel_id','buffer_synced_at','buffer_error','buffer_mode','buffer_due_at'):
        if name not in cols:
            c.execute(f'alter table content_items add column {name} TEXT')
    c.commit(); c.close()


def _token():
    token=(os.environ.get(TOKEN_ENV) or '').strip()
    if not token:
        raise BufferError('ASO Buffer API-Key fehlt')
    return token


def buffer_status():
    try:
        return {'ok':True,'channels':channels(_token())}
    except BufferError as e:
        return {'ok':False,'error':str(e)}

def handoff(db_path, data):
    sid=(data.get('signal_id') or '').strip()
    platform=(data.get('platform') or 'facebook').strip().lower()
    body=str(data.get('body') or '')
    if platform == 'reels':
        return {'ok':False,'error':'Reels werden nicht als normaler Instagram-Post uebergeben'}
    c=sqlite3.connect(db_path); c.row_factory=sqlite3.Row
    row=c.execute('select * from content_items where signal_id=? and platform=?',(sid,platform)).fetchone()
    if not row:
        c.close(); return {'ok':False,'error':'Entwurf nicht gefunden'}
    d=dict(row)
    action=(data.get('action') or 'draft').lower()
    media_url=(data.get('media_url') or d.get('post_media_url') or d.get('image_url') or '').strip() or None
    if media_url and media_url.startswith('/'):
        media_url=(os.environ.get('ASOL_PUBLIC_URL') or 'https://sma-asol.agentsolutions-mallorca.com').rstrip('/') + media_url
    media_kind=(data.get('media_kind') or 'image').lower()
    due_at=data.get('due_at') or None
    first_comment=None
    link_url=(data.get('link_url') or '').strip() or None
    post_type=(data.get('post_type') or 'post').lower()
    try:
        res=create_post(_token(),platform,body,action=action,due_at=due_at,
                        media_url=media_url,media_kind=media_kind,
                        first_comment=first_comment,link_url=link_url,
                        post_type=post_type)
        now=datetime.now(timezone.utc).isoformat()
        c.execute('update content_items set body=?,updated_at=?,buffer_post_id=?,buffer_channel_id=?,buffer_synced_at=?,buffer_error=NULL,buffer_mode=?,buffer_due_at=? where signal_id=? and platform=?',
                  (body,now,res['post_id'],res['channel_id'],now,action,res.get('due_at') or due_at,sid,platform))
        c.commit(); c.close()
        return {'ok':True,'action':action,'platform':platform,**res}
    except BufferError as e:
        c.execute('update content_items set buffer_error=? where signal_id=? and platform=?',(str(e)[:500],sid,platform))
        c.commit(); c.close()
        return {'ok':False,'error':str(e)}