84 lines
3.1 KiB
PHP
84 lines
3.1 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
require_once __DIR__ . '/../db/config.php';
|
|
|
|
function e(string $value): string {
|
|
return htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
|
|
}
|
|
|
|
function check_admin_auth(): bool {
|
|
session_start();
|
|
return isset($_SESSION['user_type']) && $_SESSION['user_type'] === 'admin';
|
|
}
|
|
|
|
function ensure_tables(): void {
|
|
$pdo = db();
|
|
|
|
$pdo->exec("
|
|
CREATE TABLE IF NOT EXISTS twilio_numbers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
label VARCHAR(120) NOT NULL,
|
|
account_sid VARCHAR(64) NOT NULL,
|
|
auth_token VARCHAR(128) NOT NULL,
|
|
api_key VARCHAR(128) DEFAULT NULL,
|
|
from_number VARCHAR(32) NOT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$pdo->exec("
|
|
CREATE TABLE IF NOT EXISTS contacts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(120) DEFAULT NULL,
|
|
phone VARCHAR(32) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_phone (phone)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$pdo->exec("
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
contact_id INT NOT NULL,
|
|
twilio_number_id INT NOT NULL,
|
|
last_message_at TIMESTAMP NULL DEFAULT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_contact (contact_id),
|
|
INDEX idx_twilio (twilio_number_id),
|
|
CONSTRAINT fk_convo_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_convo_twilio FOREIGN KEY (twilio_number_id) REFERENCES twilio_numbers(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$pdo->exec("
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
conversation_id INT NOT NULL,
|
|
direction ENUM('inbound','outbound') NOT NULL,
|
|
body TEXT NOT NULL,
|
|
status VARCHAR(32) DEFAULT 'stored',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_convo (conversation_id),
|
|
CONSTRAINT fk_msg_convo FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
|
|
$pdo->exec("
|
|
CREATE TABLE IF NOT EXISTS billing_snapshots (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
twilio_number_id INT NOT NULL,
|
|
period_start DATE NOT NULL,
|
|
period_end DATE NOT NULL,
|
|
usage_count INT DEFAULT 0,
|
|
cost DECIMAL(10,4) DEFAULT 0,
|
|
currency VARCHAR(12) DEFAULT 'USD',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_bill_twilio (twilio_number_id),
|
|
CONSTRAINT fk_bill_twilio FOREIGN KEY (twilio_number_id) REFERENCES twilio_numbers(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
");
|
|
}
|
|
|
|
ensure_tables(); |