<?php
// submit.php
// Production-Ready Submission Handler with SMTP & Token System

// 0. Start Session
session_start();

// 1. Configuration & Dependencies
require_once 'config.php';
require_once 'smtp_mailer.php';

// 2. Error Handling
ini_set('display_errors', 0); // Suppress HTML output to keep JSON valid
ini_set('log_errors', 1);
error_reporting(E_ALL);

// 3. Response Header
header('Content-Type: application/json; charset=utf-8');

// 4. Global Exception Handler
set_exception_handler(function ($e) {
    echo json_encode(['status' => 'error', 'message' => 'Server Error: ' . $e->getMessage()]);
    exit;
});

// 5. Main Logic
try {
    $action = $_REQUEST['action'] ?? 'submit_data';

    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    if ($conn->connect_error) {
        throw new Exception("Database Connection Failed: " . $conn->connect_error);
    }

    // --------------------------------------------------------------------------
    // ACTION: CHECK EMAIL (Real-time)
    // --------------------------------------------------------------------------
    if ($action === 'check_email') {
        $email = filter_var($_GET['email'] ?? '', FILTER_SANITIZE_EMAIL);

        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            echo json_encode(['status' => 'invalid']);
            exit;
        }

        $stmt = $conn->prepare("SELECT id FROM submissions WHERE email = ?");
        if (!$stmt) {
            throw new Exception("DB Error (Check Email): " . $conn->error);
        }
        $stmt->bind_param("s", $email);
        $stmt->execute();
        $stmt->store_result();

        if ($stmt->num_rows > 0) {
            echo json_encode(['status' => 'exists', 'message' => 'This email is already registered.']);
        } else {
            echo json_encode(['status' => 'available']);
        }
        $stmt->close();
        exit;
    }

    // Initialize Mailer
    $mailer = new SmtpMailer(SMTP_HOST, SMTP_PORT, SMTP_USER, SMTP_PASS);

    // --------------------------------------------------------------------------
    // ACTION: SUBMIT data (Logged In User)
    // --------------------------------------------------------------------------
    if ($action === 'submit_data') {
        // 1. Auth Check
        if (!isset($_SESSION['user_id'])) {
            throw new Exception("Unauthorized. Please log in.");
        }
        $user_id = $_SESSION['user_id'];

        // 2. Get Inputs
        $title = htmlspecialchars($_POST['title'] ?? 'Untitled data');
        // Raw data for content analysis (before massive escaping)
        $raw_data = $_POST['data'] ?? '';

        // Security: Reject <video> or <iframe> in rich text
        if (preg_match('/<video|<iframe|<embed|<object/i', $raw_data)) {
            echo json_encode(['status' => 'error', 'message' => 'Video embedding is not allowed in the text editor. Please upload videos as files below.']);
            exit;
        }

        // Allow safe Quill HTML tags, strip everything else
        $allowed_tags = '<p><br><strong><em><u><s><ol><ul><li><a><blockquote><h1><h2><h3><h4><h5><h6><sub><sup><span><img>';
        $data_text = strip_tags($raw_data, $allowed_tags);

        $service_chains = $_POST['service_chains'] ?? [];
        $enabling_pillars = $_POST['enabling_pillars'] ?? [];
        // Map new challenges_sector to thematic_categories
        $thematic_categories = $_POST['challenges_sector'] ?? ($_POST['thematic_categories'] ?? []);
        $challenge_area = htmlspecialchars($_POST['challenge_area'] ?? ''); // Keep for backward compatibility if needed, else ignore

        // Fix: Re-key metadata by filename for easy lookup
        $media_meta_raw = json_decode($_POST['media_meta'] ?? '[]', true);
        $media_meta = [];
        if (is_array($media_meta_raw)) {
            foreach ($media_meta_raw as $m) {
                if (isset($m['name'])) {
                    $media_meta[$m['name']] = $m;
                }
            }
        }

        if (empty($service_chains)) {
            echo json_encode(['status' => 'error', 'message' => 'Please select at least one Service Chain category.']);
            exit;
        }

        $clean_text_len = mb_strlen(trim(strip_tags($raw_data))); // Text length without HTML tags
        $has_text = $clean_text_len > 0;

        // Fix: Check ANY uploaded file, not just the first one (which might be empty input)
        $has_file = false;
        if (!empty($_FILES['media_files']['name'])) {
            // Check if $_FILES['media_files']['name'] is array (multiple files)
            if (is_array($_FILES['media_files']['name'])) {
                foreach ($_FILES['media_files']['error'] as $key => $error) {
                    if ($error === UPLOAD_ERR_OK && !empty($_FILES['media_files']['name'][$key])) {
                        $has_file = true;
                        break;
                    }
                }
            } else {
                // Single file upload
                if ($_FILES['media_files']['error'] === UPLOAD_ERR_OK && !empty($_FILES['media_files']['name'])) {
                    $has_file = true;
                }
            }
        }

        if (!$has_text && !$has_file) {
            echo json_encode(['status' => 'error', 'message' => 'Please either provide a data description or upload a file.']);
            exit;
        }

        // 3. Calculate Cost
        $total_cost = 0;

        // A. Inline Images cost
        // Simple regex to count <img tags. 
        $inline_img_count = preg_match_all('/<img\s+/i', $raw_data);
        $total_cost += ($inline_img_count * COST_PER_IMAGE);

        // B. Text Cost (stripped of HTML)
        if ($clean_text_len > 0) {
            $total_cost += ($clean_text_len * COST_PER_CHAR);
        }

        $uploads = [];
        if (!empty($_FILES['media_files'])) {
            $files = $_FILES['media_files'];
            $file_count = count($files['name']);
            for ($i = 0; $i < $file_count; $i++) {
                if ($files['error'][$i] === UPLOAD_ERR_OK) {
                    $name = $files['name'][$i];
                    $type = $files['type'][$i];
                    $tmp_name = $files['tmp_name'][$i];
                    $size = $files['size'][$i];

                    // Security Check: Validate Extension
                    $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));
                    $allowed_extensions = ['jpg', 'jpeg', 'png', 'gif', 'webp', 'mp4', 'avi', 'mov', 'mkv', 'mp3', 'wav', 'ogg', 'pdf', 'doc', 'docx', 'ppt', 'pptx', 'xls', 'xlsx', 'txt'];
                    if (!in_array($ext, $allowed_extensions)) {
                        echo json_encode(['status' => 'error', 'message' => "File type not allowed: $name"]);
                        exit;
                    }

                    $file_cost = 0;
                    if (strpos($type, 'image') !== false) {
                        $file_cost = COST_PER_IMAGE;
                    } elseif (strpos($type, 'video') !== false) {
                        $val = $media_meta[$name] ?? 0;
                        $duration = is_array($val) ? ($val['duration'] ?? 0) : $val;
                        $file_cost = max(1, $duration) * COST_PER_VIDEO_SEC;
                    } elseif (strpos($type, 'audio') !== false) {
                        $val = $media_meta[$name] ?? 0;
                        $duration = is_array($val) ? ($val['duration'] ?? 0) : $val;
                        $file_cost = max(1, $duration) * COST_PER_AUDIO_SEC;
                    } elseif ($type === 'application/pdf') {
                        $val = $media_meta[$name] ?? 0;
                        $pdf_chars = is_array($val) ? ($val['chars'] ?? 0) : 0;
                        $file_cost = $pdf_chars * COST_PER_CHAR;
                    }

                    $total_cost += $file_cost;
                    $uploads[] = [
                        'tmp_name' => $tmp_name,
                        'name' => $name,
                        'type' => $type,
                        'mime' => $type,
                        'size' => $size
                    ];
                }
            }
        }

        // C. Multi-category surcharge (Service Chains + Enabling Pillars)
        $cat_count = count($service_chains) + count($enabling_pillars);
        if ($cat_count > 1) {
            $total_cost += ($cat_count - 1) * COST_PER_EXTRA_CATEGORY;
        }

        // 4. Transaction
        $conn->begin_transaction();
        try {
            // Check Balance
            $stmt = $conn->prepare("
                SELECT us.id, us.tokens_remaining, sp.name 
                FROM user_subscriptions us 
                JOIN subscription_packages sp ON us.package_id = sp.id 
                WHERE us.user_id = ? AND us.status = 'active' 
                FOR UPDATE
            ");
            if (!$stmt)
                throw new Exception("DB Error (Check Sub): " . $conn->error);

            $stmt->bind_param("i", $user_id);
            $stmt->execute();
            $res = $stmt->get_result();
            if ($res->num_rows === 0)
                throw new Exception("No active subscription found.");

            $sub = $res->fetch_assoc();

            // Check if user is on the free community tier
            if (strtolower(trim($sub['name'])) === 'community') {
                throw new Exception("Your current Community subscription does not allow data submission. Please upgrade your plan.");
            }

            $sub_id = $sub['id'];
            $balance = floatval($sub['tokens_remaining']);

            if ($balance < $total_cost) {
                throw new Exception("Insufficient tokens. Cost: $total_cost, Balance: $balance");
            }

            // Lock tokens (deducted from balance now, finalized on admin approval)
            $new_balance = $balance - $total_cost;
            $upd = $conn->prepare("UPDATE user_subscriptions SET tokens_remaining = ? WHERE id = ?");
            if (!$upd)
                throw new Exception("DB Error (Update Balance): " . $conn->error);

            $upd->bind_param("di", $new_balance, $sub_id);
            $upd->execute();

            // Log as 'lock' (will convert to 'debit' on approval, or 'refund' on rejection)
            $desc = "Submission: " . substr($title, 0, 20) . "...";
            $log = $conn->prepare("INSERT INTO token_transactions (user_id, subscription_id, amount, transaction_type, description) VALUES (?, ?, ?, 'lock', ?)");
            if (!$log)
                throw new Exception("DB Error (Log Transaction): " . $conn->error);
            $log->bind_param("iids", $user_id, $sub_id, $total_cost, $desc);
            $log->execute();

            // 5. Save Content
            $u_stmt = $conn->prepare("SELECT org_name, email FROM users WHERE id = ?");
            if (!$u_stmt)
                throw new Exception("DB Error (Get User): " . $conn->error);
            $u_stmt->bind_param("i", $user_id);
            $u_stmt->execute();
            $user_data = $u_stmt->get_result()->fetch_assoc();

            $chains_json = json_encode($service_chains);
            $pillars_json = json_encode($enabling_pillars);
            // Check if thematic categories are integers or strings and encode appropriately
            // The new form sends strings ("Access", "Funding", etc)
            $thematic_json = json_encode($thematic_categories);

            $org_sector_post = $_POST['org_sector'] ?? [];
            if (!empty($org_sector_post) && is_array($org_sector_post)) {
                if (isset($org_sector_post['sector']) && $org_sector_post['sector'] !== 'Others') {
                    $org_sector_post['other'] = ''; // Clear 'other' if not 'Others'
                }
                if (isset($org_sector_post['sector']) && $org_sector_post['sector'] === 'Others') {
                    $org_sector_post['sub_type'] = '';
                    $org_sector_post['specialisation'] = '';
                }
            } else {
                $org_sector_post = null;
            }
            $org_sector_json = $org_sector_post ? json_encode($org_sector_post) : null;

            $contact = $user_data['org_name'];
            $role = htmlspecialchars($_POST['submission_type'] ?? 'Member'); // Use submission_type if available
            $mobile = 'N/A';

            // Handle multiple counties
            $counties = $_POST['counties'] ?? [];
            if (!is_array($counties)) {
                $counties = !empty($_POST['county']) ? [$_POST['county']] : [];
            }
            // Store as comma-separated string for simplicity in DB (or JSON if preferred, but string matches existing pattern)
            $county = htmlspecialchars(implode(', ', $counties) ?: 'N/A');

            $description = htmlspecialchars($_POST['description'] ?? '');

            $ins_sub = $conn->prepare("INSERT INTO submissions (org_name, title, contact_person, email, mobile, story, description, role, org_sector, service_chains, enabling_pillars, thematic_categories, challenge_area, county, status, is_verified, category_id, user_id, last_token_cost, tokens_locked) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 0, 0, ?, ?, ?)");

            if (!$ins_sub) {
                error_log("Submission prepare failed: " . $conn->error);
                throw new Exception("DB Error (Insert Submission): " . $conn->error);
            }

            $ins_sub->bind_param("ssssssssssssssiid", $user_data['org_name'], $title, $contact, $user_data['email'], $mobile, $data_text, $description, $role, $org_sector_json, $chains_json, $pillars_json, $thematic_json, $challenge_area, $county, $user_id, $total_cost, $total_cost);
            if (!$ins_sub->execute()) {
                error_log("Submission execute failed: " . $ins_sub->error);
                throw new Exception("DB Error executing submission insert: " . $ins_sub->error);
            }
            $submission_id = $ins_sub->insert_id;
            error_log("Submission inserted successfully, ID: $submission_id");

            // Save Files
            // Collect captions from form data
            $media_captions = $_POST['media_captions'] ?? [];

            foreach ($uploads as $file) {
                $upload_dir = SECURE_UPLOAD_PATH . 'uploads/';
                if (!is_dir($upload_dir))
                    mkdir($upload_dir, 0777, true);

                $ext = pathinfo($file['name'], PATHINFO_EXTENSION);
                $new_filename = uniqid() . '.' . $ext;
                $target_path = $upload_dir . $new_filename;

                if (move_uploaded_file($file['tmp_name'], $target_path)) {
                    // Proper media type classification
                    $mime = $file['type'];
                    if (strpos($mime, 'image') !== false) {
                        $clean_type = 'image';
                    } elseif (strpos($mime, 'video') !== false) {
                        $clean_type = 'video';
                    } elseif (strpos($mime, 'audio') !== false) {
                        $clean_type = 'audio';
                    } elseif ($mime === 'application/pdf') {
                        $clean_type = 'pdf';
                    } else {
                        $clean_type = 'text';
                    }

                    // Get caption for this file if provided
                    $caption = $media_captions[$file['name']] ?? null;
                    $original_name = $file['name'];
                    // Store relative path for serve_file.php
                    $relative_path = 'uploads/' . $new_filename;

                    $ins_media = $conn->prepare("INSERT INTO media_uploads (user_id, submission_id, subscription_id, media_type, file_path, file_size, mime_type, title, caption, original_filename, status) VALUES (?, ?, 0, ?, ?, ?, ?, ?, ?, ?, 'pending')");
                    if ($ins_media) {
                        $file_mime = $file['type'] ?? '';
                        $ins_media->bind_param("iississss", $user_id, $submission_id, $clean_type, $relative_path, $file['size'], $file_mime, $title, $caption, $original_name);
                        if (!$ins_media->execute()) {
                            error_log("Media insert failed: " . $ins_media->error);
                        }
                    } else {
                        error_log("Media prepare failed: " . $conn->error);
                    }
                }
            }

            $conn->commit();

            // --- Send Email Notification ---
            try {
                require_once 'smtp_mailer.php';
                $mailer = new SmtpMailer(SMTP_HOST, SMTP_PORT, SMTP_USER, SMTP_PASS);
                $admin_email = "ewambui@washvoiceafrica.org";
                $submitter_name = htmlspecialchars($user_data['org_name'] ?? 'A User');
                $submission_title = htmlspecialchars($title);
                $review_link = "http://" . $_SERVER['HTTP_HOST'] . "/admin/submissions.php";

                $subject = "New Data Submission: " . $submission_title;
                $message = "<h2>New SaniBook Data Submission</h2>";
                $message .= "<p>A new data submission titled '<strong>{$submission_title}</strong>' has been submitted by <strong>{$submitter_name}</strong>.</p>";
                $message .= "<p>Please <a href='{$review_link}'>click here to review it</a> in the Admin Dashboard.</p>";

                $mailer->send($admin_email, $subject, $message);
            } catch (Exception $email_ex) {
                // Log email error, but do not fail the submission
                error_log("Failed to send submission email notification: " . $email_ex->getMessage());
            }

            echo json_encode(['status' => 'success', 'message' => 'Data submitted successfully!', 'remaining_balance' => $new_balance]);

        } catch (Exception $ex) {
            $conn->rollback();
            throw $ex;
        }
        exit;
    }

    // --------------------------------------------------------------------------
    // ACTION: SAVE DRAFT
    // --------------------------------------------------------------------------
    if ($action === 'save_draft') {
        // 1. Auth Check
        if (!isset($_SESSION['user_id'])) {
            throw new Exception("Unauthorized. Please log in.");
        }
        $user_id = $_SESSION['user_id'];

        // 2. Get Inputs
        $title = htmlspecialchars($_POST['title'] ?? 'Untitled Draft');
        $raw_data = $_POST['data'] ?? '';
        $allowed_tags = '<p><br><strong><em><u><s><ol><ul><li><a><blockquote><h1><h2><h3><h4><h5><h6><sub><sup><span><img>';
        $data_text = strip_tags($raw_data, $allowed_tags);

        $service_chains = $_POST['service_chains'] ?? [];
        $enabling_pillars = $_POST['enabling_pillars'] ?? [];
        $thematic_categories = $_POST['challenges_sector'] ?? ($_POST['thematic_categories'] ?? []);
        $challenge_area = htmlspecialchars($_POST['challenge_area'] ?? '');

        // Fetch User Info
        $u_stmt = $conn->prepare("SELECT org_name, email FROM users WHERE id = ?");
        if (!$u_stmt)
            throw new Exception("DB Error (Get User): " . $conn->error);
        $u_stmt->bind_param("i", $user_id);
        $u_stmt->execute();
        $user_data = $u_stmt->get_result()->fetch_assoc();

        $chains_json = json_encode($service_chains);
        $pillars_json = json_encode($enabling_pillars);
        $thematic_json = json_encode($thematic_categories);

        $org_sector_post = $_POST['org_sector'] ?? [];
        if (!empty($org_sector_post) && is_array($org_sector_post)) {
            if (isset($org_sector_post['sector']) && $org_sector_post['sector'] !== 'Others') {
                $org_sector_post['other'] = ''; // Clear 'other' if not 'Others'
            }
            if (isset($org_sector_post['sector']) && $org_sector_post['sector'] === 'Others') {
                $org_sector_post['sub_type'] = '';
                $org_sector_post['specialisation'] = '';
            }
        } else {
            $org_sector_post = null;
        }
        $org_sector_json = $org_sector_post ? json_encode($org_sector_post) : null;

        $contact = $user_data['org_name'];
        $role = htmlspecialchars($_POST['submission_type'] ?? 'Member');
        $mobile = 'N/A';

        $counties = $_POST['counties'] ?? [];
        if (!is_array($counties)) {
            $counties = !empty($_POST['county']) ? [$_POST['county']] : [];
        }
        $county = htmlspecialchars(implode(', ', $counties) ?: 'N/A');

        $description = htmlspecialchars($_POST['description'] ?? '');

        $conn->begin_transaction();
        try {
            $draft_id = isset($_POST['draft_id']) ? intval($_POST['draft_id']) : 0;

            if ($draft_id > 0) {
                // Update existing draft
                $upd_sub = $conn->prepare("UPDATE submissions SET title = ?, story = ?, description = ?, role = ?, org_sector = ?, service_chains = ?, enabling_pillars = ?, thematic_categories = ?, challenge_area = ?, county = ? WHERE id = ? AND user_id = ? AND status = 'draft'");
                if (!$upd_sub)
                    throw new Exception("DB Error (Update Draft): " . $conn->error);
                $upd_sub->bind_param("ssssssssssii", $title, $data_text, $description, $role, $org_sector_json, $chains_json, $pillars_json, $thematic_json, $challenge_area, $county, $draft_id, $user_id);
                $upd_sub->execute();
                $submission_id = $draft_id;
            } else {
                // Save as new 'draft'
                $ins_sub = $conn->prepare("INSERT INTO submissions (org_name, title, contact_person, email, mobile, story, description, role, org_sector, service_chains, enabling_pillars, thematic_categories, challenge_area, county, status, is_verified, category_id, user_id, last_token_cost, tokens_locked) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'draft', 0, 0, ?, 0, 0)");

                if (!$ins_sub)
                    throw new Exception("DB Error (Insert Draft): " . $conn->error);

                $ins_sub->bind_param("ssssssssssssssi", $user_data['org_name'], $title, $contact, $user_data['email'], $mobile, $data_text, $description, $role, $org_sector_json, $chains_json, $pillars_json, $thematic_json, $challenge_area, $county, $user_id);
                if (!$ins_sub->execute())
                    throw new Exception("DB Error executing draft insert: " . $ins_sub->error);
                $submission_id = $ins_sub->insert_id;
            }

            // Save Files (optional for draft, but good to save if they uploaded)
            if (!empty($_FILES['media_files']['name']) && is_array($_FILES['media_files']['name'])) {
                $files = $_FILES['media_files'];
                $upload_dir = SECURE_UPLOAD_PATH . 'uploads/';
                if (!is_dir($upload_dir))
                    mkdir($upload_dir, 0777, true);

                $media_captions = $_POST['media_captions'] ?? [];

                for ($i = 0; $i < count($files['name']); $i++) {
                    if ($files['error'][$i] === UPLOAD_ERR_OK) {
                        $name = $files['name'][$i];
                        $type = $files['type'][$i];
                        $tmp_name = $files['tmp_name'][$i];
                        $size = $files['size'][$i];
                        $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION));

                        $allowed_extensions = ['jpg', 'jpeg', 'png', 'gif', 'webp', 'mp4', 'avi', 'mov', 'mkv', 'mp3', 'wav', 'ogg', 'pdf', 'doc', 'docx', 'ppt', 'pptx', 'xls', 'xlsx', 'txt'];
                        if (!in_array($ext, $allowed_extensions)) {
                            // Skip invalid files quietly in draft mode or handle error, here we skip
                            continue;
                        }
                        $ext = pathinfo($name, PATHINFO_EXTENSION);

                        $clean_type = (strpos($type, 'image') !== false) ? 'image' :
                            ((strpos($type, 'video') !== false) ? 'video' :
                                ((strpos($type, 'audio') !== false) ? 'audio' :
                                    (($type === 'application/pdf') ? 'document' : 'other')));

                        $caption = htmlspecialchars($media_captions[$name] ?? '');
                        $new_filename = uniqid() . '.' . $ext;
                        $target_path = $upload_dir . $new_filename;

                        if (move_uploaded_file($tmp_name, $target_path)) {
                            $relative_path = 'uploads/' . $new_filename;
                            $ins_media = $conn->prepare("INSERT INTO media_uploads (user_id, submission_id, subscription_id, media_type, file_path, file_size, mime_type, title, caption, original_filename, status) VALUES (?, ?, 0, ?, ?, ?, ?, ?, ?, ?, 'draft')");
                            if ($ins_media) {
                                $ins_media->bind_param("iississss", $user_id, $submission_id, $clean_type, $relative_path, $size, $type, $title, $caption, $name);
                                $ins_media->execute();
                            }
                        }
                    }
                }
            }

            $conn->commit();
            echo json_encode(['status' => 'success', 'message' => 'Draft saved successfully!', 'draft_id' => $submission_id]);
        } catch (Exception $ex) {
            $conn->rollback();
            throw $ex;
        }
        exit;
    }

    // --------------------------------------------------------------------------
    // ACTION: UPDATE data
    // --------------------------------------------------------------------------
    if ($action === 'update_data') {
        if (!isset($_SESSION['user_id']))
            throw new Exception("Unauthorized.");
        $user_id = $_SESSION['user_id'];
        $is_admin = isset($_SESSION['role']) && $_SESSION['role'] === 'admin';
        $edit_id = intval($_POST['edit_id'] ?? 0);
        $title = htmlspecialchars($_POST['title'] ?? '');
        $allowed_tags = '<p><br><strong><em><u><s><ol><ul><li><a><blockquote><h1><h2><h3><h4><h5><h6><sub><sup><span><img>';
        $data_text = strip_tags($_POST['data'] ?? '', $allowed_tags);
        $service_chains = $_POST['service_chains'] ?? [];
        $enabling_pillars = $_POST['enabling_pillars'] ?? [];
        $thematic_categories = $_POST['thematic_categories'] ?? [];
        $county = htmlspecialchars($_POST['county'] ?? 'N/A');

        // Simple cost logic for edit: Fixed fee 20
        $pay_amount = $is_admin ? 0 : 20;

        $conn->begin_transaction();
        try {
            // Deduct Fee (skip for admin)
            $new_balance = 0;
            if (!$is_admin) {
                $stmt = $conn->prepare("SELECT id, tokens_remaining FROM user_subscriptions WHERE user_id = ? AND status = 'active' FOR UPDATE");
                if (!$stmt)
                    throw new Exception("DB Error (Check Sub): " . $conn->error);
                $stmt->bind_param("i", $user_id);
                $stmt->execute();
                $sub = $stmt->get_result()->fetch_assoc();

                if (!$sub || $sub['tokens_remaining'] < $pay_amount)
                    throw new Exception("Insufficient tokens for edit.");

                $new_balance = $sub['tokens_remaining'] - $pay_amount;
                $conn->query("UPDATE user_subscriptions SET tokens_remaining = $new_balance WHERE id = " . $sub['id']);
            }

            // Update
            $chains_json = json_encode($service_chains);
            $pillars_json = json_encode($enabling_pillars);
            $thematic_json = json_encode($thematic_categories);

            $org_sector_post = $_POST['org_sector'] ?? [];
            if (!empty($org_sector_post) && is_array($org_sector_post)) {
                if (isset($org_sector_post['sector']) && $org_sector_post['sector'] !== 'Others') {
                    $org_sector_post['other'] = ''; // Clear 'other' if not 'Others'
                }
                if (isset($org_sector_post['sector']) && $org_sector_post['sector'] === 'Others') {
                    $org_sector_post['sub_type'] = '';
                    $org_sector_post['specialisation'] = '';
                }
            } else {
                $org_sector_post = null;
            }
            $org_sector_json = $org_sector_post ? json_encode($org_sector_post) : null;

            if ($is_admin) {
                $upd_sub = $conn->prepare("UPDATE submissions SET title = ?, story = ?, org_sector = ?, service_chains = ?, enabling_pillars = ?, thematic_categories = ?, county = ?, edit_count = edit_count + 1 WHERE id = ?");
                if (!$upd_sub)
                    throw new Exception("DB Error (Update Query): " . $conn->error);
                $upd_sub->bind_param("sssssssi", $title, $data_text, $org_sector_json, $chains_json, $pillars_json, $thematic_json, $county, $edit_id);
            } else {
                $upd_sub = $conn->prepare("UPDATE submissions SET title = ?, story = ?, org_sector = ?, service_chains = ?, enabling_pillars = ?, thematic_categories = ?, county = ?, edit_count = edit_count + 1 WHERE id = ? AND user_id = ?");
                if (!$upd_sub)
                    throw new Exception("DB Error (Update Query): " . $conn->error);
                $upd_sub->bind_param("sssssssii", $title, $data_text, $org_sector_json, $chains_json, $pillars_json, $thematic_json, $county, $edit_id, $user_id);
            }
            $upd_sub->execute();

            // Handle media deletions
            if (!empty($_POST['delete_media']) && is_array($_POST['delete_media'])) {
                foreach ($_POST['delete_media'] as $media_id) {
                    $media_id = intval($media_id);
                    // Verify media belongs to this submission
                    $chk = $conn->prepare("SELECT id, file_path FROM media_uploads WHERE id = ? AND submission_id = ?");
                    $chk->bind_param("ii", $media_id, $edit_id);
                    $chk->execute();
                    $m_row = $chk->get_result()->fetch_assoc();
                    if ($m_row) {
                        // Delete file from disk
                        $full_path = SECURE_UPLOAD_PATH . $m_row['file_path'];
                        if (file_exists($full_path)) {
                            unlink($full_path);
                        }
                        // Delete DB record
                        $del = $conn->prepare("DELETE FROM media_uploads WHERE id = ?");
                        $del->bind_param("i", $media_id);
                        $del->execute();
                    }
                }
            }

            $conn->commit();
            echo json_encode(['status' => 'success', 'message' => 'Data updated!', 'remaining_balance' => $new_balance]);
        } catch (Exception $ex) {
            $conn->rollback();
            throw $ex;
        }
        exit;
    }

    // --------------------------------------------------------------------------
    // ACTION: REGISTER (Guest)
    // --------------------------------------------------------------------------
    if ($action === 'submit_data') {
        $input = json_decode(file_get_contents('php://input'), true);
        if ($input)
            $_POST = array_merge($_POST, $input);

        $org_name = htmlspecialchars($_POST['org_name'] ?? '');
        $title = htmlspecialchars($_POST['title'] ?? '');
        $email = htmlspecialchars($_POST['email'] ?? '');
        $mobile = htmlspecialchars($_POST['mobile'] ?? '');
        $contact_person = htmlspecialchars($_POST['contact_person'] ?? '');
        $contact_role = htmlspecialchars($_POST['contact_role'] ?? 'Member');
        $allowed_tags_story = '<p><br><strong><em><u><s><ol><ul><li><a><blockquote><h1><h2><h3><h4><h5><h6><sub><sup><span><img>';
        $data = strip_tags($_POST['description'] ?? $_POST['data'] ?? '', $allowed_tags_story);
        $password = $_POST['password'] ?? 'default';
        $category_id = intval($_POST['category_id'] ?? 0);
        $challenge_area = htmlspecialchars($_POST['challenge_area'] ?? '');
        $service_chains = $_POST['service_chains'] ?? [];
        $enabling_pillars = $_POST['enabling_pillars'] ?? [];
        $thematic_categories = $_POST['challenges_sector'] ?? ($_POST['thematic_categories'] ?? []);
        $county = htmlspecialchars($_POST['county'] ?? 'N/A');
        $chains_json = json_encode($service_chains);
        $pillars_json = json_encode($enabling_pillars);
        $thematic_json = json_encode($thematic_categories);

        $org_sector_post = $_POST['org_sector'] ?? [];
        if (!empty($org_sector_post) && is_array($org_sector_post)) {
            if (isset($org_sector_post['sector']) && $org_sector_post['sector'] !== 'Others') {
                $org_sector_post['other'] = ''; // Clear 'other' if not 'Others'
            }
            if (isset($org_sector_post['sector']) && $org_sector_post['sector'] === 'Others') {
                $org_sector_post['sub_type'] = '';
                $org_sector_post['specialisation'] = '';
            }
        } else {
            $org_sector_post = null;
        }
        $org_sector_json = $org_sector_post ? json_encode($org_sector_post) : null;

        $description = htmlspecialchars($_POST['description'] ?? '');

        $otp = substr(md5(uniqid()), 0, 10);
        $password_hash = password_hash($password, PASSWORD_BCRYPT);

        $sql = "INSERT INTO submissions (org_name, title, contact_person, role, org_sector, email, mobile, story, description, service_chains, enabling_pillars, thematic_categories, challenge_area, county, verification_code, is_verified, password_hash, category_id, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, 'pending')";
        $stmt = $conn->prepare($sql);

        if (!$stmt) {
            error_log("Register prepare failed: " . $conn->error);
            throw new Exception("DB Error (Register): " . $conn->error);
        }

        $stmt->bind_param("ssssssssssssssssi", $org_name, $title, $contact_person, $contact_role, $org_sector_json, $email, $mobile, $data, $description, $chains_json, $pillars_json, $thematic_json, $challenge_area, $county, $otp, $password_hash, $category_id);

        if ($stmt->execute()) {
            $new_id = $stmt->insert_id;

            // Sync to Users Table
            $u_sql = "INSERT INTO users (org_name, email, password_hash, is_verified, verification_code) VALUES (?, ?, ?, 0, ?)";
            $u_stmt = $conn->prepare($u_sql);
            if ($u_stmt) {
                $u_stmt->bind_param("ssss", $org_name, $email, $password_hash, $otp);
                $u_stmt->execute();
                $new_u_id = $u_stmt->insert_id;

                // Assign user_id to the submission
                $conn->query("UPDATE submissions SET user_id = $new_u_id WHERE id = $new_id");

                // Assign default tokens
                $conn->query("INSERT INTO user_subscriptions (user_id, package_id, status, tokens_remaining) VALUES ($new_u_id, 1, 'active', 2000)");
            }

            // Send Email
            require_once 'smtp_mailer.php';
            $mailer = new SmtpMailer(SMTP_HOST, SMTP_PORT, SMTP_USER, SMTP_PASS);
            $verify_link = "http://" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/verify.php?email=" . urlencode($email) . "&code=" . $otp;
            $subject = "Verify your SaniBook Account";
            $message = "<h2>Welcome to SaniBook!</h2><p>Your data was successfully submitted. Please click the link below to verify your email address to log in and manage your submissions:</p><p><a href='$verify_link'>Verify My Account</a></p>";
            try {
                $mailer->send($email, $subject, $message);
            } catch (Exception $e) {
                error_log("Failed to send verification email for guest: " . $e->getMessage());
            }

            echo json_encode(['status' => 'pending_verification', 'submission_id' => $new_id]);
        } else {
            throw new Exception("Execution failed: " . $stmt->error);
        }
        exit;
    }

    // --------------------------------------------------------------------------
    // ACTION: VERIFY OTP
    // --------------------------------------------------------------------------
    if ($action === 'verify_otp') {
        $submission_id = intval($_POST['submission_id'] ?? 0);
        $user_otp = htmlspecialchars($_POST['otp'] ?? '');

        if ($submission_id === 0 || empty($user_otp)) {
            throw new Exception("Invalid submission ID or OTP.");
        }

        $stmt = $conn->prepare("SELECT id, email, org_name, contact_person FROM submissions WHERE id = ? AND verification_code = ?");
        if (!$stmt)
            throw new Exception("DB Error (Verify OTP Select): " . $conn->error);

        $stmt->bind_param("is", $submission_id, $user_otp);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($row = $result->fetch_assoc()) {
            // Update to Verified (Submission)
            $update = $conn->prepare("UPDATE submissions SET is_verified = 1 WHERE id = ?");
            if (!$update)
                throw new Exception("DB Error (Verify OTP Update Sub): " . $conn->error);
            $update->bind_param("i", $submission_id);
            $update->execute();

            // Sync Verification to Users Table
            $u_upd = $conn->prepare("UPDATE users SET is_verified = 1 WHERE email = ?");
            if (!$u_upd)
                throw new Exception("DB Error (Verify OTP Update User): " . $conn->error);
            $u_upd->bind_param("s", $row['email']);
            $u_upd->execute();

            // Welcome Email
            $subject = "Welcome to SaniBook - Registration Complete";
            $message = "Registration confirmed for {$row['org_name']}.";
            $mailer->send($row['email'], $subject, $message);

            echo json_encode(['status' => 'success']);
        } else {
            echo json_encode(['status' => 'error', 'message' => 'Incorrect verification code.']);
        }
        exit;
    }

} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
    exit;
} finally {
    if (isset($conn) && $conn instanceof mysqli) {
        $conn->close();
    }
}
?>