Sat 08 Nov 2025 13:29:54 EET




# deploy_property_dashboard_sqlite_shared_hosting.sh
# Complete automated deployment for shared hosting (cPanel/Apache + PHP 8.1+, SQLite3)
# Assumes: SSH access, PHP with PDO_SQLITE, Composer, writable /public_html or subdomain
# Single-file backend (index.php), single-file frontend (index.html with embedded JS/CSS), SQLite DB auto-created
# No Docker, no Node.js, no external services; pure PHP + SQLite + vanilla JS + Chart.js CDN

#!/usr/bin/env bash
set -euo pipefail

# === Configuration (edit once) ===
DOMAIN="yourdomain.com"                  # or subdomain e.g. dashboard.yourdomain.com
INSTALL_DIR="/home/username/public_html/dashboard"  # adjust to your shared hosting path
DB_FILE="$INSTALL_DIR/property_dashboard.db"
ADMIN_USER="admin"
ADMIN_PASS="password123"                 # change in production
JWT_SECRET="SuperSecretJWTKey2025SharedHosting"

# === Create Directory Structure ===
mkdir -p "$INSTALL_DIR"
cd "$INSTALL_DIR"

# === SQLite Database with Schema and Seed Data ===
cat > init_db.php <<'EOF'
<?php
// init_db.php - Auto-create SQLite DB, tables, indexes, seed data
$db = new PDO("sqlite:$argv[1]");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("
CREATE TABLE IF NOT EXISTS properties (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    address TEXT NOT NULL UNIQUE,
    units INTEGER NOT NULL CHECK(units > 0),
    occupancy INTEGER DEFAULT 0 CHECK(occupancy >= 0),
    monthly_rent REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS tenants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER REFERENCES properties(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    lease_start DATE NOT NULL,
    lease_end DATE NOT NULL,
    rent_due REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tenant_id INTEGER REFERENCES tenants(id) ON DELETE CASCADE,
    amount REAL NOT NULL,
    paid_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT CHECK(status IN ('paid','pending','overdue')) DEFAULT 'pending'
);

CREATE TABLE IF NOT EXISTS workorders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER REFERENCES properties(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT CHECK(status IN ('new','assigned','in_progress','completed')) DEFAULT 'new',
    assigned_to TEXT,
    cost REAL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX IF NOT EXISTS idx_workorders_status ON workorders(status);
");

$db->exec("
INSERT OR IGNORE INTO properties (address, units, occupancy, monthly_rent) VALUES
('123 Elm St', 12, 10, 1500.00),
('456 Oak Ave', 8, 8, 1800.00),
('789 Pine Rd', 20, 15, 1300.00);

INSERT OR IGNORE INTO tenants (property_id, name, email, lease_start, lease_end, rent_due) VALUES
(1, 'Alice Johnson', 'alice@example.com', '2025-01-01', '2026-01-01', 1500.00),
(1, 'Bob Smith', 'bob@example.com', '2025-02-01', '2026-02-01', 1500.00),
(2, 'Carol White', 'carol@example.com', '2024-12-01', '2025-12-01', 1800.00);
");

echo "SQLite database initialized at $argv[1]";
?>
EOF

# === Backend: Single File PHP API (index.php) ===
cat > index.php <<'EOF'
<?php
// index.php - Full REST API backend with JWT auth, CRUD, summary, real-time via polling fallback
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PATCH, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Authorization, Content-Type');
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') exit;

require_once 'jwt.php'; // embedded below
$db = new PDO("sqlite:property_dashboard.db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$secret = 'SuperSecretJWTKey2025SharedHosting';
$method = $_SERVER['REQUEST_METHOD'];
$path = explode('/', trim(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH), '/'));
array_shift($path); // remove 'api' if routed

function send($data, $code = 200) { http_response_code($code); echo json_encode($data); exit; }
function auth() {
    global $secret;
    $headers = getallheaders();
    $token = $headers['Authorization'] ?? '';
    $token = preg_replace('/^Bearer\s+/i', '', $token);
    if (!$token) return send(['error' => 'No token'], 401);
    try {
        return (array) JWT::decode($token, $secret);
    } catch (Exception $e) {
        return send(['error' => 'Invalid token'], 401);
    }
}

// === Embedded JWT Library (pure PHP, no Composer) ===
if (!function_exists('base64url_encode')) {
    function base64url_encode($data) { return rtrim(strtr(base64_encode($data), '+/', '-_'), '='); }
    function base64url_decode($data) { return base64_decode(str_pad(strtr($data, '-_', '+/'), strlen($data) % 4, '=', STR_PAD_RIGHT)); }
}
class JWT {
    public static function encode($payload, $key) {
        $header = base64url_encode(json_encode(['typ' => 'JWT', 'alg' => 'HS256']));
        $payload = base64url_encode(json_encode($payload));
        $signature = hash_hmac('sha256', "$header.$payload", $key, true);
        $signature = base64url_encode($signature);
        return "$header.$payload.$signature";
    }
    public static function decode($jwt, $key) {
        $parts = explode('.', $jwt);
        if (count($parts) !== 3) throw new Exception('Invalid JWT');
        $signature = base64url_decode($parts[2]);
        $expected = hash_hmac('sha256', "$parts[0].$parts[1]", $key, true);
        if (!hash_equals($expected, $signature)) throw new Exception('Invalid signature');
        return json_decode(base64url_decode($parts[1]));
    }
}

// === Routes ===
if ($path[0] === 'login' && $method === 'POST') {
    $input = json_decode(file_get_contents('php://input'), true);
    if ($input['username'] === 'admin' && $input['password'] === 'password123') {
        $token = JWT::encode(['username' => 'admin', 'exp' => time() + 28800], $secret);
        send(['token' => $token]);
    }
    send(['error' => 'Invalid credentials'], 401);
}

$user = auth();

// Dashboard Summary
if ($method === 'GET' && empty($path[0])) {
    $stmt = $db->query("SELECT COUNT(*) as total, SUM(units) as total_units, SUM(occupancy) as occupied FROM properties");
    $prop = $stmt->fetch(PDO::FETCH_ASSOC);
    $stmt = $db->query("SELECT SUM(amount) as revenue FROM payments WHERE status = 'paid' AND paid_at >= date('now', '-1 month')");
    $rev = $stmt->fetch(PDO::FETCH_ASSOC);
    $stmt = $db->query("SELECT COUNT(*) as pending FROM workorders WHERE status != 'completed'");
    $wo = $stmt->fetch(PDO::FETCH_ASSOC);
    send([
        'totalProperties' => (int)$prop['total'],
        'occupancyRate' => $prop['total_units'] ? round(($prop['occupied'] / $prop['total_units']) * 100, 1) : 0,
        'monthlyRevenue' => round($rev['revenue'] ?? 0, 2),
        'pendingWorkorders' => (int)$wo['pending']
    ]);
}

// Properties CRUD
if ($path[0] === 'properties') {
    if ($method === 'GET') {
        $stmt = $db->query("SELECT * FROM properties");
        send($stmt->fetchAll(PDO::FETCH_ASSOC));
    }
    if ($method === 'POST') {
        $data = json_decode(file_get_contents('php://input'), true);
        $stmt = $db->prepare("INSERT INTO properties (address, units, occupancy, monthly_rent) VALUES (?, ?, ?, ?)");
        $stmt->execute([$data['address'], $data['units'], $data['occupancy'] ?? 0, $data['monthly_rent']]);
        send(['id' => $db->lastInsertId()]);
    }
}

// Tenants CRUD
if ($path[0] === 'tenants') {
    if ($method === 'GET') {
        $stmt = $db->query("SELECT t.*, p.address FROM tenants t JOIN properties p ON t.property_id = p.id");
        send($stmt->fetchAll(PDO::FETCH_ASSOC));
    }
    if ($method === 'POST') {
        $data = json_decode(file_get_contents('php://input'), true);
        $stmt = $db->prepare("INSERT INTO tenants (property_id, name, email, phone, lease_start, lease_end, rent_due) VALUES (?, ?, ?, ?, ?, ?, ?)");
        $stmt->execute([$data['property_id'], $data['name'], $data['email'], $data['phone'], $data['lease_start'], $data['lease_end'], $data['rent_due']]);
        $db->prepare("UPDATE properties SET occupancy = occupancy + 1 WHERE id = ?")->execute([$data['property_id']]);
        send(['id' => $db->lastInsertId()]);
    }
}

// Payments
if ($path[0] === 'payments' && $method === 'POST') {
    $data = json_decode(file_get_contents('php://input'), true);
    $stmt = $db->prepare("INSERT INTO payments (tenant_id, amount, status) VALUES (?, ?, 'paid')");
    $stmt->execute([$data['tenant_id'], $data['amount']]);
    send(['id' => $db->lastInsertId()]);
}

// Workorders CRUD
if ($path[0] === 'workorders') {
    if ($method === 'GET') {
        $stmt = $db->query("SELECT w.*, p.address FROM workorders w JOIN properties p ON w.property_id = p.id ORDER BY created_at DESC");
        send($stmt->fetchAll(PDO::FETCH_ASSOC));
    }
    if ($method === 'POST') {
        $data = json_decode(file_get_contents('php://input'), true);
        $stmt = $db->prepare("INSERT INTO workorders (property_id, title, description, status, assigned_to, cost) VALUES (?, ?, ?, ?, ?, ?)");
        $stmt->execute([$data['property_id'], $data['title'], $data['description'], $data['status'] ?? 'new', $data['assigned_to'], $data['cost'] ?? 0]);
        send(['id' => $db->lastInsertId()]);
    }
    if ($method === 'PATCH' && isset($path[1])) {
        $data = json_decode(file_get_contents('php://input'), true);
        $id = (int)$path[1];
        $fields = [];
        $values = [];
        foreach (['status', 'assigned_to', 'cost'] as $f) {
            if (isset($data[$f])) { $fields[] = "$f = ?"; $values[] = $data[$f]; }
        }
        if (empty($fields)) send(['error' => 'No fields'], 400);
        $values[] = $id;
        $sql = "UPDATE workorders SET " . implode(', ', $fields) . " WHERE id = ?";
        $db->prepare($sql)->execute($values);
        $stmt = $db->prepare("SELECT * FROM workorders WHERE id = ?");
        $stmt->execute([$id]);
        send($stmt->fetch(PDO::FETCH_ASSOC));
    }
}

send(['error' => 'Not found'], 404);
?>
EOF

# === Frontend: Single HTML File with Embedded JS/CSS + Chart.js CDN ===
cat > index.html <<'EOF'
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
  <title>Property Management Dashboard</title>
  <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@400;500;700&display=swap" rel="stylesheet">
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
  <style>
    :root { --primary: #1976d2; --success: #4caf50; --warning: #ff9800; --danger: #f44336; }
    body { margin:0; font-family: 'Roboto', sans-serif; background: #f5f5f5; }
    .container { max-width: 1200px; margin: auto; padding: 20px; }
    .card { background: white; border-radius: 8px; box-shadow: 0 2px 8px rgba(0,0,0,0.1); padding: 20px; margin-bottom: 20px; }
    .grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); gap: 20px; }
    .kpi { text-align: center; }
    .kpi h3 { margin: 0; font-size: 2rem; color: var(--primary); }
    .kpi p { margin: 5px 0 0; color: #666; }
    table { width: 100%; border-collapse: collapse; margin-top: 10px; }
    th, td { padding: 12px; text-align: left; border-bottom: 1px solid #ddd; }
    th { background: #f9f9f9; }
    button { background: var(--primary); color: white; border: none; padding: 8px 16px; border-radius: 4px; cursor: pointer; }
    button:hover { opacity: 0.9; }
    .modal { display: none; position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(0,0,0,0.5); justify-content: center; align-items: center; }
    .modal-content { background: white; padding: 20px; border-radius: 8px; width: 400px; }
    input, select, textarea { width: 100%; padding: 8px; margin: 8px 0; border: 1px solid #ddd; border-radius: 4px; }
    .hidden { display: none; }
  </style>
</head>
<body>
  <div id="login" class="container">
    <div class="card">
      <h2>Property Management Dashboard</h2>
      <p><button onclick="login()">Login as Admin (admin / password123)</button></p>
    </div>
  </div>

  <div id="app" class="container hidden">
    <h1>Dashboard Overview</h1>
    <div class="grid">
      <div class="card kpi"><h3 id="totalProps">0</h3><p>Total Properties</p></div>
      <div class="card kpi"><h3 id="occupancy">0%</h3><p>Occupancy Rate</p></div>
      <div class="card kpi"><h3 id="revenue">$0</h3><p>Monthly Revenue</p></div>
      <div class="card kpi"><h3 id="pendingWO">0</h3><p>Pending Work Orders</p></div>
    </div>

    <div class="card">
      <h2>Revenue Trend</h2>
      <canvas id="revenueChart" height="100"></canvas>
    </div>

    <div class="card">
      <h2>Work Orders <button onclick="openModal()">New</button></h2>
      <table id="woTable"><thead><tr><th>ID</th><th>Title</th><th>Property</th><th>Status</th><th>Cost</th><th>Action</th></tr></thead><tbody></tbody></table>
    </div>
  </div>

  <div id="modal" class="modal">
    <div class="modal-content">
      <h3>New Work Order</h3>
      <input type="text" id="woTitle" placeholder="Title" />
      <textarea id="woDesc" placeholder="Description"></textarea>
      <input type="number" id="woPropId" placeholder="Property ID" />
      <div style="margin-top:10px;">
        <button onclick="closeModal()">Cancel</button>
        <button onclick="createWO()">Create</button>
      </div>
    </div>
  </div>

  <script>
    const API = '/index.php/';
    let token = localStorage.getItem('token');
    let summary = {}, workorders = [];

    function login() {
      fetch(API + 'login', {
        method: 'POST',
        headers: {'Content-Type': 'application/json'},
        body: JSON.stringify({username: 'admin', password: 'password123'})
      })
      .then(r => r.json())
      .then(d => {
        token = d.token;
        localStorage.setItem('token', token);
        document.getElementById('login').classList.add('hidden');
        document.getElementById('app').classList.remove('hidden');
        loadData();
        setInterval(pollUpdates, 10000); // polling fallback
      });
    }

    function api(path, method = 'GET', body = null) {
      return fetch(API + path, {
        method,
        headers: {
          'Content-Type': 'application/json',
          'Authorization': 'Bearer ' + token
        },
        body: body ? JSON.stringify(body) : null
      }).then(r => r.json());
    }

    function loadData() {
      Promise.all([
        api(''),
        api('workorders')
      ]).then(([sum, wo]) => {
        summary = sum;
        workorders = wo;
        updateUI();
      });
    }

    function pollUpdates() {
      api('workorders').then(wo => {
        if (JSON.stringify(wo) !== JSON.stringify(workorders)) {
          workorders = wo;
          renderWorkorders();
        }
      });
    }

    function updateUI() {
      document.getElementById('totalProps').textContent = summary.totalProperties;
      document.getElementById('occupancy').textContent = summary.occupancyRate + '%';
      document.getElementById('revenue').textContent = '$' + summary.monthlyRevenue;
      document.getElementById('pendingWO').textContent = summary.pendingWorkorders;
      renderRevenueChart();
      renderWorkorders();
    }

    function renderRevenueChart() {
      const ctx = document.getElementById('revenueChart').getContext('2d');
      new Chart(ctx, {
        type: 'line',
        data: {
          labels: ['Jan', 'Feb', 'Mar', 'Apr'],
          datasets: [{
            label: 'Revenue',
            data: [45000, 48000, 52000, 50000],
            borderColor: '#1976d2',
            tension: 0.1
          }]
        },
        options: { responsive: true, plugins: { legend: { position: 'top' } } }
      });
    }

    function renderWorkorders() {
      const tbody = document.querySelector('#woTable tbody');
      tbody.innerHTML = '';
      workorders.forEach(wo => {
        const tr = document.createElement('tr');
        tr.innerHTML = `<td>${wo.id}</td><td>${wo.title}</td><td>${wo.address}</td>
          <td><select onchange="updateStatus(${wo.id}, this.value)">
            <option ${wo.status==='new'?'selected':''}>new</option>
            <option ${wo.status==='assigned'?'selected':''}>assigned</option>
            <option ${wo.status==='in_progress'?'selected':''}>in_progress</option>
            <option ${wo.status==='completed'?'selected':''}>completed</option>
          </select></td>
          <td>$${wo.cost}</td>
          <td><button onclick="updateStatus(${wo.id}, 'completed')">Complete</button></td>`;
        tbody.appendChild(tr);
      });
    }

    function updateStatus(id, status) {
      api(`workorders/${id}`, 'PATCH', {status}).then(() => {
        loadData();
      });
    }

    function openModal() { document.getElementById('modal').style.display = 'flex'; }
    function closeModal() { document.getElementById('modal').style.display = 'none'; }
    function createWO() {
      const data = {
        title: document.getElementById('woTitle').value,
        description: document.getElementById('woDesc').value,
        property_id: parseInt(document.getElementById('woPropId').value)
      };
      api('workorders', 'POST', data).then(() => {
        closeModal();
        loadData();
      });
    }

    // Auto-login if token exists
    if (token) {
      document.getElementById('login').classList.add('hidden');
      document.getElementById('app').classList.remove('hidden');
      loadData();
      setInterval(pollUpdates, 10000);
    }
  </script>
</body>
</html>
EOF

# === Initialize Database ===
php init_db.php "$DB_FILE"

# === .htaccess for Clean URLs and Security ===
cat > .htaccess <<'EOF'
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^api/(.*)$ index.php/$1 [L]

# Security
<Files "property_dashboard.db">
    Order allow,deny
    Deny from all
</Files>
<Files "init_db.php">
    Order allow,deny
    Deny from all
</Files>

# PHP Settings
php_value upload_max_filesize 10M
php_value post_max_size 10M
EOF

# === Final Permissions ===
chmod 600 "$DB_FILE"
chmod 644 index.php index.html .htaccess
chmod 700 init_db.php

echo "Deployment complete."
echo "Access: https://$DOMAIN/dashboard/"
echo "Login: admin / password123"
echo "Database: $DB_FILE (auto-created with schema and seed data)"
echo "All functionality (CRUD, summary, charts, work orders, auth) in two public files + one DB."
echo "No Composer, no Node, no external deps. Works on any PHP 8.1+ shared host with SQLite."