#!/usr/bin/env python3
"""
Generates entity_content_assets INSERT SQL for new offers/services
(those with ext_id as hash = not yet in DB with GrapeJS content).
Converts Contentful data into GrapeJS project JSON + HTML + CSS.

Output: import/entity_content_assets_import.sql (uses subquery for entity_id)
"""

import json
import os
import re
import random
import string
from datetime import datetime

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
OUT_DIR = os.path.join(BASE_DIR, "output")
OFFERS_SRC = os.path.join(BASE_DIR, "offers")
SERVICES_SRC = os.path.join(BASE_DIR, "services")
OFFERS_IMPORT_SQL = os.path.join(OUT_DIR, "aws_offers_import.sql")
SERVICES_IMPORT_SQL = os.path.join(OUT_DIR, "aws_services_import.sql")
OUTPUT_SQL = os.path.join(OUT_DIR, "entity_content_assets_import.sql")

BASE_URL = "https://cockpit.astara-partner.com"

CSS_TEMPLATE = """
    /* multilang */
    .multilang-textfield { display: block; margin: 1rem 0; line-height: 1.6; }
    .lang { display: block; min-height: 1em; }

    /* heroimage */
    .heroimage-wrapper { position: relative; width: 100%; }
    .heroimage-bg-wrapper { position: relative; overflow: hidden; }
    .heroimage-bg { background-color: #a0a0a0; background-size: cover; background-position: center; }
    .headline-wrapper { margin: 0.5rem auto; padding: 0; }

    /* banner-large */
    .banner-large-wrapper { position: relative; overflow: hidden; }
    .banner-large-bg { background-color: #7a8a9a; background-size: cover; background-position: center; background-repeat: no-repeat; }
    .banner-large-content { position: relative; z-index: 1; display: flex; flex-direction: column; align-items: center; justify-content: center; height: 100%; padding: 2rem; text-align: center; }

    /* teaser-large */
    .teaser-large-wrapper { position: relative; overflow: hidden; }
    .teaser-large-bg { background-color: #4a5568; background-size: cover; background-position: center; background-repeat: no-repeat; }
    .teaser-large-overlay { background: rgba(0,0,0,0.35); }
    .teaser-large-content { position: relative; z-index: 1; display: flex; flex-direction: column; align-items: center; justify-content: center; height: 100%; padding: 2rem; text-align: center; color: #fff; }

    /* offer-root */
    .offer-root { position: relative; width: 100%; min-height: 100vh; }
    .heroimage { height: 60vh; width: 100%; background-size: cover; background-position: center center; background-repeat: no-repeat; }
    .container { max-width: 1200px; margin: 0 auto; padding: 20px; }
    .headline-field { font-size: 2rem; font-weight: bold; margin: 1rem 0; }
    .subheadline-field { font-size: 1.25rem; margin: 0.5rem 0; color: #555; }
    .disclaimer-field { margin: 2rem 0; font-size: 0.875rem; color: #888; border-top: 1px solid #ddd; padding-top: 1rem; }
    .form-placeholder { padding: 20px; background: #f3f3f3; border: 2px dashed #999; text-align: center; color: #666; font-style: italic; }
"""


def read_json(path):
    if not os.path.isfile(path):
        return None
    with open(path, "r", encoding="utf-8") as f:
        return json.load(f)


def get_locale_val(d, locale, default=""):
    if not isinstance(d, dict):
        return default
    v = d.get(locale)
    if v is None:
        return default
    return str(v)


def rand_id():
    return "i" + "".join(random.choices(string.ascii_lowercase + string.digits, k=5))


def esc_sql(val):
    if val is None:
        return "NULL"
    s = str(val).replace("\\", "\\\\").replace("'", "\\'")
    return f"'{s}'"


def parse_import_hashes(sql_path):
    """Parse the import SQL output to get {ext_id: hash} pairs."""
    mapping = {}
    with open(sql_path, "r", encoding="utf-8") as f:
        content = f.read()

    match = re.search(
        r"INSERT INTO\s+`?\w+`?\s*\([^)]+\)\s*VALUES\s*(.+?);",
        content, re.DOTALL | re.IGNORECASE
    )
    if not match:
        return mapping

    vb = match.group(1)
    rows = []
    depth = 0
    cur = []
    for ch in vb:
        if ch == "(" and depth == 0:
            depth = 1; cur = []
        elif ch == "(":
            depth += 1; cur.append(ch)
        elif ch == ")":
            depth -= 1
            if depth == 0:
                rows.append("".join(cur))
            else:
                cur.append(ch)
        else:
            if depth > 0:
                cur.append(ch)

    for rs in rows:
        parts = split_sql_vals(rs)
        if len(parts) >= 26:
            h = parts[0].strip("'\" ") if parts[0].strip("'\" ") != "NULL" else None
            e = parts[24].strip("'\" ") if len(parts) > 24 and parts[24].strip("'\" ") != "NULL" else None
            if h and e:
                mapping[e] = h
    return mapping


def split_sql_vals(row_str):
    parts = []
    cur = []
    in_str = False
    sc = None
    i = 0
    while i < len(row_str):
        ch = row_str[i]
        if in_str:
            if ch == "\\" and i + 1 < len(row_str):
                cur.append(ch); cur.append(row_str[i + 1]); i += 2; continue
            elif ch == sc:
                in_str = False; cur.append(ch)
            else:
                cur.append(ch)
        else:
            if ch in ("'", '"'):
                in_str = True; sc = ch; cur.append(ch)
            elif ch == ",":
                parts.append("".join(cur).strip()); cur = []
            else:
                cur.append(ch)
        i += 1
    if cur:
        parts.append("".join(cur).strip())
    return parts


def find_detail_asset(assets):
    for a in assets:
        if a.get("sourceField") == "fields.detailsImage" and a.get("language") == "de":
            return a
    for a in assets:
        if a.get("sourceField") == "fields.detailsImage":
            return a
    return None


import urllib.parse


def url_enc(s):
    """URL-encode a path component (spaces -> %20, etc.)."""
    return s.replace(" ", "%20")


def generate_grapesjs_project(entity_type, entity_hash, meta, content):
    """Generate GrapeJS project JSON, HTML, CSS from Contentful metadata."""
    data = meta if meta else {}
    ctx = (content or {}).get("context", {})

    ext_id = data.get("id") or (content or {}).get("id", "")
    brand_name = ctx.get("brand", "")
    market = ctx.get("market", "CH")

    # Images
    assets = data.get("assets", [])
    detail = find_detail_asset(assets)
    detail_image_hash = None
    detail_ext = None
    if detail:
        fname = detail.get("fileName", "")
        name_no_ext, ext = os.path.splitext(fname)
        detail_image_hash = name_no_ext
        detail_ext = ext.lstrip(".")

    # Texts
    title = data.get("teaserHeadline") or data.get("title", {})
    title_de = get_locale_val(title, "de")
    title_fr = get_locale_val(title, "fr")
    title_it = get_locale_val(title, "it")

    desc = data.get("teaserSubheadline", {})
    desc_de = get_locale_val(desc, "de") if desc else ""
    desc_fr = get_locale_val(desc, "fr") if desc else ""
    desc_it = get_locale_val(desc, "it") if desc else ""

    body = data.get("bodyCopyData", {})
    body_de = get_locale_val(body, "de") if body else ""
    body_fr = get_locale_val(body, "fr") if body else ""
    body_it = get_locale_val(body, "it") if body else ""
    has_body = bool(body_de or body_fr or body_it)

    disc = data.get("disclaimerData", {})
    disc_de = get_locale_val(disc, "de") if disc else ""
    disc_fr = get_locale_val(disc, "fr") if disc else ""
    disc_it = get_locale_val(disc, "it") if disc else ""
    has_disc = bool(disc_de or disc_fr or disc_it)

    hero_id = rand_id()
    placeholder_id = rand_id()

    # Hero image URL
    hero_url = ""
    if detail_image_hash and detail_ext:
        hero_url = url_enc(f"{BASE_URL}/uploads/{entity_type}/{entity_hash}/{detail_image_hash}.{detail_ext}")

    # --- Build project JSON ---
    hero_style = {
        "height": "60vh",
        "width": "100%",
        "background-size": "cover",
        "background-position": "center center",
        "background-repeat": "no-repeat",
    }
    if hero_url:
        hero_style["background-image"] = f'url("{hero_url}")'

    placeholder_style = {
        "padding": "20px",
        "background": "#f3f3f3",
        "border": "2px dashed #999",
        "textAlign": "center",
        "color": "#666",
        "fontStyle": "italic",
    }

    styles = [
        {"selectors": [f"#{hero_id}"], "style": hero_style},
        {"selectors": [f"#{placeholder_id}"], "style": placeholder_style},
    ]

    # Build container components
    container_components = []

    # Headline
    hl = {
        "type": "headline-field",
        "classes": ["headline-field"],
        "components": [
            {"type": "plain-text", "content": title_de, "classes": ["lang", "lang-de"]},
            {"type": "plain-text", "content": title_fr, "classes": ["lang", "lang-fr"]},
            {"type": "plain-text", "content": title_it, "classes": ["lang", "lang-it"]},
        ],
        "textDe": title_de,
        "textFr": title_fr,
        "textIt": title_it,
    }
    container_components.append(hl)

    # Subheadline
    if desc_de or desc_fr or desc_it:
        shl = {
            "type": "subheadline-field",
            "classes": ["subheadline-field"],
            "components": [
                {"type": "plain-text", "content": desc_de, "classes": ["lang", "lang-de"]},
                {"type": "plain-text", "content": desc_fr, "classes": ["lang", "lang-fr"]},
                {"type": "plain-text", "content": desc_it, "classes": ["lang", "lang-it"]},
            ],
            "textDe": desc_de,
            "textFr": desc_fr,
            "textIt": desc_it,
        }
        container_components.append(shl)

    # Body
    if has_body:
        bf = {
            "type": "multilang-textfield",
            "classes": ["multilang-textfield"],
            "components": [
                {"type": "plain-text", "content": body_de, "classes": ["lang", "lang-de"]},
                {"type": "plain-text", "content": body_fr, "classes": ["lang", "lang-fr"]},
                {"type": "plain-text", "content": body_it, "classes": ["lang", "lang-it"]},
            ],
            "textDe": body_de,
            "textFr": body_fr,
            "textIt": body_it,
        }
        container_components.append(bf)

    # Form placeholder
    container_components.append({"type": "form-placeholder", "attributes": {"id": placeholder_id}})

    # Disclaimer
    if has_disc:
        df = {
            "type": "disclaimer-field",
            "classes": ["disclaimer-field"],
            "components": [
                {"type": "plain-text", "content": disc_de, "classes": ["lang", "lang-de"]},
                {"type": "plain-text", "content": disc_fr, "classes": ["lang", "lang-fr"]},
                {"type": "plain-text", "content": disc_it, "classes": ["lang", "lang-it"]},
            ],
            "textDe": disc_de,
            "textFr": disc_fr,
            "textIt": disc_it,
        }
        container_components.append(df)

    # Hero image component
    hero_component = {
        "type": "heroimage",
        "classes": ["heroimage"],
        "attributes": {"id": hero_id},
    }
    if hero_url:
        hero_component["heroImage"] = hero_url

    project = {
        "dataSources": [],
        "assets": [],
        "styles": styles,
        "pages": [
            {
                "name": "Offer Page",
                "frames": [
                    {
                        "component": {
                            "type": "wrapper",
                            "stylable": [
                                "background", "background-color", "background-image",
                                "background-repeat", "background-attachment",
                                "background-position", "background-size"
                            ],
                            "components": [
                                {
                                    "type": "offer-root",
                                    "classes": ["offer-root"],
                                    "components": [
                                        hero_component,
                                        {"classes": ["container"], "components": container_components},
                                    ],
                                }
                            ],
                        }
                    }
                ],
            }
        ],
    }

    project_json = json.dumps(project, ensure_ascii=False)

    # --- Build HTML ---
    html_parts = ['<body><div class="offer-root">']

    # Hero
    if hero_url:
        html_parts.append(f'<div id="{hero_id}" class="heroimage"></div>')
    else:
        html_parts.append(f'<div id="{hero_id}" class="heroimage" style="background-color:#a0a0a0"></div>')

    html_parts.append('<div class="container">')

    # Headline
    html_parts.append(f'<h1 class="headline-field">')
    html_parts.append(f'<div class="lang lang-de">{esc_html(title_de)}</div>')
    html_parts.append(f'<div class="lang lang-fr">{esc_html(title_fr)}</div>')
    html_parts.append(f'<div class="lang lang-it">{esc_html(title_it)}</div>')
    html_parts.append('</h1>')

    # Subheadline
    if desc_de or desc_fr or desc_it:
        html_parts.append(f'<h2 class="subheadline-field">')
        html_parts.append(f'<div class="lang lang-de">{esc_html(desc_de)}</div>')
        html_parts.append(f'<div class="lang lang-fr">{esc_html(desc_fr)}</div>')
        html_parts.append(f'<div class="lang lang-it">{esc_html(desc_it)}</div>')
        html_parts.append('</h2>')

    # Body
    if has_body:
        html_parts.append(f'<div class="multilang-textfield">')
        html_parts.append(f'<div class="lang lang-de">{body_de}</div>')
        html_parts.append(f'<div class="lang lang-fr">{body_fr}</div>')
        html_parts.append(f'<div class="lang lang-it">{body_it}</div>')
        html_parts.append('</div>')

    # Form placeholder
    html_parts.append(f'<div id="{placeholder_id}" class="form-placeholder">[Form]</div>')

    # Disclaimer
    if has_disc:
        html_parts.append(f'<div class="disclaimer-field">')
        html_parts.append(f'<div class="lang lang-de">{disc_de}</div>')
        html_parts.append(f'<div class="lang lang-fr">{disc_fr}</div>')
        html_parts.append(f'<div class="lang lang-it">{disc_it}</div>')
        html_parts.append('</div>')

    html_parts.append('</div></div></body>')
    html_str = "\n".join(html_parts)

    return project_json, html_str


def esc_html(val):
    return val.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;").replace('"', "&quot;")


def generate_insert(entity_type, ext_id, entity_hash, project_json, html_str):
    """Generate INSERT IGNORE SQL using subquery for entity_id."""
    table = "aws_offers" if entity_type == "offers" else "aws_services"
    id_col = "id_offers" if entity_type == "offers" else "id_services"
    etype_val = "'offer'" if entity_type == "offers" else "'service'"

    lines = []
    # project entry
    pj_esc = project_json.replace("\\", "\\\\").replace("'", "\\'")
    lines.append(
        f"INSERT IGNORE INTO `entity_content_assets` (`id`, `entity_type`, `entity_id`, `section_key`, `locale`, `asset_type`, `content`, `created_at`, `updated_at`)\n"
        f"SELECT NULL, {etype_val}, {id_col}, '', 'de', 'project',\n"
        f"  '{pj_esc}',\n"
        f"  NOW(), NOW()"
        f"\nFROM {table} WHERE ext_id = '{ext_id}';\n"
    )

    # html entry
    html_esc = html_str.replace("\\", "\\\\").replace("'", "\\'")
    lines.append(
        f"INSERT IGNORE INTO `entity_content_assets` (`id`, `entity_type`, `entity_id`, `section_key`, `locale`, `asset_type`, `content`, `created_at`, `updated_at`)\n"
        f"SELECT NULL, {etype_val}, {id_col}, '', 'de', 'html',\n"
        f"  '{html_esc}',\n"
        f"  NOW(), NOW()"
        f"\nFROM {table} WHERE ext_id = '{ext_id}';\n"
    )

    # css entry
    css_esc = CSS_TEMPLATE.replace("\\", "\\\\").replace("'", "\\'")
    lines.append(
        f"INSERT IGNORE INTO `entity_content_assets` (`id`, `entity_type`, `entity_id`, `section_key`, `locale`, `asset_type`, `content`, `created_at`, `updated_at`)\n"
        f"SELECT NULL, {etype_val}, {id_col}, '', 'de', 'css',\n"
        f"  '{css_esc}',\n"
        f"  NOW(), NOW()"
        f"\nFROM {table} WHERE ext_id = '{ext_id}';\n"
    )

    return lines


def main():
    random.seed(42)
    print("=== Convert Contentful to GrapeJS ===\n")

    # Parse import SQLs to get {ext_id: hash}
    print("[1/4] Parsing import SQLs...")
    offer_map = parse_import_hashes(OFFERS_IMPORT_SQL)
    service_map = parse_import_hashes(SERVICES_IMPORT_SQL)
    print(f"  {len(offer_map)} offers, {len(service_map)} services in import")

    # Filter: only items where hash == ext_id (new items needing GrapeJS)
    new_offers = {e: h for e, h in offer_map.items() if e == h}
    new_services = {e: h for e, h in service_map.items() if e == h}
    print(f"  New (need GrapeJS): {len(new_offers)} offers, {len(new_services)} services")

    # Generate output
    print("\n[2/4] Generating GrapeJS content...")
    all_inserts = []
    items_processed = {"offers": 0, "services": 0}

    for entity_type, src_dir, new_map in [
        ("offers", OFFERS_SRC, new_offers),
        ("services", SERVICES_SRC, new_services),
    ]:
        if not os.path.isdir(src_dir):
            continue

        for brand_name in sorted(os.listdir(src_dir)):
            brand_dir = os.path.join(src_dir, brand_name)
            if not os.path.isdir(brand_dir) or brand_name.startswith("."):
                continue

            for item_name in sorted(os.listdir(brand_dir)):
                item_dir = os.path.join(brand_dir, item_name)
                if not os.path.isdir(item_dir) or item_name.startswith("."):
                    continue

                meta = read_json(os.path.join(item_dir, "metadata.json"))
                content = read_json(os.path.join(item_dir, "content.json"))
                data = meta if meta else {}
                ext_id = data.get("id") or (content or {}).get("id", "")

                if ext_id not in new_map:
                    continue

                entity_hash = new_map[ext_id]

                # Generate GrapeJS project
                project_json, html_str = generate_grapesjs_project(
                    entity_type, entity_hash, meta, content
                )

                # Generate INSERT statements
                inserts = generate_insert(entity_type, ext_id, entity_hash, project_json, html_str)
                all_inserts.extend(inserts)
                items_processed[entity_type] += 1

    print(f"  Processed: {items_processed['offers']} offers, {items_processed['services']} services")

    # Write output SQL
    print("\n[3/4] Writing output...")
    os.makedirs(OUT_DIR, exist_ok=True)

    sql_lines = [
        "-- entity_content_assets import for new offers/services",
        f"-- Generated: {datetime.now().isoformat()}",
        "-- Converts Contentful data to GrapeJS project format",
        "--",
        "-- NOTE: uses INSERT IGNORE with subquery to resolve entity_id from",
         "-- aws_offers/aws_services by ext_id (skips existing rows)",
        "-- Each item generates 3 rows: project, html, css",
        "",
        "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";",
        "START TRANSACTION;",
        "SET time_zone = \"+00:00\";",
        "",
        "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;",
        "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;",
        "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;",
        "/*!40101 SET NAMES utf8mb4 */;",
        "",
    ]

    sql_lines.extend(all_inserts)

    sql_lines.extend([
        "",
        "/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;",
        "/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;",
        "/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;",
        "COMMIT;",
    ])

    with open(OUTPUT_SQL, "w", encoding="utf-8") as f:
        f.write("\n".join(sql_lines))
        f.write("\n")

    print(f"  SQL: {OUTPUT_SQL}")

    # Summary
    print("\n[4/4] Summary")
    total_rows = sum(items_processed.values()) * 3
    print(f"  Items processed: {items_processed['offers']} offers + {items_processed['services']} services = {sum(items_processed.values())} total")
    print(f"  SQL rows generated: {total_rows} (3 per item)")
    print("\nDone.")


if __name__ == "__main__":
    main()