<?php
require_once __DIR__ . '/auth.php';
require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/db_bootstrap.php';

$conn = ensure_db(true);

if (!function_exists('finance_table_exists')) {
  function finance_table_exists(mysqli $conn, string $table): bool {
    $t = $conn->real_escape_string($table);
    $res = $conn->query("SHOW TABLES LIKE '{$t}'");
    return ($res instanceof mysqli_result) && ($res->num_rows > 0);
  }
}

if (!function_exists('finance_column_exists')) {
  function finance_column_exists(mysqli $conn, string $table, string $column): bool {
    $t = $conn->real_escape_string($table);
    $c = $conn->real_escape_string($column);
    $res = $conn->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    return ($res instanceof mysqli_result) && ($res->num_rows > 0);
  }
}

// Some pages were written with different helper names
if (!function_exists('finance_table_has_col')) {
  function finance_table_has_col(mysqli $conn, string $table, string $column): bool {
    return finance_column_exists($conn, $table, $column);
  }
}

// Pick the first existing column name from a list (returns a safe backticked identifier)
if (!function_exists('finance_pick_col')) {
  function finance_pick_col(mysqli $conn, string $table, array $candidates): ?string {
    foreach ($candidates as $col) {
      $col = (string)$col;
      if ($col !== '' && finance_column_exists($conn, $table, $col)) {
        // backtick to avoid reserved words issues
        return '`' . str_replace('`', '', $col) . '`';
      }
    }
    return null;
  }
}

if (!function_exists('finance_migrate')) {
  function finance_migrate(mysqli $conn): void {
    // payments
    $conn->query("CREATE TABLE IF NOT EXISTS `payments` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `invoice_id` INT NULL,
      `subscriber_id` INT NULL,
      `amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
      `method` VARCHAR(50) NULL,
      `ref` VARCHAR(120) NULL,
      `reference` VARCHAR(120) NULL,
      `note` VARCHAR(255) NULL,
      `created_by` VARCHAR(100) NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `paid_at` DATETIME NULL,
      INDEX (`invoice_id`), INDEX (`subscriber_id`), INDEX (`created_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // expenses
    $conn->query("CREATE TABLE IF NOT EXISTS `expenses` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `title` VARCHAR(120) NOT NULL,
      `category` VARCHAR(60) NULL,
      `amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
      `spent_at` DATE NOT NULL,
      `expense_date` DATE NULL,
      `note` VARCHAR(255) NULL,
      `created_by` VARCHAR(100) NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      INDEX (`spent_at`), INDEX (`category`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // cashbox_daily
    $conn->query("CREATE TABLE IF NOT EXISTS `cashbox_daily` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `day` DATE NOT NULL,
      `opening_balance` DECIMAL(10,2) NOT NULL DEFAULT 0,
      `closing_balance` DECIMAL(10,2) NULL,
      `closed_by` VARCHAR(100) NULL,
      `closed_at` DATETIME NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY (`day`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // month closings
    $conn->query("CREATE TABLE IF NOT EXISTS `month_closings` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `year` INT NOT NULL,
      `month` INT NOT NULL,
      `closed_by` VARCHAR(100) NULL,
      `closed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `archived_at` DATETIME NULL,
      `totals_json` LONGTEXT NULL,
      UNIQUE KEY (`year`,`month`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");


    // ---- compatibility migrations (older/newer page variants) ----
    if (finance_table_exists($conn, 'payments')) {
      if (!finance_column_exists($conn, 'payments', 'reference')) {
        @$conn->query("ALTER TABLE `payments` ADD COLUMN `reference` VARCHAR(120) NULL");
      }
      if (!finance_column_exists($conn, 'payments', 'ref')) {
        @$conn->query("ALTER TABLE `payments` ADD COLUMN `ref` VARCHAR(120) NULL");
      }
      if (!finance_column_exists($conn, 'payments', 'paid_at')) {
        @$conn->query("ALTER TABLE `payments` ADD COLUMN `paid_at` DATETIME NULL");
      }
      // best-effort: keep paid_at filled
      @$conn->query("UPDATE payments SET paid_at = COALESCE(paid_at, created_at) WHERE paid_at IS NULL");
      // best-effort: keep reference/ref mirrored
      @$conn->query("UPDATE payments SET reference = COALESCE(reference, ref) WHERE reference IS NULL");
      @$conn->query("UPDATE payments SET ref = COALESCE(ref, reference) WHERE ref IS NULL");
    }

    if (finance_table_exists($conn, 'expenses')) {
      if (!finance_column_exists($conn, 'expenses', 'expense_date')) {
        @$conn->query("ALTER TABLE `expenses` ADD COLUMN `expense_date` DATE NULL");
      }
      if (!finance_column_exists($conn, 'expenses', 'spent_at')) {
        @$conn->query("ALTER TABLE `expenses` ADD COLUMN `spent_at` DATE NULL");
      }
      if (!finance_column_exists($conn, 'expenses', 'expense_status')) {
        @$conn->query("ALTER TABLE `expenses` ADD COLUMN `expense_status` VARCHAR(20) NOT NULL DEFAULT 'POSTED'");
      }
      // best-effort: mirror both fields
      @$conn->query("UPDATE expenses SET expense_date = COALESCE(expense_date, spent_at) WHERE expense_date IS NULL");
      @$conn->query("UPDATE expenses SET spent_at = COALESCE(spent_at, expense_date) WHERE spent_at IS NULL");
    }

    if (finance_table_exists($conn, 'cashbox_daily')) {
      if (!finance_column_exists($conn, 'cashbox_daily', 'created_at')) {
        @$conn->query("ALTER TABLE `cashbox_daily` ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");
      }
      if (!finance_column_exists($conn, 'cashbox_daily', 'archived_at')) {
        @$conn->query("ALTER TABLE `cashbox_daily` ADD COLUMN `archived_at` DATETIME NULL");
      }
      if (!finance_column_exists($conn, 'cashbox_daily', 'totals_json')) {
        @$conn->query("ALTER TABLE `cashbox_daily` ADD COLUMN `totals_json` LONGTEXT NULL");
      }
    }


    if (finance_table_exists($conn, 'month_closings')) {
      if (!finance_column_exists($conn, 'month_closings', 'archived_at')) {
        @$conn->query("ALTER TABLE `month_closings` ADD COLUMN `archived_at` DATETIME NULL");
      }
      if (!finance_column_exists($conn, 'month_closings', 'totals_json')) {
        @$conn->query("ALTER TABLE `month_closings` ADD COLUMN `totals_json` LONGTEXT NULL");
      }
    }


    // Try to enhance invoices table if exists
    if (finance_table_exists($conn, 'invoices')) {
      if (!finance_column_exists($conn, 'invoices', 'paid_amount')) {
        @$conn->query("ALTER TABLE `invoices` ADD COLUMN `paid_amount` DECIMAL(10,2) NOT NULL DEFAULT 0");
      }
      if (!finance_column_exists($conn, 'invoices', 'remaining_amount')) {
        @$conn->query("ALTER TABLE `invoices` ADD COLUMN `remaining_amount` DECIMAL(10,2) NOT NULL DEFAULT 0");
      }
      if (!finance_column_exists($conn, 'invoices', 'payment_status')) {
        @$conn->query("ALTER TABLE `invoices` ADD COLUMN `payment_status` VARCHAR(20) NOT NULL DEFAULT 'unpaid'");
      }
      if (!finance_column_exists($conn, 'invoices', 'updated_at')) {
        @$conn->query("ALTER TABLE `invoices` ADD COLUMN `updated_at` DATETIME NULL");
      }
    }
  }
}

finance_migrate($conn);
require_once __DIR__ . '/finance_daily_cashbox_lib.php';
finance_daily_migrate($conn);

// Recalculate invoice paid/remaining/payment_status after adding/deleting payments
if (!function_exists('finance_recalc_invoice')) {
  function finance_recalc_invoice(mysqli $conn, int $invoice_id): void {
    $invoice_id = (int)$invoice_id;
    if ($invoice_id <= 0) return;
    if (!finance_table_exists($conn, 'invoices') || !finance_table_exists($conn, 'payments')) return;

    $total_col = finance_pick_col($conn,'invoices',['amount','total_amount','total','price','invoice_total']);
    if (!$total_col) return;

    $paid_col = finance_table_has_col($conn,'invoices','paid_amount') ? 'paid_amount' : null;
    $rem_col  = finance_table_has_col($conn,'invoices','remaining_amount') ? 'remaining_amount' : null;
    $ps_col   = finance_table_has_col($conn,'invoices','payment_status') ? 'payment_status' : null;
    $st_col   = finance_table_has_col($conn,'invoices','status') ? 'status' : null;

    $paymentFilter = finance_table_has_col($conn, 'payments', 'payment_status')
      ? " AND payment_status='POSTED'"
      : '';
    $res = $conn->query("SELECT COALESCE(SUM(amount),0) AS s FROM payments WHERE invoice_id={$invoice_id}{$paymentFilter}");
    $paid = 0.0;
    if ($res && ($row=$res->fetch_assoc())) $paid = (float)$row['s'];

    $creditSelect = finance_table_has_col($conn, 'invoices', 'credit_applied') ? ',COALESCE(credit_applied,0) AS credit_applied' : ',0 AS credit_applied';
    $res2 = $conn->query("SELECT {$total_col} AS t{$creditSelect} FROM invoices WHERE id={$invoice_id} LIMIT 1");
    if (!($res2 && ($r2=$res2->fetch_assoc()))) return;
    $total = (float)($r2['t'] ?? 0);
    $paid += (float)($r2['credit_applied'] ?? 0);

    $remaining = max(0.0, $total - $paid);

    $ps = 'unpaid';
    if ($paid > 0.00001 && $remaining > 0.00001) $ps = 'partial';
    if ($remaining <= 0.00001 && $total > 0.00001) $ps = 'paid';

    // Arabic status compatibility
    $status_ar = 'غير مدفوع';
    if ($ps === 'partial') $status_ar = 'مدفوع جزئياً';
    if ($ps === 'paid') $status_ar = 'مدفوع';

    $sets = [];
    if ($paid_col) $sets[] = "`{$paid_col}`=".(float)$paid;
    if ($rem_col)  $sets[] = "`{$rem_col}`=".(float)$remaining;
    if ($ps_col)   $sets[] = "`{$ps_col}`='".$conn->real_escape_string($ps)."'";
    if ($st_col)   $sets[] = "`{$st_col}`='".$conn->real_escape_string($status_ar)."'";
    if (finance_table_has_col($conn,'invoices','updated_at')) $sets[] = "updated_at=NOW()";

    if ($sets) {
      $conn->query("UPDATE invoices SET ".implode(',', $sets)." WHERE id={$invoice_id} LIMIT 1");
    }
  }
}

if (!function_exists('finance_is_month_closed')) {
  function finance_is_month_closed(mysqli $conn, int $year, int $month): bool {
    $y = (int)$year; $m = (int)$month;
    $stmt = $conn->prepare("SELECT id FROM month_closings WHERE year=? AND month=? LIMIT 1");
    if (!$stmt) return false;
    $stmt->bind_param('ii', $y, $m);
    $stmt->execute();
    $res = $stmt->get_result();
    $ok = $res && $res->num_rows > 0;
    $stmt->close();
    return $ok;
  }
}



if (!function_exists('finance_is_day_closed')) {
  function finance_is_day_closed(mysqli $conn, string $day): bool {
    $d = trim($day);
    if ($d === '') return false;
    $stmt = $conn->prepare("SELECT id FROM cashbox_daily WHERE day=? AND closed_at IS NOT NULL LIMIT 1");
    if (!$stmt) return false;
    $stmt->bind_param('s', $d);
    $stmt->execute();
    $res = $stmt->get_result();
    $ok = $res && $res->num_rows > 0;
    $stmt->close();
    return $ok;
  }
}
