entrypoint.sh

#!/bin/sh
set -e

DATADIR=/data/mysql

if [ ! -d "$DATADIR/mysql" ]; then
    echo "Initializing MariaDB data directory..."
    mysql_install_db --user=appuser --datadir="$DATADIR" --skip-test-db
fi

echo "Starting MariaDB..."
mysqld --user=appuser --datadir="$DATADIR" --socket=/run/mysqld/mysqld.sock --port=3306 --skip-networking=0 --bind-address=127.0.0.1 &

echo "Waiting for MariaDB to start..."
for i in $(seq 1 30); do
    if mysqladmin --socket=/run/mysqld/mysqld.sock ping 2>/dev/null | grep -q "alive"; then
        echo "MariaDB is ready."
        break
    fi
    if [ "$i" = "30" ]; then
        echo "ERROR: MariaDB failed to start within 30 seconds."
        exit 1
    fi
    sleep 1
done

mysql --socket=/run/mysqld/mysqld.sock -e "
    CREATE DATABASE IF NOT EXISTS sms_gateway;
    CREATE USER IF NOT EXISTS 'sms'@'127.0.0.1' IDENTIFIED BY 'sms_password';
    GRANT ALL PRIVILEGES ON sms_gateway.* TO 'sms'@'127.0.0.1';
    FLUSH PRIVILEGES;
"

echo "Creating tables if needed..."
mysql --socket=/run/mysqld/mysqld.sock sms_gateway <<'EOSQL'
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS goose_db_version (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id BIGINT NOT NULL,
    is_applied TINYINT(1) NOT NULL,
    tstamp TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(32) NOT NULL,
    password_hash VARCHAR(72) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    deleted_at DATETIME(3) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS devices (
    id CHAR(21) NOT NULL,
    name VARCHAR(128) DEFAULT NULL,
    auth_token CHAR(21) NOT NULL,
    push_token VARCHAR(256) DEFAULT NULL,
    user_id VARCHAR(32) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    deleted_at DATETIME(3) DEFAULT NULL,
    last_seen DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY idx_devices_auth_token (auth_token),
    KEY fk_users_devices (user_id),
    KEY idx_devices_last_seen (last_seen),
    CONSTRAINT fk_users_devices FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS device_settings (
    user_id VARCHAR(32) NOT NULL,
    settings JSON NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (user_id),
    CONSTRAINT fk_device_settings_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id CHAR(21) NOT NULL,
    ext_id VARCHAR(36) NOT NULL,
    state ENUM('Pending','Processed','Sent','Delivered','Failed') NOT NULL DEFAULT 'Pending',
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    deleted_at DATETIME(3) DEFAULT NULL,
    valid_until DATETIME(3) DEFAULT NULL,
    sim_number TINYINT UNSIGNED DEFAULT NULL,
    with_delivery_report TINYINT UNSIGNED NOT NULL DEFAULT 1,
    is_hashed TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_encrypted TINYINT UNSIGNED NOT NULL DEFAULT 0,
    priority TINYINT NOT NULL DEFAULT 0,
    type ENUM('Text','Data') NOT NULL DEFAULT 'Text',
    content TEXT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY unq_messages_id_device (ext_id, device_id),
    KEY idx_messages_device_state (device_id, state),
    KEY idx_messages_is_hashed (is_hashed),
    CONSTRAINT fk_messages_device FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS message_recipients (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    message_id BIGINT UNSIGNED NOT NULL,
    phone_number VARCHAR(128) NOT NULL,
    state ENUM('Pending','Processed','Sent','Delivered','Failed') NOT NULL DEFAULT 'Pending',
    error VARCHAR(256) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY unq_message_recipients_message_id_phone_number (message_id, phone_number),
    UNIQUE KEY id (id),
    CONSTRAINT fk_messages_recipients FOREIGN KEY (message_id) REFERENCES messages (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS message_states (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    message_id BIGINT UNSIGNED NOT NULL,
    state ENUM('Pending','Sent','Processed','Delivered','Failed') NOT NULL,
    updated_at DATETIME(3) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY unq_message_states_message_id_state (message_id, state),
    CONSTRAINT fk_messages_states FOREIGN KEY (message_id) REFERENCES messages (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS tokens (
    id CHAR(21) NOT NULL,
    user_id CHAR(21) NOT NULL,
    expires_at DATETIME(3) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    revoked_at DATETIME(3) DEFAULT NULL,
    token_use ENUM('access','refresh') NOT NULL DEFAULT 'access',
    parent_jti CHAR(21) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_tokens_user_id (user_id),
    KEY idx_tokens_expires_at (expires_at),
    KEY idx_tokens_parent_jti (parent_jti),
    CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS webhooks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ext_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(32) NOT NULL,
    url VARCHAR(256) NOT NULL,
    event VARCHAR(32) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    deleted_at DATETIME(3) DEFAULT NULL,
    device_id CHAR(21) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY unq_webhooks_user_extid (user_id, ext_id),
    KEY idx_webhooks_device (device_id),
    CONSTRAINT fk_webhooks_device FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE,
    CONSTRAINT fk_webhooks_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO goose_db_version (version_id, is_applied) VALUES
(0, 1), (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1),
(8, 1), (9, 1), (10, 1), (11, 1), (12, 1), (13, 1), (14, 1),
(15, 1), (16, 1), (17, 1), (18, 1), (19, 1), (20, 1), (21, 1),
(22, 1), (23, 1), (24, 1), (25, 1);

SET FOREIGN_KEY_CHECKS = 1;
EOSQL

echo "Database and tables ready."

sed -i "s|\${GATEWAY__PRIVATE_TOKEN}|${GATEWAY__PRIVATE_TOKEN}|g" /app/config.yml

echo "Starting SMS Gateway..."
exec /usr/bin/supervisord -c /etc/supervisor/conf.d/supervisord.conf