<?php
// ============================================================
// billing-back.php — RBE Utilities Unified Billing Backend (v10)
// ------------------------------------------------------------
// Handles: Fees, Tiers, Fee Modes, Settings, History
// Role: Admin + Landlord compatible
// Version-safe: auto-archives previous billing_settings into history
// JSON-consistent responses for billing.js frontend
// ============================================================

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();
require_once __DIR__ . '/../../core/db_connect.php';
require_once __DIR__ . '/../../core/functions.php';
header('Content-Type: application/json');

// ------------------------------------------------------------
// Helpers
// ------------------------------------------------------------
function res($status, $msg = '', $data = null) {
  echo json_encode(['status' => $status, 'message' => $msg, 'data' => $data], JSON_UNESCAPED_SLASHES);
  exit;
}
function esc($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }

// ------------------------------------------------------------
// Auth & Role Validation
// ------------------------------------------------------------
if (!isset($_SESSION['user_id'])) res('error', 'Not logged in');

$user_id = (int)$_SESSION['user_id'];
$stmt = $conn_rbe->prepare("SELECT role FROM rbe_roles WHERE user_id=? LIMIT 1");
$stmt->execute([$user_id]);
$role = $stmt->fetchColumn();

if (!in_array($role, ['admin','landlord'])) res('error', 'Access denied');
$is_admin = ($role === 'admin');

// landlord_id is set by GET or from session
$landlord_id = $is_admin && !empty($_GET['landlord_id']) ? (int)$_GET['landlord_id'] : $user_id;

// ------------------------------------------------------------
// Determine Action
// ------------------------------------------------------------
$action = $_GET['action'] ?? $_POST['action'] ?? null;
if (!$action) res('error', 'No action provided');

// ------------------------------------------------------------
// Wrap logic in try/catch
// ------------------------------------------------------------
try {

  // ============================================================
  // 1️⃣ GET TENANTS
  // ============================================================
  if ($action === 'get_tenants') {
    // Prefer explicit landlord_id if provided (admin mode), otherwise fallback to scoped $landlord_id
    $lid = isset($_GET['landlord_id']) && $_GET['landlord_id'] !== '' ? (int)$_GET['landlord_id'] : (int)$landlord_id;
    if ($lid <= 0) res('error', 'Missing landlord_id parameter', []);

    $stmt = $conn_rbe->prepare(
      "SELECT 
          tlm.tenant_id,
          u.fullname,
          p.property_number,
          p.address
       FROM tenant_landlord_map tlm
       JOIN rbe_users u ON u.id = tlm.tenant_id
       LEFT JOIN properties p ON p.id = tlm.property_id
       WHERE tlm.landlord_id = ?
       ORDER BY u.fullname ASC"
    );
    $stmt->execute([$lid]);
    $tenants = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (!$tenants) res('error', 'No tenants found for landlord', []);
    res('success', 'Tenants loaded', $tenants);
  }

  if ($action === 'get_properties') {
    $stmt = $conn_rbe->prepare("SELECT id, property_number FROM properties WHERE landlord_id=? ORDER BY property_number ASC");
    $stmt->execute([$landlord_id]);
    res('success','', $stmt->fetchAll(PDO::FETCH_ASSOC));
  }

  // ============================================================
  // 2️⃣ EXTRA FEES
  // ============================================================
  if ($action === 'get_fees') {
    $sql = "SELECT f.*, u.fullname AS tenant_name 
            FROM extra_fees f 
            LEFT JOIN rbe_users u ON f.tenant_id=u.id
            WHERE f.landlord_id=? AND f.active=1
            ORDER BY u.fullname ASC";
    $stmt = $conn_rbe->prepare($sql);
    $stmt->execute([$landlord_id]);
    res('success', '', $stmt->fetchAll(PDO::FETCH_ASSOC));
  }

  if ($action === 'save_fee') {
    $tenant_id = $_POST['tenant_id'] ?? null;
    $fee_type  = $_POST['fee_type'] ?? 'CUSTOM';
    $custom_label = $_POST['custom_label'] ?? null;
    $amount = (float)($_POST['amount'] ?? 0);
    if (!$amount) res('error','Missing amount');

    // Normalise fee_type to match extra_fees.fee_type ENUM in DB
    $raw = strtoupper(trim((string)$fee_type));
    switch ($raw) {
      case 'AIRCON':             $fee_type = 'aircon_fee'; break;
      case 'GENERATOR':          $fee_type = 'generator_fee'; break;
      case 'ELEC_AVAILABILITY':  $fee_type = 'elec_avail_fee'; break;
      case 'ELEC_BASIC':         $fee_type = 'elec_basic_fee'; break;
      case 'WATER_AVAILABILITY': $fee_type = 'water_avail_fee'; break;
      case 'WATER_BASIC':        $fee_type = 'water_basic_fee'; break;
      case 'CUSTOM':
      default:                   $fee_type = 'custom'; break;
    }

    $sql = "INSERT INTO extra_fees (tenant_id, landlord_id, fee_type, custom_label, amount, active)
            VALUES (?,?,?,?,?,1)
            ON DUPLICATE KEY UPDATE amount=VALUES(amount), custom_label=VALUES(custom_label), active=1";
    $stmt = $conn_rbe->prepare($sql);
    $stmt->execute([$tenant_id, $landlord_id, $fee_type, $custom_label, $amount]);
    res('success','Fee saved successfully');
  }

  if ($action === 'delete_fee') {
    $id = (int)($_POST['id'] ?? 0);
    $stmt = $conn_rbe->prepare("UPDATE extra_fees SET active=0 WHERE id=? AND landlord_id=?");
    $stmt->execute([$id, $landlord_id]);
    res('success','Fee deleted');
  }

  // ============================================================
  // 3️⃣ TIERS
  // ============================================================
  if ($action === 'get_tiers') {
    $type = $_GET['type'] ?? '';
    $cond = $type ? "AND t.type=?" : "";
    $sql = "SELECT t.*, u.fullname AS tenant_name 
            FROM billing_tiers t 
            LEFT JOIN rbe_users u ON u.id=t.tenant_id
            WHERE t.landlord_id=? $cond 
            ORDER BY t.type, u.fullname, t.tier_from";
    $stmt = $conn_rbe->prepare($sql);
    $params = $type ? [$landlord_id, $type] : [$landlord_id];
    $stmt->execute($params);
    res('success','',$stmt->fetchAll(PDO::FETCH_ASSOC));
  }

  if ($action === 'get_tier') {
    $id = (int)($_GET['id'] ?? 0);
    if ($id <= 0) res('error','Invalid ID');
    $stmt = $conn_rbe->prepare("SELECT * FROM billing_tiers WHERE id=? AND landlord_id=? LIMIT 1");
    $stmt->execute([$id, $landlord_id]);
    $tier = $stmt->fetch(PDO::FETCH_ASSOC);
    $tier = $tier ?: [];
    res('success','',$tier);
  }

  if ($action === 'save_tiers') {
    $tiers_json = $_POST['tiers'] ?? '[]';
    $tiers = json_decode($tiers_json, true);
    if (!$tiers || !is_array($tiers)) res('error','Invalid data');
    if (!count($tiers)) res('error','No tier data received');

    $ins = $conn_rbe->prepare("INSERT INTO billing_tiers (landlord_id, tenant_id, type, tier_from, tier_to, rate, block_name, valid_from, valid_to)
                                VALUES (?,?,?,?,?,?,?,?,?)");
    $upd = $conn_rbe->prepare("UPDATE billing_tiers
                                SET type=?, tenant_id=?, block_name=?, tier_from=?, tier_to=?, rate=?, valid_from=?, valid_to=?
                                WHERE id=? AND landlord_id=?");

    foreach ($tiers as $t) {
      $id = (int)($t['tier_id'] ?? 0);
      $tenant = (!empty($t['tenant_id'])) ? (int)$t['tenant_id'] : null;
      $type = $t['type'] ?? 'electricity';
      $from = is_numeric($t['tier_from'] ?? null) ? (float)$t['tier_from'] : 0;
      $to   = ($t['tier_to'] === '' || $t['tier_to'] === null) ? null : (float)$t['tier_to'];
      $rate = is_numeric($t['rate'] ?? null) ? (float)$t['rate'] : 0;
      $block= $t['block_name'] ?? null;
      $vfrom = $t['valid_from'] ?? date('Y-m-d');
      $vto   = $t['valid_to'] ?? null;

      if ($id > 0) {
        $upd->execute([$type, $tenant, $block, $from, $to, $rate, $vfrom, $vto, $id, $landlord_id]);
      } else {
        $ins->execute([$landlord_id, $tenant, $type, $from, $to, $rate, $block, $vfrom, $vto]);
      }
    }
    // return updated list after save
    $stmt = $conn_rbe->prepare("SELECT t.*, u.fullname AS tenant_name FROM billing_tiers t LEFT JOIN rbe_users u ON u.id=t.tenant_id WHERE t.landlord_id=? ORDER BY t.type, u.fullname, t.tier_from");
    $stmt->execute([$landlord_id]);
    res('success','Tier saved successfully',$stmt->fetchAll(PDO::FETCH_ASSOC));
  }

  if ($action === 'delete_tier') {
    $id = (int)($_POST['id'] ?? 0);
    $stmt = $conn_rbe->prepare("DELETE FROM billing_tiers WHERE id=? AND landlord_id=?");
    $stmt->execute([$id, $landlord_id]);
    res('success','Tier deleted');
  }

  // ============================================================
  // 4️⃣ FEE MODES
  // ============================================================
  if ($action === 'get_fee_modes') {
    $stmt = $conn_rbe->prepare("SELECT fee_type, mode FROM billing_fee_modes WHERE landlord_id=?");
    $stmt->execute([$landlord_id]);
    res('success','',$stmt->fetchAll(PDO::FETCH_ASSOC));
  }

  if ($action === 'save_fee_modes') {
    $modes = json_decode($_POST['modes'] ?? '{}', true);
    if (!$modes) res('error','Invalid data');

    // clear old
    $conn_rbe->prepare("DELETE FROM billing_fee_modes WHERE landlord_id=?")->execute([$landlord_id]);

    foreach ($modes as $type => $mode) {
      $stmt = $conn_rbe->prepare("
        INSERT INTO billing_fee_modes (landlord_id, fee_type, mode)
        VALUES (?,?,?)
        ON DUPLICATE KEY UPDATE mode=VALUES(mode)");
      $stmt->execute([$landlord_id, $type, $mode]);
    }
    res('success','Fee modes saved');
  }

  // Preview fee distribution for a given amount/mode (optional property scope)
  if ($action === 'preview_distribution') {
    $amount = isset($_GET['amount']) ? (float)$_GET['amount'] : (isset($_POST['amount']) ? (float)$_POST['amount'] : 0);
    $mode   = $_GET['mode'] ?? $_POST['mode'] ?? 'flat';
    $property_id = isset($_GET['property_id']) && $_GET['property_id'] !== '' ? (int)$_GET['property_id'] : (isset($_POST['property_id']) && $_POST['property_id'] !== '' ? (int)$_POST['property_id'] : null);
    if ($amount <= 0) res('error','Amount must be > 0');
    $alloc = distribute_fee($conn_rbe, (int)$landlord_id, $amount, (string)$mode, $property_id);
    if (!$alloc) res('success','No tenants in scope', []);
    // attach tenant names
    $ids = array_keys($alloc);
    $in = implode(',', array_fill(0, count($ids), '?'));
    $st = $conn_rbe->prepare("SELECT id, fullname FROM rbe_users WHERE id IN ($in)");
    $st->execute($ids);
    $names = [];
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) { $names[(int)$r['id']] = $r['fullname']; }
    $rows = [];
    foreach ($alloc as $tid => $amt) { $rows[] = ['tenant_id'=>(int)$tid,'tenant_name'=>($names[(int)$tid] ?? ''),'amount'=>round((float)$amt,2)]; }
    res('success','Preview generated', $rows);
  }

  // ============================================================
  // 5️⃣ BILLING SETTINGS (with auto-version history)
  // ============================================================
  if ($action === 'get_settings') {
    $tenant_id = $_GET['tenant_id'] ?? null;
    $cond = $tenant_id ? "AND tenant_id=?" : "AND tenant_id IS NULL";
    $stmt = $conn_rbe->prepare("SELECT * FROM billing_settings WHERE landlord_id=? $cond ORDER BY id DESC LIMIT 1");
    $params = $tenant_id ? [$landlord_id, $tenant_id] : [$landlord_id];
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$row) res('success','', new stdClass());
    res('success','', $row);
  }

  if ($action === 'save_settings') {
    $tenant_id = $_POST['tenant_id'] ?? null;

    // fetch latest existing to archive
    $cond = $tenant_id ? "tenant_id=?" : "tenant_id IS NULL";
    $stmt = $conn_rbe->prepare("SELECT * FROM billing_settings WHERE landlord_id=? AND $cond ORDER BY id DESC LIMIT 1");
    $stmt->execute($tenant_id ? [$landlord_id, $tenant_id] : [$landlord_id]);
    $old = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($old) {
      $fields = array_keys($old);
      $cols = implode(',', $fields);
      $vals = implode(',', array_fill(0, count($fields), '?'));
      $ins = $conn_rbe->prepare("INSERT INTO billing_settings_history ($cols) VALUES ($vals)");
      $ins->execute(array_values($old));
    }

    $cols = ['landlord_id','tenant_id','buy_rate','sell_rate','kva_cost','kva_sell','water_rate','sewage_fee',
             'refuse_removal_fee','rates_and_taxes','other_fee_name','other_fee','other_fee_name_2','other_fee_2',
             'other_fee_name_3','other_fee_3','other_fee_name_4','other_fee_4','other_fee_name_5','other_fee_5',
             'other_fees','display_mode','effective_from','effective_to','updated_at'];
    $placeholders = implode(',', array_fill(0, count($cols), '?'));
    $sql = "INSERT INTO billing_settings (".implode(',', $cols).") VALUES ($placeholders)";
    $stmt = $conn_rbe->prepare($sql);

    // Map POST fields to schema (support legacy _1 keys)
    $of_name = $_POST['other_fee_name'] ?? ($_POST['other_fee_name_1'] ?? '');
    $of_amt  = $_POST['other_fee'] ?? ($_POST['other_fee_1'] ?? 0);

    $stmt->execute([
      $landlord_id,
      $tenant_id ?: null,
      $_POST['buy_rate'] ?? 0,
      $_POST['sell_rate'] ?? 0,
      $_POST['kva_cost'] ?? 0,
      $_POST['kva_sell'] ?? 0,
      $_POST['water_rate'] ?? 0,
      $_POST['sewage_fee'] ?? 0,
      $_POST['refuse_removal_fee'] ?? 0,
      $_POST['rates_and_taxes'] ?? 0,
      $of_name,
      $of_amt,
      $_POST['other_fee_name_2'] ?? '',
      $_POST['other_fee_2'] ?? 0,
      $_POST['other_fee_name_3'] ?? '',
      $_POST['other_fee_3'] ?? 0,
      $_POST['other_fee_name_4'] ?? '',
      $_POST['other_fee_4'] ?? 0,
      $_POST['other_fee_name_5'] ?? '',
      $_POST['other_fee_5'] ?? 0,
      $_POST['other_fees'] ?? null,
      $_POST['display_mode'] ?? 'accumulated',
      date('Y-m-d'),
      null,
      date('Y-m-d H:i:s')
    ]);
    // return updated settings row
    $cond2 = $tenant_id ? "AND tenant_id=?" : "AND tenant_id IS NULL";
    $st2 = $conn_rbe->prepare("SELECT * FROM billing_settings WHERE landlord_id=? $cond2 ORDER BY id DESC LIMIT 1");
    $st2->execute($tenant_id ? [$landlord_id, $tenant_id] : [$landlord_id]);
    $row = $st2->fetch(PDO::FETCH_ASSOC);
    res('success','Billing settings saved & versioned', $row ?: new stdClass());
  }

  // ============================================================
  // 6️⃣ HISTORY
  // ============================================================
  if ($action === 'get_history') {
    $stmt = $conn_rbe->prepare("
      SELECT h.*, u.fullname AS tenant_name
      FROM billing_settings_history h
      LEFT JOIN rbe_users u ON h.tenant_id=u.id
      WHERE h.landlord_id=?
      ORDER BY h.archived_at DESC
      LIMIT 50");
    $stmt->execute([$landlord_id]);
    res('success','',$stmt->fetchAll(PDO::FETCH_ASSOC));
  }

  // ============================================================
  // DEFAULT
  // ============================================================
  res('error','Invalid action');

} catch (Throwable $e) {
  res('error', 'Server exception: '.$e->getMessage());
}
?>
