"""The editorial commands: articles in, subscribers out.

Registered into `app.cli` by one line so the content commands stay in their own file
(Stream D owns this one, Stream A owns `cli.py`). Rules and shapes: `services/editorial.py`,
`services/subscribers.py`, `models/editorial.py`.

    python -m app.cli articles import <file> [--kind article|airport_writeup|category_intro]
        [--airport IATA] [--category NAME] [--slug S] [--title T] [--standfirst S]
        [--author NAME] [--hero-image URL] [--hero-credit TEXT] [--tag WORD ...]
        [--sample] [--publish]
    python -m app.cli articles import --upload <id> ...    # one client hand-in from /todo
    python -m app.cli articles import --todo <id> ...      # every document handed in on one to-do
    python -m app.cli articles list [--all] [--samples]
    python -m app.cli articles publish <slug> [<slug> ...]
    python -m app.cli articles unpublish <slug> [<slug> ...] | --samples
    python -m app.cli subscribers export [--out FILE] [--include-unsubscribed]
    python -m app.cli subscribers stats
    python -m app.cli subscribers unsubscribe <email>

Imports are drafts by default and idempotent on slug: run the same command twice and the
second run replaces the text without touching the status. `--sample` is the exception the
launch plan rests on: the flag is whatever the command says, so `articles unpublish --samples`
before production's database is replaced can never miss a piece. Word files are converted by
`mammoth`; embedded images are counted and left out (they arrive through the shared Drive).
The export is the only way the list leaves the database; it runs here, in the container's
shell, and never through a route.
"""

from __future__ import annotations

import argparse
import os
import sys
from pathlib import Path

from sqlalchemy import func, select, text

from app.db import SessionLocal
from app.models.catalog import Account
from app.models.client import ClientUpload
from app.models.editorial import ARTICLE_KINDS, Article
from app.services import editorial, subscribers
from app.services.uploads import UPLOAD_ROOT


def _author_id(db, name: str | None) -> int | None:
    if not name:
        return None
    account = db.scalar(select(Account).where(Account.display_name == name))
    if account is None:
        known = ", ".join(a.display_name for a in db.scalars(select(Account).order_by(Account.id)))
        raise SystemExit(f"no account named {name!r} (known: {known or 'none'})")
    return account.id


def _sources(db, args: argparse.Namespace) -> list[tuple[bytes, str, str | None, int | None]]:
    """(bytes, name, note, upload_id) for each document the command names."""
    if args.file:
        path = Path(args.file)
        if not path.is_file():
            raise SystemExit(f"not a file: {path}")
        return [(path.read_bytes(), path.name, None, None)]
    if args.upload:
        rows = [db.get(ClientUpload, args.upload)]
        if rows[0] is None:
            raise SystemExit(f"no upload with id {args.upload}")
    else:
        rows = list(
            db.scalars(
                select(ClientUpload)
                .where(ClientUpload.todo_id == args.todo)
                .order_by(ClientUpload.created_at)
            )
        )
        if not rows:
            raise SystemExit(f"to-do {args.todo} has no uploads")
    out = []
    for u in rows:
        if u.kind == "image" or u.content_type == "application/pdf":
            print(
                f"skipping upload {u.id} ({u.original_name}): {u.content_type} is not text we can import"
            )
            continue
        path = UPLOAD_ROOT / str(u.todo_id) / u.stored_name
        if not path.is_file():
            print(f"skipping upload {u.id} ({u.original_name}): file missing on disk")
            continue
        out.append((path.read_bytes(), u.original_name, u.note, u.id))
    return out


def next_sequence_value(last_value: int | None, start_value: int, increment_by: int) -> int:
    """What `nextval` will hand out, from what `pg_sequences` shows. `last_value` is NULL
    there for a sequence that has never been called, and that case is the whole point: a
    sequence sitting unused under rows that already exist hands out an id that is taken."""
    if last_value is None:
        return start_value
    return last_value + increment_by


def _lift_article_sequence(db) -> None:
    """Postgres only, idempotent, raises the id sequence and never lowers it.

    Staging held one article at id 1 with `articles_id_seq` never called, so the first
    `articles import` after a database replacement died on `duplicate key value violates
    unique constraint "articles_pkey"` before a word of the piece was written. A staging
    refresh replays the rows people wrote by natural key, ids and all, and leaves the
    sequence where it was. This command is the only writer to the table, so it checks here
    rather than leaving a `setval` in a launch checklist for somebody to forget at four in
    the morning. It says what it did; a silent repair would be a hidden write."""
    if db.get_bind().dialect.name != "postgresql":
        return
    seq = db.scalar(text("SELECT pg_get_serial_sequence('articles', 'id')"))
    if not seq:
        return
    row = db.execute(
        text(
            "SELECT last_value, start_value, increment_by FROM pg_sequences"
            " WHERE schemaname || '.' || sequencename = :seq"
        ),
        {"seq": seq},
    ).first()
    if row is None:
        return
    highest = db.scalar(select(func.max(Article.id))) or 0
    if next_sequence_value(row.last_value, row.start_value, row.increment_by) > highest:
        return
    db.execute(text("SELECT setval(:seq, :value, true)"), {"seq": seq, "value": highest})
    db.commit()
    print(f"note: {seq} was behind the rows that exist; lifted to {highest}")


def cmd_articles_import(args: argparse.Namespace) -> int:
    if not (args.file or args.upload or args.todo):
        raise SystemExit("name a <file>, --upload <id> or --todo <id>")
    if args.todo and (args.slug or args.title):
        raise SystemExit("--slug and --title apply to one document; import a --todo without them")
    with SessionLocal() as db:
        _lift_article_sequence(db)
        author_id = _author_id(db, args.author)
        sources = _sources(db, args)
        if not sources:
            return 1
        for data, name, note, upload_id in sources:
            doc = editorial.read_document(data, name)
            try:
                result = editorial.upsert_article(
                    db,
                    doc,
                    kind=args.kind,
                    slug=args.slug,
                    title=args.title or (None if doc.title else note),
                    standfirst=args.standfirst,
                    airport_code=args.airport,
                    category=args.category,
                    author_id=author_id,
                    hero_image=args.hero_image,
                    hero_credit=args.hero_credit,
                    sample=args.sample,
                    tags=args.tag if args.tag is not None else None,
                    source_upload_id=upload_id,
                    publish=args.publish,
                    fallback_name=name,
                )
            except ValueError as exc:
                print(f"{name}: {exc}", file=sys.stderr)
                return 2
            verb = "created" if result.created else "updated"
            mark = " sample" if args.sample else ""
            line = f'{verb} {result.slug} ({result.status}{mark}) "{result.title}" from {name}'
            if result.dropped_images:
                line += f"; {result.dropped_images} embedded image(s) left out (place them under public/ and reference them)"
            print(line)
            for warning in result.warnings:
                print(f"  note: {warning}")
    return 0


def cmd_articles_list(args: argparse.Namespace) -> int:
    with SessionLocal() as db:
        stmt = select(Article).order_by(
            Article.kind, Article.status.desc(), Article.updated_at.desc()
        )
        if not args.all and not args.samples:
            stmt = stmt.where(Article.status == "published")
        if args.samples:
            stmt = stmt.where(Article.sample.is_(True))
        rows = db.scalars(stmt).all()
        if not rows:
            if args.samples:
                print("no sample articles")
            else:
                print("no articles" + ("" if args.all else " published (use --all for drafts)"))
            return 0
        for a in rows:
            key = a.airport_code or a.category or ""
            when = a.published_at.strftime("%Y-%m-%d") if a.published_at else "-"
            flag = "sample" if a.sample else "      "
            tags = ",".join(a.tags or [])
            print(
                f'{a.status:9s} {flag} {a.kind:16s} {key:12s} {when}  {a.slug}  "{a.title}"'
                + (f"  [{tags}]" if tags else "")
            )
    return 0


def _set_status(slugs: list[str], status: str) -> int:
    code = 0
    with SessionLocal() as db:
        for slug in slugs:
            row = editorial.article_by_slug(db, slug, published_only=False)
            if row is None:
                print(f"no article with slug {slug}", file=sys.stderr)
                code = 1
                continue
            editorial.set_status(row, status)
            db.commit()
            print(f"{slug}: {status}")
    return code


def cmd_articles_publish(args: argparse.Namespace) -> int:
    return _set_status(args.slug, "published")


def cmd_articles_unpublish(args: argparse.Namespace) -> int:
    """`--samples` is the launch plan's step: every sample row to draft in one command, so the
    ten pieces the client looked at on staging cannot ride a database replacement to
    production. It reads the column, never a list of slugs pasted into a checklist."""
    if args.samples:
        if args.slug:
            raise SystemExit("--samples takes every sample row; name slugs or --samples, not both")
        with SessionLocal() as db:
            slugs = [a.slug for a in editorial.sample_articles(db)]
        if not slugs:
            print("no sample articles")
            return 0
        return _set_status(slugs, "draft")
    if not args.slug:
        raise SystemExit("name at least one slug, or --samples")
    return _set_status(args.slug, "draft")


def cmd_subscribers_export(args: argparse.Namespace) -> int:
    with SessionLocal() as db:
        text = subscribers.export_csv(db, include_unsubscribed=args.include_unsubscribed)
    if args.out:
        path = Path(args.out)
        # Created mode 600 from the first byte, never through a symlink: this file is the list.
        fd = os.open(path, os.O_WRONLY | os.O_CREAT | os.O_TRUNC | os.O_NOFOLLOW, 0o600)
        with os.fdopen(fd, "w") as fh:
            fh.write(text)
        os.chmod(path, 0o600)
        print(f"wrote {text.count(chr(10)) - 1} row(s) to {path}")
    else:
        sys.stdout.write(text)
    return 0


def cmd_subscribers_stats(_: argparse.Namespace) -> int:
    with SessionLocal() as db:
        for key, value in subscribers.stats(db).items():
            print(f"{key:24s} {value}")
    return 0


def cmd_subscribers_unsubscribe(args: argparse.Namespace) -> int:
    with SessionLocal() as db:
        try:
            changed = subscribers.unsubscribe(db, args.email)
        except subscribers.InvalidSubscription as exc:
            raise SystemExit(str(exc)) from exc
    print("unsubscribed" if changed else "no active subscription for that address")
    return 0


def register(sub: argparse._SubParsersAction) -> None:
    articles = sub.add_parser("articles", help="editorial text: import hand-ins, list, publish")
    asub = articles.add_subparsers(dest="articles_command", required=True)

    imp = asub.add_parser(
        "import", help="a Markdown, text or Word file into the articles table (draft)"
    )
    imp.add_argument("file", nargs="?", help="path to a .md, .txt or .docx file")
    imp.add_argument("--upload", type=int, help="a client_uploads id from /todo instead of a path")
    imp.add_argument("--todo", type=int, help="every document handed in on this to-do id")
    imp.add_argument("--kind", default="article", choices=ARTICLE_KINDS)
    imp.add_argument("--airport", help="IATA code (airport_writeup)")
    imp.add_argument("--category", help="our category name (category_intro)")
    imp.add_argument("--slug", help="override the slug derived from the title")
    imp.add_argument("--title", help="override the document's own title")
    imp.add_argument("--standfirst", help="the one-line summary under the title")
    imp.add_argument("--author", help="account display name (e.g. rian)")
    imp.add_argument("--hero-image", help="site-relative or https image address")
    imp.add_argument("--hero-credit", help="the attribution printed under the hero picture")
    imp.add_argument(
        "--tag",
        action="append",
        help="a short lowercase label for the article centre's filter; repeatable, at most 8",
    )
    imp.add_argument(
        "--sample",
        action="store_true",
        help="the Professor's own sample, marked on the site and dropped before the launch",
    )
    imp.add_argument(
        "--publish", action="store_true", help="publish now instead of leaving a draft"
    )
    imp.set_defaults(func=cmd_articles_import)

    lst = asub.add_parser("list", help="published articles (--all includes drafts)")
    lst.add_argument("--all", action="store_true")
    lst.add_argument("--samples", action="store_true", help="only the samples, drafts included")
    lst.set_defaults(func=cmd_articles_list)

    pub = asub.add_parser("publish", help="make articles public")
    pub.add_argument("slug", nargs="+")
    pub.set_defaults(func=cmd_articles_publish)

    unpub = asub.add_parser("unpublish", help="take articles back to draft")
    unpub.add_argument("slug", nargs="*")
    unpub.add_argument(
        "--samples", action="store_true", help="every sample row (the launch plan's step)"
    )
    unpub.set_defaults(func=cmd_articles_unpublish)

    subs = sub.add_parser("subscribers", help="email capture: export the list (owner only), counts")
    ssub = subs.add_subparsers(dest="subscribers_command", required=True)

    exp = ssub.add_parser(
        "export", help="CSV of active subscribers to stdout or --out FILE (mode 600)"
    )
    exp.add_argument("--out", help="write here instead of stdout")
    exp.add_argument("--include-unsubscribed", action="store_true")
    exp.set_defaults(func=cmd_subscribers_export)

    st = ssub.add_parser("stats", help="counts by source; no addresses")
    st.set_defaults(func=cmd_subscribers_stats)

    uns = ssub.add_parser("unsubscribe", help="record a withdrawal for one address")
    uns.add_argument("email")
    uns.set_defaults(func=cmd_subscribers_unsubscribe)