"""Seed the Server Hardware catalog.

Creates 4 top-level ``product_categories`` (Dell PowerEdge, HPE ProLiant,
Enterprise Processors, Enterprise SSD Storage) and 35 ``products`` covering
Dell/HPE server models, enterprise CPUs, and enterprise SSDs.

The seed is idempotent: it keys off of the unique ``slug`` column for both
categories and products, so running it multiple times never creates duplicates.
Existing rows with the same slug are left untouched to avoid clobbering any
admin edits.

Each product's rich hardware metadata (brand, model, condition, warranty,
specification rows) is stored as JSON in the ``products.variants`` column,
which already exists. Filter facets are written into the comma-separated
``products.tags`` column as ``facet:value`` entries so the existing Shop
page tag filter can drive Brand / Generation / CPU / RAM / Storage filtering
without a schema change.
"""

from __future__ import annotations

import json
import logging
from typing import Any, Dict, List, Optional, Tuple

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from models.product_categories import Product_categories
from models.products import Products


logger = logging.getLogger(__name__)


# ---- Image URLs generated for each category. Products fall back to these ----
IMG_DELL = (
    "https://mgx-backend-cdn.metadl.com/generate/images/1085345/"
    "2026-05-03/n3abbtiaafmq/dell-poweredge-rack-server.png"
)
IMG_HPE = (
    "https://mgx-backend-cdn.metadl.com/generate/images/1085345/"
    "2026-05-03/n3abahqaafla/hpe-proliant-rack-server.png"
)
IMG_CPU = (
    "https://mgx-backend-cdn.metadl.com/generate/images/1085345/"
    "2026-05-03/n3abbaqaafma/enterprise-server-cpu.png"
)
IMG_SSD = (
    "https://mgx-backend-cdn.metadl.com/generate/images/1085345/"
    "2026-05-03/n3abd3iaaflq/enterprise-ssd-drives.png"
)


# ----------------------------- Category list --------------------------------

CATEGORIES: List[Dict[str, Any]] = [
    {
        "name": "Dell PowerEdge Servers",
        "slug": "dell-poweredge-servers",
        "description": (
            "Enterprise Dell PowerEdge rack servers — from the trusted R420/R520 "
            "workhorses to the latest R750 Xeon Scalable generation. New and "
            "professionally refurbished, all with warranty."
        ),
        "image_url": IMG_DELL,
        "sort_order": 10,
    },
    {
        "name": "HPE ProLiant Servers",
        "slug": "hpe-proliant-servers",
        "description": (
            "HPE ProLiant DL360 and DL380 servers across Gen9, Gen10 and Gen11 — "
            "proven enterprise performance with flexible storage and memory "
            "configurations."
        ),
        "image_url": IMG_HPE,
        "sort_order": 20,
    },
    {
        "name": "Enterprise Processors",
        "slug": "enterprise-processors",
        "description": (
            "Server-grade Intel processors: Core i7/i9 for workstation loads, "
            "Xeon Silver / Gold / Platinum for virtualization, databases and "
            "mission-critical workloads."
        ),
        "image_url": IMG_CPU,
        "sort_order": 30,
    },
    {
        "name": "Enterprise SSD Storage",
        "slug": "enterprise-ssd-storage",
        "description": (
            "Data-center class SATA, SAS and NVMe SSDs from 480GB to 15.36TB — "
            "high endurance, consistent latency, hot-swap ready."
        ),
        "image_url": IMG_SSD,
        "sort_order": 40,
    },
]


# ------------------ Helper builders for product payloads --------------------

def _build_tags(*pairs: str) -> str:
    """Return a comma-separated tag string from `facet:value` entries."""
    return ",".join(tag for tag in pairs if tag)


def _pack_variants(
    brand: str,
    model: str,
    condition: str,
    warranty: str,
    specs: List[Tuple[str, str]],
    quote_only: bool = False,
) -> str:
    """Serialize hardware metadata into the products.variants JSON column."""
    payload = {
        "brand": brand,
        "model": model,
        "condition": condition,
        "warranty": warranty,
        "specs": [{"label": label, "value": value} for label, value in specs],
        "quote_only": quote_only,
    }
    return json.dumps(payload, ensure_ascii=False)


# ------------------------- Dell PowerEdge models ----------------------------

_DELL_MODELS: List[Dict[str, Any]] = [
    {
        "model": "PowerEdge R420",
        "gen": "gen2",
        "gen_label": "12G",
        "price": 2499.0,
        "cpu_desc": "Up to 2x Intel Xeon E5-2400 / E5-2400 v2",
        "cpu_facet": "xeon-e5",
        "ram_desc": "Up to 192GB DDR3 ECC (12 DIMMs)",
        "ram_facet": "ram:192gb",
        "storage_desc": "Up to 4x 3.5\" SAS/SATA hot-swap bays",
        "drive_bays": "4 × 3.5\"",
        "form_factor": "1U",
        "short": "Reliable 1U dual-socket workhorse for small business and edge workloads.",
    },
    {
        "model": "PowerEdge R520",
        "gen": "gen2",
        "gen_label": "12G",
        "price": 2899.0,
        "cpu_desc": "Up to 2x Intel Xeon E5-2400 / E5-2400 v2",
        "cpu_facet": "xeon-e5",
        "ram_desc": "Up to 384GB DDR3 ECC (12 DIMMs)",
        "ram_facet": "ram:384gb",
        "storage_desc": "Up to 8x 3.5\" hot-swap SAS/SATA",
        "drive_bays": "8 × 3.5\"",
        "form_factor": "2U",
        "short": "2U dual-socket server with strong storage capacity for file, backup and mail workloads.",
    },
    {
        "model": "PowerEdge R640",
        "gen": "gen4",
        "gen_label": "14G",
        "price": 5499.0,
        "cpu_desc": "Up to 2x Intel Xeon Scalable Gen1 / Gen2",
        "cpu_facet": "xeon-scalable",
        "ram_desc": "Up to 1.5TB DDR4 ECC (24 DIMMs)",
        "ram_facet": "ram:1.5tb",
        "storage_desc": "Up to 10x 2.5\" SAS/SATA/NVMe hot-swap",
        "drive_bays": "10 × 2.5\"",
        "form_factor": "1U",
        "short": "Dense 1U dual-socket server optimized for virtualization, VDI and software-defined workloads.",
    },
    {
        "model": "PowerEdge R720",
        "gen": "gen3",
        "gen_label": "12G",
        "price": 3199.0,
        "cpu_desc": "Up to 2x Intel Xeon E5-2600 / E5-2600 v2",
        "cpu_facet": "xeon-e5",
        "ram_desc": "Up to 768GB DDR3 ECC (24 DIMMs)",
        "ram_facet": "ram:768gb",
        "storage_desc": "Up to 16x 2.5\" or 8x 3.5\" hot-swap",
        "drive_bays": "16 × 2.5\" / 8 × 3.5\"",
        "form_factor": "2U",
        "short": "Popular 2U mid-range virtualization server with huge memory and storage flexibility.",
    },
    {
        "model": "PowerEdge R730",
        "gen": "gen3",
        "gen_label": "13G",
        "price": 3899.0,
        "cpu_desc": "Up to 2x Intel Xeon E5-2600 v3 / v4",
        "cpu_facet": "xeon-e5",
        "ram_desc": "Up to 1.5TB DDR4 ECC (24 DIMMs)",
        "ram_facet": "ram:1.5tb",
        "storage_desc": "Up to 16x 2.5\" or 8x 3.5\" hot-swap",
        "drive_bays": "16 × 2.5\" / 8 × 3.5\"",
        "form_factor": "2U",
        "short": "General-purpose 2U server with Broadwell-class Xeons — the reliable virtualization platform.",
    },
    {
        "model": "PowerEdge R750",
        "gen": "gen5",
        "gen_label": "15G",
        "price": 7999.0,
        "cpu_desc": "Up to 2x Intel Xeon Scalable Gen3 (Ice Lake)",
        "cpu_facet": "xeon-scalable",
        "ram_desc": "Up to 8TB DDR4 ECC (32 DIMMs)",
        "ram_facet": "ram:8tb",
        "storage_desc": "Up to 24x 2.5\" NVMe direct-attach",
        "drive_bays": "24 × 2.5\"",
        "form_factor": "2U",
        "short": "Flagship 2U dual-socket Ice Lake server for databases, AI inference and SDDC workloads.",
    },
]


def _dell_products(category_id: int) -> List[Dict[str, Any]]:
    out: List[Dict[str, Any]] = []
    for m in _DELL_MODELS:
        specs = [
            ("Form Factor", m["form_factor"]),
            ("Processor", m["cpu_desc"]),
            ("Memory", m["ram_desc"]),
            ("Drive Bays", m["drive_bays"]),
            ("Storage", m["storage_desc"]),
            ("Network", "4x 1GbE LOM, optional 10/25GbE NDC"),
            ("Power", "Dual redundant hot-plug PSUs"),
            ("Remote Management", "Dell iDRAC with Lifecycle Controller"),
            ("Warranty", "12 months hardware warranty"),
        ]
        tags = _build_tags(
            "brand:dell",
            f"model:{m['model'].replace(' ', '-').lower()}",
            f"gen:{m['gen']}",
            m["cpu_facet"] and f"cpu:{m['cpu_facet']}",
            m["ram_facet"],
            "storage:sas",
            "condition:refurbished",
            f"form:{m['form_factor'].lower()}",
        )
        short_slug = m["model"].replace(" ", "-").lower()
        out.append({
            "name": f"Dell {m['model']}",
            "slug": f"dell-{short_slug}",
            "description": m["short"],
            "long_description": (
                f"The Dell {m['model']} ({m['gen_label']}) is a professionally "
                f"refurbished enterprise rack server, tested and certified by "
                f"our engineers before shipping. It ships in a base configuration "
                f"with a 12-month hardware warranty; custom CPU, RAM and storage "
                f"builds are available on request — use the Request Quote button "
                f"for a tailored quotation."
            ),
            "product_type": "physical",
            "price": m["price"],
            "compare_at_price": round(m["price"] * 1.25, 2),
            "currency": "SAR",
            "category_id": category_id,
            "tags": tags,
            "image_url": IMG_DELL,
            "gallery": IMG_DELL,
            "stock": 5,
            "weight_grams": 18000,
            "sku": f"DELL-{m['model'].replace(' ', '').upper()}",
            "variants": _pack_variants(
                brand="Dell",
                model=m["model"],
                condition="Refurbished",
                warranty="12 months",
                specs=specs,
            ),
            "is_active": True,
            "featured": m["model"] in {"PowerEdge R640", "PowerEdge R750"},
        })
    return out


# -------------------------- HPE ProLiant models -----------------------------

_HPE_MODELS: List[Dict[str, Any]] = [
    # DL380 family
    {"model": "ProLiant DL380 Gen9", "gen": "gen9", "price": 3499.0,
     "cpu_desc": "Up to 2x Intel Xeon E5-2600 v3 / v4", "cpu_facet": "xeon-e5",
     "ram_desc": "Up to 3TB DDR4 (24 DIMMs)", "ram_facet": "ram:3tb",
     "storage_desc": "Up to 30 SFF / 12 LFF hot-swap bays",
     "drive_bays": "30 × 2.5\" / 12 × 3.5\"",
     "form_factor": "2U",
     "short": "Rock-solid 2U dual-socket virtualization server, huge storage flexibility."},
    {"model": "ProLiant DL380 Gen10", "gen": "gen10", "price": 5299.0,
     "cpu_desc": "Up to 2x Intel Xeon Scalable Gen1 / Gen2", "cpu_facet": "xeon-scalable",
     "ram_desc": "Up to 3TB DDR4 (24 DIMMs)", "ram_facet": "ram:3tb",
     "storage_desc": "Up to 30 SFF / 19 LFF hot-swap bays, NVMe capable",
     "drive_bays": "30 × 2.5\" / 19 × 3.5\"",
     "form_factor": "2U",
     "short": "Best-selling 2U enterprise server with flexible NVMe and persistent-memory support."},
    {"model": "ProLiant DL380 Gen11", "gen": "gen11", "price": 8499.0,
     "cpu_desc": "Up to 2x Intel Xeon Scalable Gen4 (Sapphire Rapids)", "cpu_facet": "xeon-scalable",
     "ram_desc": "Up to 8TB DDR5 (32 DIMMs)", "ram_facet": "ram:8tb",
     "storage_desc": "Up to 36 EDSFF / 24 NVMe bays",
     "drive_bays": "36 × EDSFF / 24 × NVMe",
     "form_factor": "2U",
     "short": "Next-gen DDR5 + PCIe 5.0 workhorse for AI, analytics and virtualization."},
    # DL360 family
    {"model": "ProLiant DL360 Gen9", "gen": "gen9", "price": 2999.0,
     "cpu_desc": "Up to 2x Intel Xeon E5-2600 v3 / v4", "cpu_facet": "xeon-e5",
     "ram_desc": "Up to 1.5TB DDR4 (24 DIMMs)", "ram_facet": "ram:1.5tb",
     "storage_desc": "Up to 10 SFF hot-swap bays",
     "drive_bays": "10 × 2.5\"",
     "form_factor": "1U",
     "short": "Compact 1U dual-socket server for dense, rack-efficient deployments."},
    {"model": "ProLiant DL360 Gen10", "gen": "gen10", "price": 4899.0,
     "cpu_desc": "Up to 2x Intel Xeon Scalable Gen1 / Gen2", "cpu_facet": "xeon-scalable",
     "ram_desc": "Up to 3TB DDR4 (24 DIMMs)", "ram_facet": "ram:3tb",
     "storage_desc": "Up to 10 SFF + 2 M.2, NVMe capable",
     "drive_bays": "10 × 2.5\" + 2 × M.2",
     "form_factor": "1U",
     "short": "Flagship 1U dual-socket server for high-density data centers."},
    {"model": "ProLiant DL360 Gen11", "gen": "gen11", "price": 7899.0,
     "cpu_desc": "Up to 2x Intel Xeon Scalable Gen4 (Sapphire Rapids)", "cpu_facet": "xeon-scalable",
     "ram_desc": "Up to 8TB DDR5 (32 DIMMs)", "ram_facet": "ram:8tb",
     "storage_desc": "Up to 10 EDSFF / 8 NVMe bays",
     "drive_bays": "10 × EDSFF / 8 × NVMe",
     "form_factor": "1U",
     "short": "Next-gen 1U server with DDR5, PCIe 5.0 and enhanced iLO 6 management."},
]


def _hpe_products(category_id: int) -> List[Dict[str, Any]]:
    out: List[Dict[str, Any]] = []
    for m in _HPE_MODELS:
        specs = [
            ("Form Factor", m["form_factor"]),
            ("Processor", m["cpu_desc"]),
            ("Memory", m["ram_desc"]),
            ("Drive Bays", m["drive_bays"]),
            ("Storage", m["storage_desc"]),
            ("Network", "4x 1GbE OCP 3.0, optional 10/25GbE"),
            ("Power", "Dual redundant hot-plug PSUs (up to 1600W)"),
            ("Remote Management", "HPE iLO 5 / iLO 6 with Intelligent Provisioning"),
            ("Warranty", "12 months hardware warranty"),
        ]
        short_slug = m["model"].replace(" ", "-").lower()
        tags = _build_tags(
            "brand:hpe",
            f"model:{short_slug}",
            f"gen:{m['gen']}",
            f"cpu:{m['cpu_facet']}",
            m["ram_facet"],
            "storage:sas",
            "condition:refurbished",
            f"form:{m['form_factor'].lower()}",
        )
        out.append({
            "name": f"HPE {m['model']}",
            "slug": f"hpe-{short_slug}",
            "description": m["short"],
            "long_description": (
                f"The HPE {m['model']} is a professionally refurbished enterprise "
                f"server — fully tested, cleaned, updated to the latest firmware "
                f"and certified by our engineers. Shipped in a base configuration "
                f"with a 12-month warranty. Need a specific CPU/RAM/SSD build? "
                f"Request a tailored quote."
            ),
            "product_type": "physical",
            "price": m["price"],
            "compare_at_price": round(m["price"] * 1.25, 2),
            "currency": "SAR",
            "category_id": category_id,
            "tags": tags,
            "image_url": IMG_HPE,
            "gallery": IMG_HPE,
            "stock": 5,
            "weight_grams": 18000,
            "sku": f"HPE-{m['model'].replace(' ', '').upper()}",
            "variants": _pack_variants(
                brand="HPE",
                model=m["model"],
                condition="Refurbished",
                warranty="12 months",
                specs=specs,
            ),
            "is_active": True,
            "featured": m["model"] in {"ProLiant DL380 Gen10", "ProLiant DL360 Gen11"},
        })
    return out


# -------------------------- Enterprise CPUs ---------------------------------

_CPU_LINES: List[Dict[str, Any]] = [
    {
        "name": "Intel Core i7-13700K (16-Core Workstation CPU)",
        "slug": "cpu-intel-core-i7-13700k",
        "short": "16-core (8P+8E) 13th-gen desktop/workstation CPU for performance builds.",
        "price": 1499.0, "cpu_facet": "core-i7", "brand": "Intel",
        "specs": [
            ("Family", "Intel Core i7 (13th Gen, Raptor Lake)"),
            ("Cores / Threads", "16 (8P + 8E) / 24"),
            ("Base / Boost Clock", "3.4 GHz / up to 5.4 GHz"),
            ("Cache", "30MB L3 + 24MB L2"),
            ("TDP", "125W base / 253W turbo"),
            ("Socket", "LGA1700"),
            ("Use Case", "High-performance workstation / rendering"),
        ],
    },
    {
        "name": "Intel Core i9-14900K (24-Core Workstation CPU)",
        "slug": "cpu-intel-core-i9-14900k",
        "short": "24-core (8P+16E) flagship 14th-gen desktop/workstation CPU.",
        "price": 2399.0, "cpu_facet": "core-i9", "brand": "Intel",
        "specs": [
            ("Family", "Intel Core i9 (14th Gen, Raptor Lake Refresh)"),
            ("Cores / Threads", "24 (8P + 16E) / 32"),
            ("Base / Boost Clock", "3.2 GHz / up to 6.0 GHz"),
            ("Cache", "36MB L3 + 32MB L2"),
            ("TDP", "125W base / 253W turbo"),
            ("Socket", "LGA1700"),
            ("Use Case", "Enthusiast workstation / content creation"),
        ],
    },
    {
        "name": "Intel Xeon Silver 4316 (20-Core Server CPU)",
        "slug": "cpu-intel-xeon-silver-4316",
        "short": "20-core Ice Lake Xeon Silver for mainstream dual-socket servers.",
        "price": 2899.0, "cpu_facet": "xeon-silver", "brand": "Intel",
        "specs": [
            ("Family", "Intel Xeon Scalable Gen3 (Ice Lake-SP)"),
            ("Cores / Threads", "20 / 40"),
            ("Base / Boost Clock", "2.3 GHz / up to 3.4 GHz"),
            ("Cache", "30MB L3"),
            ("TDP", "150W"),
            ("Socket", "LGA4189"),
            ("Use Case", "Dual-socket virtualization / general compute"),
        ],
    },
    {
        "name": "Intel Xeon Gold 6338 (32-Core Server CPU)",
        "slug": "cpu-intel-xeon-gold-6338",
        "short": "32-core Ice Lake Xeon Gold — proven for dense virtualization and databases.",
        "price": 6499.0, "cpu_facet": "xeon-gold", "brand": "Intel",
        "specs": [
            ("Family", "Intel Xeon Scalable Gen3 (Ice Lake-SP)"),
            ("Cores / Threads", "32 / 64"),
            ("Base / Boost Clock", "2.0 GHz / up to 3.2 GHz"),
            ("Cache", "48MB L3"),
            ("TDP", "205W"),
            ("Socket", "LGA4189"),
            ("Use Case", "Virtualization host / database / SDDC"),
        ],
    },
    {
        "name": "Intel Xeon Platinum 8380 (40-Core Server CPU)",
        "slug": "cpu-intel-xeon-platinum-8380",
        "short": "Flagship 40-core Ice Lake Xeon Platinum for mission-critical workloads.",
        "price": 14999.0, "cpu_facet": "xeon-platinum", "brand": "Intel",
        "specs": [
            ("Family", "Intel Xeon Scalable Gen3 (Ice Lake-SP)"),
            ("Cores / Threads", "40 / 80"),
            ("Base / Boost Clock", "2.3 GHz / up to 3.4 GHz"),
            ("Cache", "60MB L3"),
            ("TDP", "270W"),
            ("Socket", "LGA4189"),
            ("Use Case", "In-memory DB, AI inference, HPC"),
        ],
    },
]


def _cpu_products(category_id: int) -> List[Dict[str, Any]]:
    out: List[Dict[str, Any]] = []
    for c in _CPU_LINES:
        tags = _build_tags(
            f"brand:{c['brand'].lower()}",
            f"cpu:{c['cpu_facet']}",
            "condition:new",
            "component:cpu",
        )
        out.append({
            "name": c["name"],
            "slug": c["slug"],
            "description": c["short"],
            "long_description": (
                f"{c['name']} — brand-new, sealed retail/tray unit with full "
                f"manufacturer warranty. Ideal drop-in upgrade for your existing "
                f"server or workstation. Need a matched pair or bulk pricing? "
                f"Request a quote."
            ),
            "product_type": "physical",
            "price": c["price"],
            "compare_at_price": round(c["price"] * 1.2, 2),
            "currency": "SAR",
            "category_id": category_id,
            "tags": tags,
            "image_url": IMG_CPU,
            "gallery": IMG_CPU,
            "stock": 15,
            "weight_grams": 200,
            "sku": c["slug"].upper().replace("CPU-", "CPU-"),
            "variants": _pack_variants(
                brand=c["brand"],
                model=c["name"],
                condition="New",
                warranty="Manufacturer warranty",
                specs=c["specs"],
            ),
            "is_active": True,
            "featured": c["cpu_facet"] in {"xeon-gold", "core-i9"},
        })
    return out


# -------------------------- Enterprise SSDs ---------------------------------

_SSD_TYPES: List[Dict[str, Any]] = [
    {
        "type_label": "SATA",
        "type_slug": "sata",
        "interface": "SATA 6Gb/s",
        "read_mbs": "~550 MB/s",
        "write_mbs": "~520 MB/s",
        "iops": "up to 95k / 35k read/write IOPS",
        "endurance": "1 DWPD (mixed-use)",
        "price_base": 359.0,          # 480GB
        "price_scale": 1.85,          # multiplicative factor per capacity tier
    },
    {
        "type_label": "SAS",
        "type_slug": "sas",
        "interface": "SAS 12Gb/s",
        "read_mbs": "~2,100 MB/s",
        "write_mbs": "~2,000 MB/s",
        "iops": "up to 260k / 135k read/write IOPS",
        "endurance": "3 DWPD (mixed-use)",
        "price_base": 699.0,
        "price_scale": 1.9,
    },
    {
        "type_label": "NVMe",
        "type_slug": "nvme",
        "interface": "PCIe 4.0 x4 NVMe U.2",
        "read_mbs": "~7,000 MB/s",
        "write_mbs": "~4,200 MB/s",
        "iops": "up to 1,000k / 200k read/write IOPS",
        "endurance": "1 DWPD (read-intensive)",
        "price_base": 999.0,
        "price_scale": 1.95,
    },
]

# (capacity_label, capacity_gb, facet, tier_index) — tier_index powers pricing
_SSD_CAPACITIES: List[Tuple[str, int, str, int]] = [
    ("480GB", 480, "480gb", 0),
    ("960GB", 960, "960gb", 1),
    ("1.92TB", 1920, "1.92tb", 2),
    ("3.84TB", 3840, "3.84tb", 3),
    ("7.68TB", 7680, "7.68tb", 4),
    ("15.36TB", 15360, "15.36tb", 5),
]


def _ssd_products(category_id: int) -> List[Dict[str, Any]]:
    out: List[Dict[str, Any]] = []
    for t in _SSD_TYPES:
        for cap_label, cap_gb, cap_facet, tier in _SSD_CAPACITIES:
            price = round(t["price_base"] * (t["price_scale"] ** tier), 2)
            sku_type = t["type_slug"].upper()
            sku_cap = cap_label.replace(".", "").replace("TB", "T").replace("GB", "G")
            slug = f"ssd-{t['type_slug']}-{cap_facet}"
            tags = _build_tags(
                "brand:enterprise",
                f"storage:{t['type_slug']}",
                f"capacity:{cap_facet}",
                "condition:new",
                "component:ssd",
            )
            specs = [
                ("Form Factor", "2.5\" hot-swap (U.2 for NVMe)"),
                ("Interface", t["interface"]),
                ("Capacity", cap_label),
                ("Sequential Read", t["read_mbs"]),
                ("Sequential Write", t["write_mbs"]),
                ("Random IOPS", t["iops"]),
                ("Endurance", t["endurance"]),
                ("MTBF", "2,000,000 hours"),
                ("Warranty", "5 years limited"),
            ]
            out.append({
                "name": f"Enterprise {t['type_label']} SSD {cap_label}",
                "slug": slug,
                "description": (
                    f"{cap_label} enterprise-class 2.5\" {t['type_label']} SSD — "
                    f"high endurance and consistent low-latency performance."
                ),
                "long_description": (
                    f"Enterprise {t['type_label']} SSD ({cap_label}) is designed "
                    f"for mixed-use server workloads — virtualization, databases, "
                    f"VDI and backup targets. Ships new and sealed, 5-year "
                    f"manufacturer warranty. Need tray-mounted caddies for Dell "
                    f"PowerEdge or HPE ProLiant? Include your server model in the "
                    f"Request Quote form."
                ),
                "product_type": "physical",
                "price": price,
                "compare_at_price": round(price * 1.2, 2),
                "currency": "SAR",
                "category_id": category_id,
                "tags": tags,
                "image_url": IMG_SSD,
                "gallery": IMG_SSD,
                "stock": 25,
                "weight_grams": 120,
                "sku": f"SSD-{sku_type}-{sku_cap}",
                "variants": _pack_variants(
                    brand="Enterprise",
                    model=f"{t['type_label']} SSD {cap_label}",
                    condition="New",
                    warranty="5 years limited",
                    specs=specs,
                ),
                "is_active": True,
                "featured": cap_label in {"1.92TB"} and t["type_slug"] == "nvme",
            })
    return out


# ------------------------------ Main seed -----------------------------------

async def _get_or_create_category(
    db: AsyncSession, payload: Dict[str, Any]
) -> Product_categories:
    slug = payload["slug"]
    result = await db.execute(
        select(Product_categories).where(Product_categories.slug == slug)
    )
    row: Optional[Product_categories] = result.scalar_one_or_none()
    if row is not None:
        return row
    row = Product_categories(
        name=payload["name"],
        slug=slug,
        description=payload.get("description"),
        image_url=payload.get("image_url"),
        sort_order=payload.get("sort_order", 100),
        is_active=True,
    )
    db.add(row)
    await db.flush()  # populate row.id without committing yet
    return row


async def _create_product_if_missing(
    db: AsyncSession, payload: Dict[str, Any]
) -> Tuple[bool, str]:
    slug = payload["slug"]
    existing = await db.execute(
        select(Products.id).where(Products.slug == slug)
    )
    if existing.scalar_one_or_none() is not None:
        return False, slug
    db.add(Products(**payload))
    return True, slug


async def seed_server_hardware(db: AsyncSession) -> Dict[str, Any]:
    """Seed the server hardware catalog. Idempotent — safe to call many times.

    Returns a small report useful for the admin endpoint response.
    """
    created_categories: List[str] = []
    existing_categories: List[str] = []
    category_ids: Dict[str, int] = {}

    for cat in CATEGORIES:
        row = await _get_or_create_category(db, cat)
        category_ids[cat["slug"]] = row.id
        # crude detection: if the ORM instance is transient we created it
        if row.id is None:
            await db.flush()
        (created_categories if cat["slug"] not in existing_categories else existing_categories).append(
            cat["slug"]
        )

    await db.flush()
    # Refresh id map after flush so IDs are real.
    for cat in CATEGORIES:
        res = await db.execute(
            select(Product_categories.id).where(Product_categories.slug == cat["slug"])
        )
        cid = res.scalar_one_or_none()
        if cid:
            category_ids[cat["slug"]] = cid

    # Build product payloads.
    all_payloads: List[Dict[str, Any]] = []
    all_payloads.extend(_dell_products(category_ids["dell-poweredge-servers"]))
    all_payloads.extend(_hpe_products(category_ids["hpe-proliant-servers"]))
    all_payloads.extend(_cpu_products(category_ids["enterprise-processors"]))
    all_payloads.extend(_ssd_products(category_ids["enterprise-ssd-storage"]))

    created_slugs: List[str] = []
    skipped_slugs: List[str] = []
    for payload in all_payloads:
        created, slug = await _create_product_if_missing(db, payload)
        (created_slugs if created else skipped_slugs).append(slug)

    await db.commit()

    report = {
        "categories_total": len(CATEGORIES),
        "categories_created": created_categories,
        "products_total": len(all_payloads),
        "products_created": len(created_slugs),
        "products_skipped_existing": len(skipped_slugs),
    }
    logger.info("Server hardware seed complete: %s", report)
    return report