<?php
error_reporting(E_ALL);
include("script/condb.php");
session_start(); 
header('Content-Type: application/json');

try {
    if (!isset($_GET['project_ref'])) {
        throw new Exception("Missing project reference.");
    }

    $project_ref = $_GET['project_ref'];

    // STEP 1: Get original project info
    $stmt = $con->prepare("SELECT * FROM upline_app_project_info WHERE project_ref = ?");
    $stmt->bind_param("s", $project_ref);
    $stmt->execute();
    $result = $stmt->get_result();
    $original = $result->fetch_assoc();
    $stmt->close();

    if (!$original) {
        throw new Exception("Original project not found.");
    }

    // STEP 2: Generate new project_ref
    $new_project_ref = "PROJECT" . rand(100000, 999999) . time();

    // STEP 3: Insert new record into upline_app_project_info (exclude `id`)
    unset($original['id']);
    $original['project_ref'] = $new_project_ref;

    // Append " (COPY)" to project name
    if (isset($original['project_name'])) {
        $original['project_name'] .= " (COPY)";
    }

    // Update date field to current timestamp
    if (isset($original['date_added'])) {
        $original['date_added'] = date('Y-m-d H:i:s');
    }

    $columns = array_keys($original);
    $placeholders = implode(',', array_fill(0, count($columns), '?'));
    $types = str_repeat('s', count($columns));

    $insert_sql = "INSERT INTO upline_app_project_info (" . implode(',', $columns) . ") VALUES ($placeholders)";
    $insert_stmt = $con->prepare($insert_sql);
    $insert_stmt->bind_param($types, ...array_values($original));
    $insert_stmt->execute();
    $insert_stmt->close();

    // STEP 4: Related tables using PID
    $pid_tables = [
        'basetypecal',
        'columnbased',
        'columntype-cal',
        'concrete',
        'foundation_equipment',
        'foundation_labourer',
        'foundation_materials',
        'mortalblockcal',
        'plankandwoodreport',
        'wall_calculation'
    ];

    // NEW: related tables using project_ref (your request)
    $ref_tables = [
        'project_selected_stages',
        'project_stage_labels'
    ];

    $total_cloned = 0;

    // STEP 5A: Clone PID-based tables
    foreach ($pid_tables as $table) {
        $select = $con->prepare("SELECT * FROM `$table` WHERE PID = ?");
        $select->bind_param("s", $project_ref);
        $select->execute();
        $res = $select->get_result();

        while ($row = $res->fetch_assoc()) {

            // Remove auto-increment keys
            if (isset($row['SN'])) unset($row['SN']);
            if (isset($row['ID'])) unset($row['ID']);

            // Assign new PID
            $row['PID'] = $new_project_ref;

            // Update date field if present
            if (isset($row['Dateadded'])) {
                $row['Dateadded'] = date('Y-m-d H:i:s');
            }

            // Remove lowercase pid if exists
            if (isset($row['pid'])) unset($row['pid']);

            $cols = array_keys($row);
            $placeholders = implode(',', array_fill(0, count($cols), '?'));
            $types = str_repeat('s', count($cols));

            $insert = $con->prepare("INSERT INTO `$table` (" . implode(',', $cols) . ") VALUES ($placeholders)");
            $insert->bind_param($types, ...array_values($row));
            $insert->execute();
            $insert->close();

            $total_cloned++;
        }

        $select->close();
    }

    // STEP 5B: Clone project_ref–based tables
    foreach ($ref_tables as $table) {
        $select = $con->prepare("SELECT * FROM `$table` WHERE project_ref = ?");
        $select->bind_param("s", $project_ref);
        $select->execute();
        $res = $select->get_result();

        while ($row = $res->fetch_assoc()) {

            // Remove auto-increment ID
            unset($row['id']);

            // Assign new project_ref
            $row['project_ref'] = $new_project_ref;

            // Update date fields if present
            if (isset($row['date_added'])) $row['date_added'] = date('Y-m-d H:i:s');
            if (isset($row['Dateadded'])) $row['Dateadded'] = date('Y-m-d H:i:s');

            $cols = array_keys($row);
            $placeholders = implode(',', array_fill(0, count($cols), '?'));
            $types = str_repeat('s', count($cols));

            $insert = $con->prepare("INSERT INTO `$table` (" . implode(',', $cols) . ") VALUES ($placeholders)");
            $insert->bind_param($types, ...array_values($row));
            $insert->execute();
            $insert->close();

            $total_cloned++;
        }

        $select->close();
    }

    // STEP 6: Log duplication activity
    $cloned_by = $_SESSION['username'] ?? 'System';
    $project_name = $original['project_name'] ?? 'Unnamed Project';

    $log_stmt = $con->prepare("
        INSERT INTO project_clone_log (original_ref, new_ref, project_name, cloned_by, total_records, date_cloned)
        VALUES (?, ?, ?, ?, ?, NOW())
    ");
    $log_stmt->bind_param("ssssi", $project_ref, $new_project_ref, $project_name, $cloned_by, $total_cloned);
    $log_stmt->execute();
    $log_stmt->close();

    // STEP 7: Success response
    echo json_encode([
        "status" => "success",
        "message" => "✅ Project duplicated successfully!",
        "newRef" => $new_project_ref,
        "totalRecords" => $total_cloned
    ]);

} catch (Exception $e) {
    echo json_encode([
        "status" => "error",
        "message" => "❌ Duplication failed: " . $e->getMessage()
    ]);
}
?>
