<?php
/* ============================================================================
   Migration: Product & Service Management (admin)
   Run once:  http://localhost/neo/newproductmigration.php   (delete after running)

   Adds ONLY what the Product Management page needs:
     - share percentage columns on seller_services + agency_services
     - product_notes            (notes against a product/service)
     - product_update_history   (who changed which field, when)
   Nothing else is touched.
   ============================================================================ */
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';

echo "<h2>Migration — Product &amp; Service Management</h2>";

$share_cols = [
    'seller_share_pct'          => 'Seller share %',
    'subscription_discount_pct' => 'Discount share (subscription) %',
    'referral_share_pct'        => 'Referral share %',
    'staff_share_pct'           => 'Staff share %',
    'company_share_pct'         => 'Company share %',
];

$queries = [];
foreach (['seller_services', 'agency_services'] as $tbl) {
    foreach ($share_cols as $col => $label) {
        $queries[] = "ALTER TABLE $tbl ADD COLUMN IF NOT EXISTS $col DECIMAL(5,2) NOT NULL DEFAULT 0.00";
    }
}

// Notes against a product / service
$queries[] = "CREATE TABLE IF NOT EXISTS product_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL,
    service_type VARCHAR(20) NOT NULL DEFAULT 'seller',   -- seller | agency
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    created_by INT NULL,
    created_by_name VARCHAR(120) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Update history for a product / service
$queries[] = "CREATE TABLE IF NOT EXISTS product_update_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL,
    service_type VARCHAR(20) NOT NULL DEFAULT 'seller',
    field_label VARCHAR(120) NOT NULL,
    old_value TEXT NULL,
    new_value TEXT NULL,
    updated_by INT NULL,
    updated_by_name VARCHAR(120) NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Customer Data Recruitment — required KYC sections + area mode
foreach (['seller_services', 'agency_services'] as $tbl) {
    $queries[] = "ALTER TABLE $tbl ADD COLUMN IF NOT EXISTS required_kyc VARCHAR(255) NULL";
    $queries[] = "ALTER TABLE $tbl ADD COLUMN IF NOT EXISTS area_mode VARCHAR(20) NOT NULL DEFAULT 'all'"; // all | single | bulk
}

/* --- Pending Services review workflow ---
   status: 0 = pending review, 1 = approved / live, 2 = rejected (kept, not deleted) --- */
$queries[] = "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS rejection_reason TEXT NULL";
$queries[] = "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS reviewed_by INT NULL";
$queries[] = "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS reviewed_by_name VARCHAR(120) NULL";
$queries[] = "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS reviewed_at DATETIME NULL";
$queries[] = "ALTER TABLE seller_services ADD COLUMN IF NOT EXISTS submitted_at DATETIME NULL";
$queries[] = "ALTER TABLE seller_services ADD INDEX idx_ss_status (status)";

/* --- Columns the purchase gates read from `users` (safety net: these normally
       already exist from migration.php, but the gates hard-depend on them) --- */
$queries[] = "ALTER TABLE users ADD COLUMN IF NOT EXISTS pincode VARCHAR(20) NULL";
$queries[] = "ALTER TABLE users ADD COLUMN IF NOT EXISTS is_subscribed TINYINT(1) NOT NULL DEFAULT 0";
$queries[] = "ALTER TABLE users ADD COLUMN IF NOT EXISTS referred_by INT NULL DEFAULT NULL";

// Serviceable pincodes for a product
$queries[] = "CREATE TABLE IF NOT EXISTS product_areas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL,
    service_type VARCHAR(20) NOT NULL DEFAULT 'seller',
    pincode VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_area (service_id, service_type, pincode),
    KEY idx_area_service (service_type, service_id),
    KEY idx_area_pincode (pincode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

// Ledger of every share paid out on a purchase
$queries[] = "CREATE TABLE IF NOT EXISTS product_share_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NULL,
    service_id INT NOT NULL,
    service_type VARCHAR(20) NOT NULL DEFAULT 'seller',
    service_title VARCHAR(255) NULL,
    buyer_id INT NULL,
    share_type VARCHAR(30) NOT NULL,
    beneficiary_id INT NULL,
    beneficiary_name VARCHAR(120) NULL,
    percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    base_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    paid TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_ledger_service (service_id),
    KEY idx_ledger_order (order_id),
    KEY idx_ledger_type (share_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

$conn = $db->getConnection();
$ok = 0;
foreach ($queries as $query) {
    // MySQL < 8 / MariaDB < 10.3 don't support "ADD COLUMN IF NOT EXISTS" —
    // strip it for ALTERs only (CREATE TABLE IF NOT EXISTS is universally supported).
    $q = (stripos($query, 'ALTER TABLE') === 0)
        ? str_ireplace(" IF NOT EXISTS", "", $query)
        : $query;
    try {
        if ($conn->query($q)) {
            $ok++;
            echo "<p style='color:green;'>Success: " . htmlspecialchars(substr($q, 0, 90)) . "...</p>";
        }
    } catch (mysqli_sql_exception $e) {
        $err = $e->getMessage();
        if (strpos($err, 'Duplicate column') !== false || strpos($err, 'already exists') !== false || strpos($err, 'Duplicate key name') !== false) {
            echo "<p style='color:orange;'>Info (already exists): " . htmlspecialchars(substr($q, 0, 90)) . "...</p>";
        } else {
            echo "<p style='color:red;'>Error: " . htmlspecialchars($err) . "</p>";
        }
    }
}

/* ----------------------------------------------------------------------------
   Backfill: company share is the remainder, so any product that has never been
   configured should show 100% company share instead of 0%.
   Only touches rows where every share column is still 0.
   ---------------------------------------------------------------------------- */
foreach (['seller_services', 'agency_services'] as $tbl) {
    try {
        $conn->query("UPDATE $tbl
                      SET company_share_pct = 100.00
                      WHERE seller_share_pct = 0 AND subscription_discount_pct = 0
                        AND referral_share_pct = 0 AND staff_share_pct = 0
                        AND company_share_pct = 0");
        echo "<p style='color:green;'>Backfilled company share on <b>$tbl</b> (" . $conn->affected_rows . " row(s)).</p>";
    } catch (mysqli_sql_exception $e) {
        echo "<p style='color:orange;'>Skipped backfill on $tbl: " . htmlspecialchars($e->getMessage()) . "</p>";
    }
}

/* Re-sync company share on every product so it always equals the remainder */
foreach (['seller_services', 'agency_services'] as $tbl) {
    try {
        $conn->query("UPDATE $tbl
                      SET company_share_pct = GREATEST(0, 100 - (seller_share_pct + subscription_discount_pct + referral_share_pct + staff_share_pct))
                      WHERE (seller_share_pct + subscription_discount_pct + referral_share_pct + staff_share_pct) > 0");
        echo "<p style='color:green;'>Re-synced company share on <b>$tbl</b> (" . $conn->affected_rows . " row(s)).</p>";
    } catch (mysqli_sql_exception $e) {
        echo "<p style='color:orange;'>Skipped re-sync on $tbl: " . htmlspecialchars($e->getMessage()) . "</p>";
    }
}

echo "<br><p><b>Done ($ok statements ran).</b> Please delete this file (newproductmigration.php) for security.</p>";
?>
