"""Import Laravel testé sur une base SQLite reproduisant le schéma des migrations Laravel."""

import sqlite3

import bcrypt
import pytest
from django.contrib.auth import authenticate

from apps.accounts.models import Role, User
from apps.editorial.models import Article, Status
from apps.engagement.models import Comment
from apps.legacy.importer import LaravelImporter, convert_password, rewrite_storage_urls
from apps.medialib.models import MediaAsset

pytestmark = pytest.mark.django_db

SCHEMA = """
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, role TEXT, email_verified_at TEXT, password TEXT,
  remember_token TEXT, created_at TEXT, updated_at TEXT);
CREATE TABLE categories (id INTEGER PRIMARY KEY, nom TEXT, slug TEXT, description TEXT, est_dans_menu INTEGER, ordre INTEGER, created_at TEXT, updated_at TEXT);
CREATE TABLE tags (id INTEGER PRIMARY KEY, nom TEXT, slug TEXT, created_at TEXT, updated_at TEXT);
CREATE TABLE articles (id INTEGER PRIMARY KEY, titre TEXT, slug TEXT, resume TEXT, contenu TEXT, image_principale TEXT, legende_image TEXT,
  auteur_id INTEGER, categorie_id INTEGER, statut TEXT, vues INTEGER, est_a_la_une INTEGER, publie_le TEXT, created_at TEXT, updated_at TEXT, deleted_at TEXT);
CREATE TABLE article_tag (id INTEGER PRIMARY KEY, article_id INTEGER, tag_id INTEGER, created_at TEXT, updated_at TEXT);
CREATE TABLE article_images (id INTEGER PRIMARY KEY, article_id INTEGER, image_path TEXT, legende TEXT, created_at TEXT, updated_at TEXT);
CREATE TABLE comments (id INTEGER PRIMARY KEY, article_id INTEGER, user_id INTEGER, nom TEXT, email TEXT, contenu TEXT, approuve INTEGER,
  parent_id INTEGER, created_at TEXT, updated_at TEXT);
CREATE TABLE comment_votes (id INTEGER PRIMARY KEY, comment_id INTEGER, user_id INTEGER, ip_address TEXT, vote TEXT, created_at TEXT, updated_at TEXT);
CREATE TABLE reactions (id INTEGER PRIMARY KEY, article_id INTEGER, user_id INTEGER, ip_address TEXT, reaction_type TEXT, created_at TEXT, updated_at TEXT);
CREATE TABLE settings (id INTEGER PRIMARY KEY, key TEXT, value TEXT, label TEXT, type TEXT, "group" TEXT, created_at TEXT, updated_at TEXT);
CREATE TABLE messages (id INTEGER PRIMARY KEY, nom TEXT, email TEXT, sujet TEXT, message TEXT, lu INTEGER, created_at TEXT, updated_at TEXT);
CREATE TABLE subscribers (id INTEGER PRIMARY KEY, email TEXT, is_active INTEGER, created_at TEXT, updated_at TEXT);
CREATE TABLE publicites (id INTEGER PRIMARY KEY, titre TEXT, image_path TEXT, lien TEXT, position TEXT, actif INTEGER, date_debut TEXT,
  date_fin TEXT, impressions INTEGER, clics INTEGER, created_at TEXT, updated_at TEXT);
"""
T = "2026-02-01 10:00:00"


@pytest.fixture
def legacy_db(tmp_path):
    db = sqlite3.connect(":memory:")
    db.executescript(SCHEMA)
    pw = bcrypt.hashpw(b"secret-laravel", bcrypt.gensalt(4)).decode().replace("$2b$", "$2y$")
    db.executemany("INSERT INTO users VALUES (?,?,?,?,?,?,?,?,?)", [
        (1, "Admin", "Admin@Label.bj", "admin", None, pw, None, T, T),
        (2, "Ancien rédacteur", "red@label.bj", "redacteur", None, pw, None, T, T),
    ])
    db.execute("INSERT INTO categories VALUES (5,'Société','societe','Desc',1,2,?,?)", (T, T))
    db.execute("INSERT INTO tags VALUES (3,'Bénin','benin',?,?)", (T, T))
    contenu = ('<p>Voir <img src="http://localhost/Journal/journal/public/storage/articles/content/x.jpg"></p>'
               '<script>alert(1)</script>')
    db.executemany("INSERT INTO articles VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [
        (10, "Publié", "publie-slug", "R", contenu, "articles/une.jpg", "Légende", 2, 5, "publie", 42, 1, T, T, T, None),
        (11, "Brouillon", "brouillon-slug", "R", "<p>b</p>", None, None, 1, 5, "brouillon", 0, 0, None, T, T, None),
        (12, "Archivé", "archive-slug", "R", "<p>c</p>", None, None, 1, 5, "archive", 5, 0, T, T, T, None),
    ])
    db.execute("INSERT INTO article_tag VALUES (1,10,3,?,?)", (T, T))
    db.execute("INSERT INTO article_images VALUES (1,10,'articles/gallery/g.jpg','Galerie',?,?)", (T, T))
    db.executemany("INSERT INTO comments VALUES (?,?,?,?,?,?,?,?,?,?)", [
        (2, 10, None, "Réponse", "r@x.bj", "réponse", 1, 1, T, T),  # enfant avant parent : l'ordre doit être géré
        (1, 10, None, "Lecteur", "l@x.bj", "Bravo", 1, None, T, T),
        (3, 10, 1, None, None, "En attente", 0, None, T, T),
    ])
    db.execute("INSERT INTO comment_votes VALUES (1,1,NULL,'1.2.3.4','up',?,?)", (T, T))
    db.execute("INSERT INTO reactions VALUES (1,10,NULL,'1.2.3.4','like',?,?)", (T, T))
    db.executemany("INSERT INTO settings (id,key,value) VALUES (?,?,?)", [(1, "site_name", "Label Info TV"), (2, "contact_phone", "+229 01"), (3, "clé_inconnue", "x")])
    db.execute("INSERT INTO messages VALUES (1,'N','n@x.bj','S','M',0,?,?)", (T, T))
    db.execute("INSERT INTO subscribers VALUES (1,'s@x.bj',1,?,?)", (T, T))
    db.execute("INSERT INTO publicites VALUES (1,'Pub','promotions/p.jpg','https://x.bj','sidebar',1,NULL,NULL,100,4,?,?)", (T, T))

    storage = tmp_path / "public"
    for rel in ["articles/une.jpg", "articles/gallery/g.jpg", "articles/content/x.jpg", "promotions/p.jpg", "uploads/doc.pdf"]:
        f = storage / rel
        f.parent.mkdir(parents=True, exist_ok=True)
        f.write_bytes(b"%PDF-1.4" if rel.endswith(".pdf") else _jpeg())
    return db, storage


def _jpeg():
    from io import BytesIO

    from PIL import Image

    buf = BytesIO()
    Image.new("RGB", (1300, 700), (11, 31, 58)).save(buf, "JPEG")
    return buf.getvalue()


def run(legacy_db):
    db, storage = legacy_db
    return LaravelImporter(db.cursor(), storage_dir=storage, log=lambda *a: None).run()


def test_full_import_preserves_everything(legacy_db):
    report = run(legacy_db)
    assert report.counts["articles"] == 3 and report.counts["commentaires"] == 3

    a = Article.objects.get(pk=10)
    assert a.slug == "publie-slug" and a.vues == 42 and a.est_a_la_une and a.is_published
    assert a.image_principale.fichier.name == "articles/une.jpg"
    assert list(a.tags.values_list("slug", flat=True)) == ["benin"]
    assert a.images.get().media.fichier.name == "articles/gallery/g.jpg"
    assert 'src="/storage/articles/content/x.jpg"' in a.contenu and "<script" not in a.contenu
    assert Article.objects.get(pk=11).statut == Status.BROUILLON
    assert a.est_flash_info  # sélection Laravel reprise (derniers articles publiés)
    assert not Article.objects.get(pk=11).est_flash_info
    assert Article.objects.get(pk=12).statut == Status.ARCHIVE

    assert Comment.objects.get(pk=2).parent_id == 1
    assert Comment.objects.get(pk=3).statut == Comment.Status.EN_ATTENTE
    assert MediaAsset.objects.filter(fichier="uploads/doc.pdf", type="document").exists()
    assert any("clé_inconnue" in w for w in report.warnings)


def test_import_is_idempotent(legacy_db):
    run(legacy_db)
    run(legacy_db)
    assert Article.objects.count() == 3 and User.objects.count() == 2
    assert MediaAsset.objects.filter(fichier="articles/une.jpg").count() == 1


def test_roles_and_passwords_are_kept(legacy_db):
    run(legacy_db)
    assert User.objects.get(pk=1).role == Role.ADMIN
    assert User.objects.get(pk=2).role == Role.EDITEUR  # « redacteur » Laravel gardait les droits éditeur
    user = authenticate(email="admin@label.bj", password="secret-laravel")
    assert user is not None and user.pk == 1
    user.refresh_from_db()
    assert user.password.startswith("md5$") or user.password.startswith("pbkdf2")  # ré-encodé à la connexion


def test_helpers():
    assert convert_password("$2y$12$abc").startswith("bcrypt$$2b$12$")
    assert convert_password("") == "!"
    assert rewrite_storage_urls('<img src="https://labelinfo.tv/storage/articles/a.jpg">') == '<img src="/storage/articles/a.jpg">'
    assert rewrite_storage_urls('<a href="https://autre.site/storage/doc">') == '<a href="https://autre.site/storage/doc">'