<?php
/**
 * Seed داده عرقیات
 * php run-arag-seed.php
 */
declare(strict_types=1);

define('ACC_BASE_PATH', __DIR__);
require_once __DIR__ . '/standalone/bootstrap.php';

header('Content-Type: application/json; charset=utf-8');
global $wpdb;

// ایجاد جداول در صورت نبود
$charset = 'DEFAULT CHARSET=utf8mb4';
$tables = [
    "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}arag_families (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(120) NOT NULL, slug VARCHAR(120) NOT NULL,
        icon VARCHAR(20) NOT NULL DEFAULT '🌿', image_url VARCHAR(500) NULL, sort_order INT NOT NULL DEFAULT 0,
        is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), UNIQUE KEY slug (slug)
    ) {$charset}",
    "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}arag_variants (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, family_id BIGINT UNSIGNED NOT NULL,
        name VARCHAR(120) NOT NULL, slug VARCHAR(120) NOT NULL, description TEXT NULL,
        sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), KEY family_id (family_id), UNIQUE KEY family_slug (family_id, slug)
    ) {$charset}",
    "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}arag_packages (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, variant_id BIGINT UNSIGNED NOT NULL,
        label VARCHAR(120) NOT NULL, volume VARCHAR(30) NOT NULL DEFAULT '',
        price DECIMAL(12,0) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0,
        is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id), KEY variant_id (variant_id)
    ) {$charset}",
    "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}shop_otp (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, phone VARCHAR(15) NOT NULL, code VARCHAR(6) NOT NULL,
        expires_at DATETIME NOT NULL, verified TINYINT(1) NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY phone (phone)
    ) {$charset}",
    "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}arag_orders (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_key VARCHAR(60) NOT NULL,
        order_number VARCHAR(30) NOT NULL, user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
        items_json LONGTEXT NOT NULL, subtotal DECIMAL(14,0) NOT NULL DEFAULT 0,
        shipping_method VARCHAR(20) NOT NULL DEFAULT 'post', shipping_cod TINYINT(1) NOT NULL DEFAULT 1,
        payment_method VARCHAR(30) NOT NULL DEFAULT 'zarinpal', payment_status VARCHAR(20) NOT NULL DEFAULT 'pending',
        order_status VARCHAR(20) NOT NULL DEFAULT 'pending', authority VARCHAR(80) NULL, ref_id VARCHAR(80) NULL,
        billing_name VARCHAR(120) NULL, billing_phone VARCHAR(20) NULL, billing_address VARCHAR(500) NULL,
        billing_city VARCHAR(80) NULL, customer_note TEXT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id), UNIQUE KEY order_key (order_key), KEY user_id (user_id)
    ) {$charset}",
];
foreach ($tables as $sql) {
    $wpdb->query($sql);
}
update_option('acc_engine_db_version', '17.4');

$families = [
    [ 'name' => 'عرق گلاب',     'slug' => 'golab',     'icon' => '🌹' ],
    [ 'name' => 'عرق بیدمشک',   'slug' => 'bidmeshk',  'icon' => '🌸' ],
    [ 'name' => 'عرق کاسنی',    'slug' => 'kasni',     'icon' => '🌿' ],
    [ 'name' => 'عرق نعنا',     'slug' => 'naana',     'icon' => '🍃' ],
    [ 'name' => 'عرق نسترن',    'slug' => 'nastaran',  'icon' => '🌺' ],
    [ 'name' => 'عرق بهارنارنج','slug' => 'bahar',     'icon' => '🍊' ],
    [ 'name' => 'عرق آویشن',    'slug' => 'avishan',   'icon' => '🌱' ],
    [ 'name' => 'عرق زعفران',   'slug' => 'zafaran',   'icon' => '✨' ],
];

$variants_tpl = [
    [ 'name' => 'اصیل',   'slug' => 'asil',   'desc' => 'عرق‌گیری سنتی' ],
    [ 'name' => 'ویژه',    'slug' => 'vizhe',  'desc' => 'غلظت بالاتر' ],
    [ 'name' => 'ارگانیک', 'slug' => 'organic','desc' => 'کشت ارگانیک' ],
];

$packages_tpl = [
    [ 'label' => 'شیشه‌ای', 'volume' => '۲۵۰ میلی‌لیتر', 'price' => 85000 ],
    [ 'label' => 'پت',      'volume' => '۵۰۰ میلی‌لیتر', 'price' => 145000 ],
    [ 'label' => 'گالن',    'volume' => '۱ لیتر',        'price' => 260000 ],
    [ 'label' => 'گالن بزرگ','volume' => '۲ لیتر',       'price' => 480000 ],
];

$created_f = 0; $created_v = 0; $created_p = 0; $skipped = 0;

foreach ($families as $idx => $fam) {
    $exists = $wpdb->get_var(
        "SELECT id FROM {$wpdb->prefix}arag_families WHERE slug = '" . esc_sql($fam['slug']) . "' LIMIT 1"
    );
    if ($exists) {
        $family_id = (int) $exists;
        $skipped++;
    } else {
        $wpdb->insert("{$wpdb->prefix}arag_families", [
            'name' => $fam['name'], 'slug' => $fam['slug'],
            'icon' => $fam['icon'], 'sort_order' => $idx,
        ]);
        $family_id = (int) $wpdb->insert_id;
        $created_f++;
    }

    foreach ($variants_tpl as $vidx => $var) {
        $vslug = $fam['slug'] . '-' . $var['slug'];
        $vex = $wpdb->get_var(
            "SELECT id FROM {$wpdb->prefix}arag_variants WHERE slug = '" . esc_sql($vslug) . "' LIMIT 1"
        );
        if ($vex) {
            $variant_id = (int) $vex;
            continue;
        }
        $wpdb->insert("{$wpdb->prefix}arag_variants", [
            'family_id' => $family_id,
            'name'      => $var['name'],
            'slug'      => $vslug,
            'description' => $var['desc'],
            'sort_order'  => $vidx,
        ]);
        $variant_id = (int) $wpdb->insert_id;
        $created_v++;

        foreach ($packages_tpl as $pidx => $pkg) {
            $mult = $var['slug'] === 'organic' ? 1.3 : ($var['slug'] === 'vizhe' ? 1.15 : 1);
            $wpdb->insert("{$wpdb->prefix}arag_packages", [
                'variant_id' => $variant_id,
                'label'      => $pkg['label'],
                'volume'     => $pkg['volume'],
                'price'      => (int) round($pkg['price'] * $mult),
                'sort_order' => $pidx,
            ]);
            $created_p++;
        }
    }
}

echo json_encode([
    'success' => true,
    'created_families'  => $created_f,
    'created_variants'  => $created_v,
    'created_packages'  => $created_p,
    'skipped_families'  => $skipped,
    'shop_url' => '/acc-engine/shop.php',
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
