<?php
// create_tables.php
// Run once to create sd_leads and sd_lead_updates tables in 'servicedirect' DB.
// After successful run, remove this file from the server.

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

$DB_HOST = 'localhost';
$DB_PORT = 3306;
$DB_NAME = 'servicedirect';
$DB_USER = 'servicedirect';
$DB_PASS = 'Password@#12390';

try {
    $dsn = "mysql:host={$DB_HOST};port={$DB_PORT};dbname={$DB_NAME};charset=utf8mb4";
    $pdo = new PDO($dsn, $DB_USER, $DB_PASS, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    http_response_code(500);
    echo "DB connection failed: " . htmlspecialchars($e->getMessage());
    exit;
}

// SQL statements (idempotent)
$sql = <<<SQL
CREATE TABLE IF NOT EXISTS sd_leads (
  lead_id BIGINT PRIMARY KEY,
  lead_creation_timestamp DATETIME NULL,
  lead_caller_phone_number VARCHAR(50),
  caller_id_name VARCHAR(200),
  integration_type VARCHAR(20),
  service_category VARCHAR(100),
  zip_code VARCHAR(20),
  payout DECIMAL(10,2),
  affiliate_payout_outcome VARCHAR(100),
  service_needed INT NULL,
  raw_payload JSON,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sd_lead_updates (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  lead_id BIGINT,
  tracking_id VARCHAR(255) NULL,
  property VARCHAR(100),
  value JSON,
  ts INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (lead_id),
  INDEX (property)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SQL;

try {
    // Execute multi-statement
    $pdo->exec($sql);
    echo "Success: Tables sd_leads and sd_lead_updates have been created (or already exist).";
} catch (PDOException $e) {
    http_response_code(500);
    echo "Error creating tables: " . htmlspecialchars($e->getMessage());
    exit;
}
