#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""OK ITHAKI V249387 public creator link + publication workspace service.

This module is intentionally isolated from the main relay database writes.
It stores only creator public-link settings and anonymous aggregate visits in
its own SQLite database. Public posts are read from the existing shared relay
SQLite database and are never copied or modified here.
"""
from __future__ import annotations

from pathlib import Path
from typing import Any
from urllib.parse import quote
import hashlib
import html
import json
import os
import re
import secrets
import sqlite3
import time
import unicodedata
import uuid

VERSION = "V249387-public-creator-link-publication-workspace"
BASE_DIR = Path(__file__).resolve().parent
DATA_DIR = BASE_DIR / "data"
DB_PATH = DATA_DIR / "creator_public_v249387.sqlite3"
MAIN_DB_PATH = DATA_DIR / "ithaki_full_relay_pro.sqlite3"
PUBLIC_ORIGIN = os.getenv("ITHAKI_PUBLIC_ORIGIN", "https://ok-ithaki.gr").rstrip("/")
MAX_BIO = 420
MAX_SLUG = 40
RESERVED = {
    "api", "admin", "profile", "feed", "messages", "message", "calls", "call",
    "login", "logout", "register", "support", "privacy", "terms", "downloads",
    "site", "ok-ithaki", "ithaki", "creator", "creators", "system", "root",
}

_GREEK_MAP = str.maketrans({
    "α":"a","β":"v","γ":"g","δ":"d","ε":"e","ζ":"z","η":"i","θ":"th",
    "ι":"i","κ":"k","λ":"l","μ":"m","ν":"n","ξ":"x","ο":"o","π":"p",
    "ρ":"r","σ":"s","ς":"s","τ":"t","υ":"y","φ":"f","χ":"ch","ψ":"ps","ω":"o",
})


def _now() -> float:
    return time.time()


def _clean(value: Any, limit: int = 500) -> str:
    return str(value or "").replace("\x00", " ").strip()[:limit]


def _conn() -> sqlite3.Connection:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(str(DB_PATH), timeout=30)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA synchronous=NORMAL")
    con.execute("PRAGMA busy_timeout=30000")
    return con


def _main_conn() -> sqlite3.Connection:
    con = sqlite3.connect(str(MAIN_DB_PATH), timeout=15)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA query_only=ON")
    con.execute("PRAGMA busy_timeout=15000")
    return con


def init_db() -> None:
    with _conn() as con:
        con.executescript(
            """
            CREATE TABLE IF NOT EXISTS creator_public_profiles_v249387(
              room_id TEXT NOT NULL,
              device_id TEXT NOT NULL,
              slug TEXT NOT NULL UNIQUE,
              display_name TEXT NOT NULL DEFAULT '',
              avatar TEXT NOT NULL DEFAULT '',
              bio TEXT NOT NULL DEFAULT '',
              enabled INTEGER NOT NULL DEFAULT 1,
              created_at REAL NOT NULL,
              updated_at REAL NOT NULL,
              PRIMARY KEY(room_id,device_id)
            );
            CREATE INDEX IF NOT EXISTS idx_creator_public_slug_v249387
              ON creator_public_profiles_v249387(slug,enabled);

            CREATE TABLE IF NOT EXISTS creator_public_visits_v249387(
              event_id TEXT PRIMARY KEY,
              slug TEXT NOT NULL,
              source TEXT NOT NULL DEFAULT 'direct',
              visitor_hash TEXT NOT NULL,
              day_key TEXT NOT NULL,
              created_at REAL NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_creator_public_visits_slug_v249387
              ON creator_public_visits_v249387(slug,created_at DESC);
            CREATE UNIQUE INDEX IF NOT EXISTS idx_creator_public_unique_day_v249387
              ON creator_public_visits_v249387(slug,visitor_hash,day_key);
            """
        )
        con.commit()


def _transliterate(value: str) -> str:
    text = unicodedata.normalize("NFD", _clean(value, 160).casefold())
    text = "".join(ch for ch in text if unicodedata.category(ch) != "Mn")
    return text.translate(_GREEK_MAP)


def normalize_slug(value: Any) -> str:
    raw = _transliterate(_clean(value, 160))
    raw = raw.replace("@", "")
    raw = re.sub(r"[^a-z0-9._-]+", "-", raw)
    raw = re.sub(r"[-_.]{2,}", "-", raw).strip("-._")
    raw = raw[:MAX_SLUG]
    if len(raw) < 3:
        raise ValueError("Το δημόσιο όνομα χρειάζεται τουλάχιστον 3 λατινικούς χαρακτήρες ή αριθμούς.")
    if raw in RESERVED:
        raise ValueError("Αυτό το δημόσιο όνομα είναι δεσμευμένο.")
    return raw


def suggest_slug(name: Any, device_id: Any = "") -> str:
    try:
        base = normalize_slug(name)
    except Exception:
        base = "creator"
    if base in RESERVED or len(base) < 3:
        base = "creator"
    suffix = hashlib.sha256(_clean(device_id, 180).encode("utf-8", "ignore")).hexdigest()[:5]
    candidate = base[:MAX_SLUG]
    with _conn() as con:
        if not con.execute("SELECT 1 FROM creator_public_profiles_v249387 WHERE slug=?", (candidate,)).fetchone():
            return candidate
        candidate = f"{base[:max(3,MAX_SLUG-6)]}-{suffix}"
        if not con.execute("SELECT 1 FROM creator_public_profiles_v249387 WHERE slug=?", (candidate,)).fetchone():
            return candidate
        return f"creator-{secrets.token_hex(3)}"[:MAX_SLUG]


def _validate_professional(payload: dict[str, Any]) -> dict[str, Any]:
    from account_security_v249382 import validate_session
    return validate_session(dict(payload or {}))


def _public_row(row: sqlite3.Row | None, *, suggestion: str = "") -> dict[str, Any]:
    if row is None:
        return {
            "exists": False,
            "enabled": False,
            "slug": suggestion,
            "public_url": f"{PUBLIC_ORIGIN}/@{suggestion}" if suggestion else "",
            "display_name": "",
            "avatar": "",
            "bio": "",
            "updated_at": 0,
        }
    d = dict(row)
    slug = _clean(d.get("slug"), MAX_SLUG)
    return {
        "exists": True,
        "enabled": bool(d.get("enabled")),
        "slug": slug,
        "public_url": f"{PUBLIC_ORIGIN}/@{slug}",
        "display_name": _clean(d.get("display_name"), 80),
        "avatar": _clean(d.get("avatar"), 1_800_000),
        "bio": _clean(d.get("bio"), MAX_BIO),
        "created_at": float(d.get("created_at") or 0),
        "updated_at": float(d.get("updated_at") or 0),
    }


def status(payload: dict[str, Any]) -> dict[str, Any]:
    account = _validate_professional(payload)
    room_id = _clean(payload.get("room_id"), 120) or "ithaki-phone-test"
    device_id = _clean(payload.get("device_id"), 180)
    with _conn() as con:
        row = con.execute(
            "SELECT * FROM creator_public_profiles_v249387 WHERE room_id=? AND device_id=?",
            (room_id, device_id),
        ).fetchone()
    suggestion = suggest_slug(account.get("account_name") or "creator", device_id)
    return {
        "ok": True,
        "version": VERSION,
        "profile": _public_row(row, suggestion=suggestion),
        "account_name": _clean(account.get("account_name"), 80),
        "avatar": _clean(account.get("avatar"), 1_800_000),
        "professional_session_valid": True,
        "privacy": {
            "email_exposed": False,
            "phone_exposed": False,
            "device_id_exposed": False,
            "private_messages_exposed": False,
            "friends_exposed": False,
        },
    }


def save_profile(payload: dict[str, Any]) -> dict[str, Any]:
    account = _validate_professional(payload)
    room_id = _clean(payload.get("room_id"), 120) or "ithaki-phone-test"
    device_id = _clean(payload.get("device_id"), 180)
    slug = normalize_slug(payload.get("slug"))
    bio = _clean(payload.get("bio"), MAX_BIO)
    enabled = 1 if bool(payload.get("enabled", True)) else 0
    display_name = _clean(account.get("account_name"), 80) or "Δημιουργός"
    avatar = _clean(account.get("avatar"), 1_800_000)
    ts = _now()
    with _conn() as con:
        conflict = con.execute(
            "SELECT room_id,device_id FROM creator_public_profiles_v249387 WHERE slug=?",
            (slug,),
        ).fetchone()
        if conflict and (str(conflict["room_id"]) != room_id or str(conflict["device_id"]) != device_id):
            raise ValueError("Το δημόσιο link χρησιμοποιείται ήδη από άλλον δημιουργό.")
        con.execute(
            """
            INSERT INTO creator_public_profiles_v249387(
              room_id,device_id,slug,display_name,avatar,bio,enabled,created_at,updated_at
            ) VALUES(?,?,?,?,?,?,?,?,?)
            ON CONFLICT(room_id,device_id) DO UPDATE SET
              slug=excluded.slug,display_name=excluded.display_name,avatar=excluded.avatar,
              bio=excluded.bio,enabled=excluded.enabled,updated_at=excluded.updated_at
            """,
            (room_id, device_id, slug, display_name, avatar, bio, enabled, ts, ts),
        )
        con.commit()
        row = con.execute(
            "SELECT * FROM creator_public_profiles_v249387 WHERE room_id=? AND device_id=?",
            (room_id, device_id),
        ).fetchone()
    return {"ok": True, "version": VERSION, "saved": True, "profile": _public_row(row)}


def analytics(payload: dict[str, Any]) -> dict[str, Any]:
    _validate_professional(payload)
    room_id = _clean(payload.get("room_id"), 120) or "ithaki-phone-test"
    device_id = _clean(payload.get("device_id"), 180)
    with _conn() as con:
        row = con.execute(
            "SELECT * FROM creator_public_profiles_v249387 WHERE room_id=? AND device_id=?",
            (room_id, device_id),
        ).fetchone()
        if not row:
            return {"ok": True, "version": VERSION, "profile": None, "totals": {"all": 0, "days_30": 0, "days_7": 0, "today": 0, "unique_30": 0}, "sources": []}
        slug = str(row["slug"])
        now = _now()
        def count_since(seconds: float) -> int:
            r = con.execute("SELECT COUNT(*) c FROM creator_public_visits_v249387 WHERE slug=? AND created_at>=?", (slug, now-seconds)).fetchone()
            return int(r["c"] or 0)
        all_count = int(con.execute("SELECT COUNT(*) c FROM creator_public_visits_v249387 WHERE slug=?", (slug,)).fetchone()["c"] or 0)
        unique_30 = int(con.execute("SELECT COUNT(DISTINCT visitor_hash) c FROM creator_public_visits_v249387 WHERE slug=? AND created_at>=?", (slug, now-30*86400)).fetchone()["c"] or 0)
        sources = [dict(r) for r in con.execute(
            "SELECT source,COUNT(*) visits,COUNT(DISTINCT visitor_hash) unique_visitors FROM creator_public_visits_v249387 WHERE slug=? AND created_at>=? GROUP BY source ORDER BY visits DESC",
            (slug, now-30*86400),
        ).fetchall()]
    return {
        "ok": True,
        "version": VERSION,
        "profile": _public_row(row),
        "totals": {
            "all": all_count,
            "days_30": count_since(30*86400),
            "days_7": count_since(7*86400),
            "today": count_since(86400),
            "unique_30": unique_30,
        },
        "sources": sources,
    }


def _visit_salt() -> str:
    configured = os.getenv("ITHAKI_CREATOR_VISIT_SALT", "").strip()
    if configured:
        return configured
    salt_path = DATA_DIR / "creator_visit_salt_v249387.bin"
    try:
        DATA_DIR.mkdir(parents=True, exist_ok=True)
        if not salt_path.exists():
            flags = os.O_WRONLY | os.O_CREAT | os.O_EXCL
            fd = os.open(str(salt_path), flags, 0o600)
            try:
                os.write(fd, secrets.token_bytes(32))
            finally:
                os.close(fd)
        return salt_path.read_bytes().hex()
    except Exception:
        # Last-resort per-process salt: raw IP is still never stored.
        return secrets.token_hex(32)


def _visitor_hash(ip: str, user_agent: str) -> str:
    day = time.strftime("%Y-%m-%d", time.gmtime())
    raw = f"{day}|{_visit_salt()}|{_clean(ip,120)}|{_clean(user_agent,500)}".encode("utf-8", "ignore")
    return hashlib.sha256(raw).hexdigest()


def record_visit(slug: str, source: str, ip: str, user_agent: str) -> None:
    source = re.sub(r"[^a-z0-9_-]+", "", _clean(source, 40).casefold()) or "direct"
    if source not in {"direct", "tiktok", "instagram", "youtube", "facebook", "qr", "share", "other"}:
        source = "other"
    day = time.strftime("%Y-%m-%d", time.gmtime())
    try:
        with _conn() as con:
            con.execute(
                "INSERT OR IGNORE INTO creator_public_visits_v249387(event_id,slug,source,visitor_hash,day_key,created_at) VALUES(?,?,?,?,?,?)",
                ("cpv_" + uuid.uuid4().hex, slug, source, _visitor_hash(ip, user_agent), day, _now()),
            )
            con.commit()
    except Exception:
        pass


def _profile_by_slug(slug: str) -> sqlite3.Row | None:
    with _conn() as con:
        return con.execute(
            "SELECT * FROM creator_public_profiles_v249387 WHERE slug=? AND enabled=1",
            (slug,),
        ).fetchone()


def _parse_post(row: sqlite3.Row) -> dict[str, Any]:
    text = str(row["text"] or "")
    body = text
    media: dict[str, Any] = {}
    try:
        obj = json.loads(text)
        if isinstance(obj, dict):
            body = str(obj.get("body") or obj.get("caption") or obj.get("text") or "")
            maybe = obj.get("media")
            if isinstance(maybe, dict):
                media = maybe
    except Exception:
        pass
    url = _clean(media.get("url") or media.get("file_url") or media.get("media_url"), 2000)
    kind = _clean(media.get("kind") or row["post_kind"], 40).casefold()
    return {
        "post_id": _clean(row["post_id"], 160),
        "body": body[:1200],
        "kind": kind,
        "media_url": url,
        "mime_type": _clean(media.get("mime_type"), 100),
        "filename": _clean(media.get("filename"), 240),
        "created_at": float(row["created_at"] or 0),
    }


def public_data(slug_value: Any) -> dict[str, Any] | None:
    try:
        slug = normalize_slug(slug_value)
    except Exception:
        return None
    row = _profile_by_slug(slug)
    if not row:
        return None
    posts: list[dict[str, Any]] = []
    try:
        with _main_conn() as con:
            result = con.execute(
                """
                SELECT post_id,text,post_kind,created_at FROM flow_posts
                WHERE room_id=? AND sender_id=? AND visibility='public' AND anonymous=0
                ORDER BY created_at DESC LIMIT 36
                """,
                (str(row["room_id"]), str(row["device_id"])),
            ).fetchall()
            posts = [_parse_post(item) for item in result]
    except Exception:
        posts = []
    return {"profile": _public_row(row), "posts": posts}


def _media_html(post: dict[str, Any]) -> str:
    url = html.escape(str(post.get("media_url") or ""), quote=True)
    kind = str(post.get("kind") or "")
    if not url:
        return ""
    if "video" in kind:
        return f'<video controls playsinline preload="metadata" src="{url}"></video>'
    if "photo" in kind or "image" in kind or str(post.get("mime_type") or "").startswith("image/"):
        return f'<img loading="lazy" src="{url}" alt="Δημοσίευση δημιουργού">'
    return f'<a class="file" href="{url}" target="_blank" rel="noopener">📎 {html.escape(str(post.get("filename") or "Αρχείο"))}</a>'


def public_page(slug_value: Any, *, source: str = "direct", ip: str = "", user_agent: str = "") -> str | None:
    data = public_data(slug_value)
    if not data:
        return None
    profile = data["profile"]
    slug = profile["slug"]
    record_visit(slug, source, ip, user_agent)
    name = html.escape(profile["display_name"] or "Δημιουργός")
    bio = html.escape(profile["bio"] or "Δημόσιο προφίλ δημιουργού στην OK ITHAKI")
    avatar = str(profile.get("avatar") or "")
    if avatar.startswith("animal:"):
        avatar_html = f'<div class="avatar emoji">{html.escape(avatar[7:] or "🦉")}</div>'
    elif avatar:
        avatar_html = f'<div class="avatar"><img src="{html.escape(avatar, quote=True)}" alt="{name}"></div>'
    else:
        avatar_html = f'<div class="avatar emoji">{html.escape((profile["display_name"] or "Ι")[0])}</div>'
    cards = []
    for post in data["posts"]:
        body = html.escape(str(post.get("body") or ""))
        date = time.strftime("%d/%m/%Y", time.localtime(float(post.get("created_at") or 0))) if post.get("created_at") else ""
        cards.append(f'<article>{_media_html(post)}<div class="caption">{body or "Δημοσίευση"}</div><small>{date}</small></article>')
    cards_html = "".join(cards) or '<div class="empty">Δεν υπάρχουν ακόμη δημόσιες δημοσιεύσεις.</div>'
    canonical = f"{PUBLIC_ORIGIN}/@{quote(slug)}"
    app_link = f"{PUBLIC_ORIGIN}/?open_creator={quote(slug)}&v=249387"
    return f'''<!doctype html>
<html lang="el"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1,viewport-fit=cover">
<title>{name} · OK ITHAKI</title><meta name="description" content="{bio}"><link rel="canonical" href="{canonical}">
<meta property="og:title" content="{name} · OK ITHAKI"><meta property="og:description" content="{bio}"><meta property="og:url" content="{canonical}">
<style>
*{{box-sizing:border-box}}body{{margin:0;background:radial-gradient(circle at 50% -10%,#17354e,#06111c 42%,#02060a);color:#f5fbff;font-family:system-ui,-apple-system,Segoe UI,sans-serif;min-height:100vh}}main{{width:min(980px,94vw);margin:auto;padding:24px 0 80px}}header{{display:flex;align-items:center;gap:15px;padding:18px;border:1px solid rgba(255,211,110,.34);border-radius:26px;background:rgba(5,20,32,.88);box-shadow:0 24px 70px rgba(0,0,0,.45)}}.avatar{{width:88px;height:88px;border-radius:28px;overflow:hidden;display:grid;place-items:center;background:#10283a;border:2px solid #e7c365;font-size:34px;font-weight:900;flex:0 0 auto}}.avatar img{{width:100%;height:100%;object-fit:cover}}h1{{margin:0;color:#ffe29a;font-size:clamp(24px,5vw,38px)}}p{{margin:6px 0;color:#c8d8e4;line-height:1.5}}.actions{{display:flex;gap:9px;flex-wrap:wrap;margin-top:13px}}.actions a{{text-decoration:none;color:#10100a;background:linear-gradient(135deg,#a86e1d,#f4d277);border-radius:999px;padding:11px 17px;font-weight:900}}.actions a.secondary{{color:#fff;background:rgba(255,255,255,.08);border:1px solid rgba(255,255,255,.17)}}.notice{{margin:14px 0;padding:12px;border-radius:16px;background:rgba(62,171,255,.08);border:1px solid rgba(102,201,255,.22);color:#cceeff}}.grid{{display:grid;grid-template-columns:repeat(auto-fit,minmax(240px,1fr));gap:14px;margin-top:18px}}article{{border:1px solid rgba(255,255,255,.13);border-radius:22px;overflow:hidden;background:rgba(5,18,30,.88)}}article img,article video{{display:block;width:100%;aspect-ratio:9/13;object-fit:contain;background:#000}}article .caption{{padding:12px 13px 5px;white-space:pre-wrap;line-height:1.45}}article small{{display:block;padding:0 13px 13px;color:#8fa8b8}}a.file{{display:block;margin:16px;padding:18px;border-radius:14px;background:rgba(255,255,255,.08);color:#fff;text-decoration:none}}.empty{{padding:30px;text-align:center;border:1px dashed rgba(255,255,255,.2);border-radius:20px;color:#a9bdca}}footer{{text-align:center;color:#78909f;margin-top:30px;font-size:12px}}@media(max-width:620px){{header{{align-items:flex-start}}.avatar{{width:72px;height:72px;border-radius:22px}}}}
</style></head><body><main><header>{avatar_html}<div><h1>{name}</h1><p>@{html.escape(slug)}</p><p>{bio}</p><div class="actions"><a href="{app_link}">Άνοιγμα στην ΙΘΑΚΗ</a><a class="secondary" href="{PUBLIC_ORIGIN}">OK ITHAKI</a></div></div></header><div class="notice">Για προσωπικό μήνυμα ή κλήση χρειάζεται εγγραφή στην ΙΘΑΚΗ, αίτημα φιλίας και αποδοχή από τον δημιουργό.</div><section class="grid">{cards_html}</section><footer>Δημόσια εμφανίζονται μόνο το όνομα, το avatar, η περιγραφή και οι δημόσιες μη ανώνυμες δημοσιεύσεις.</footer></main></body></html>'''


def diagnostics() -> dict[str, Any]:
    init_db()
    with _conn() as con:
        profiles = int(con.execute("SELECT COUNT(*) c FROM creator_public_profiles_v249387").fetchone()["c"] or 0)
        enabled = int(con.execute("SELECT COUNT(*) c FROM creator_public_profiles_v249387 WHERE enabled=1").fetchone()["c"] or 0)
        visits = int(con.execute("SELECT COUNT(*) c FROM creator_public_visits_v249387").fetchone()["c"] or 0)
        integrity = con.execute("PRAGMA integrity_check").fetchone()[0]
    return {
        "ok": integrity == "ok",
        "version": VERSION,
        "database_integrity": integrity,
        "profiles": profiles,
        "enabled_profiles": enabled,
        "anonymous_unique_daily_visits": visits,
        "public_route": "/@{creator_slug}",
        "creator_link_actions": ["copy", "share", "qr", "analytics"],
        "publication_workspace": ["import", "save_file", "preview", "destination", "studio"],
        "private_data_exposed": False,
        "main_database_modified": False,
    }


init_db()
