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

# --- Config ---
APP_DIR="/var/www/html/alliance_sms"
WEB_ROOT="/var/www/html"
BUILD_ROOT="/tmp/alliance_sms_release_$(date +%Y%m%d_%H%M%S)"
OUT_ZIP="$BUILD_ROOT/../alliance_sms_package_$(date +%Y%m%d_%H%M%S).zip"

DB_NAME="mms"
DB_USER="root"
DB_PASS="cachicamo"

VHOST_FILE="apache/alliance_sms.conf"

# --- Prep build tree ---
mkdir -p "$BUILD_ROOT"/{app,apache,sql,scripts}
echo "[*] Build dir: $BUILD_ROOT"

# --- Copy application (exclude volatile) ---
rsync -a --delete \
  --exclude 'logs/*' \
  --exclude '*.log' \
  --exclude '.git' \
  --exclude '.DS_Store' \
  "$APP_DIR"/ "$BUILD_ROOT/app/"

# --- Generate Apache vhost ---
cat > "$BUILD_ROOT/$VHOST_FILE" <<'EOF'
# /etc/apache2/sites-available/alliance_sms.conf

# Redirect all HTTP to HTTPS
<VirtualHost *:80>
  ServerName alliance.csc20.com
  ServerAlias sms.csc20.com mms.csc20.com
  RewriteEngine On
  RewriteCond %{HTTPS} off
  RewriteRule ^ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]
</VirtualHost>

# alliance.csc20.com — app UI/API under /var/www/html/alliance_sms
<VirtualHost *:443>
  ServerName alliance.csc20.com

  DocumentRoot /var/www/html/alliance_sms
  DirectoryIndex index.php

  <Directory /var/www/html/alliance_sms>
    AllowOverride All
    Options -Indexes +FollowSymLinks
    Require all granted
    <FilesMatch \.php$>
      SetHandler "proxy:unix:/run/php/php-fpm.sock|fcgi://localhost/"
    </FilesMatch>
  </Directory>

  ErrorLog ${APACHE_LOG_DIR}/alliance_error.log
  CustomLog ${APACHE_LOG_DIR}/alliance_access.log combined

  SSLEngine On
  SSLCertificateFile /etc/letsencrypt/live/alliance.csc20.com/fullchain.pem
  SSLCertificateKeyFile /etc/letsencrypt/live/alliance.csc20.com/privkey.pem

  # Security and perf
  Header always set X-Content-Type-Options "nosniff"
  Header always set X-Frame-Options "SAMEORIGIN"
  Header always set Referrer-Policy "strict-origin-when-cross-origin"
</VirtualHost>

# sms.csc20.com — webhooks (receive_sms.php, receive_mms.php)
<VirtualHost *:443>
  ServerName sms.csc20.com

  DocumentRoot /var/www/html/alliance_sms
  DirectoryIndex index.php

  <Directory /var/www/html/alliance_sms>
    AllowOverride All
    Options -Indexes +FollowSymLinks
    Require all granted
    <FilesMatch \.php$>
      SetHandler "proxy:unix:/run/php/php-fpm.sock|fcgi://localhost/"
    </FilesMatch>
  </Directory>

  ErrorLog ${APACHE_LOG_DIR}/sms_error.log
  CustomLog ${APACHE_LOG_DIR}/sms_access.log combined

  SSLEngine On
  SSLCertificateFile /etc/letsencrypt/live/sms.csc20.com/fullchain.pem
  SSLCertificateKeyFile /etc/letsencrypt/live/sms.csc20.com/privkey.pem

  Header always set X-Content-Type-Options "nosniff"
  Header always set X-Frame-Options "SAMEORIGIN"
  Header always set Referrer-Policy "strict-origin-when-cross-origin"
</VirtualHost>

# mms.csc20.com — serves processed assets from /var/www/html
<VirtualHost *:443>
  ServerName mms.csc20.com

  DocumentRoot /var/www/html
  DirectoryIndex index.php index.html

  <Directory /var/www/html>
    AllowOverride None
    Options -Indexes +FollowSymLinks
    Require all granted
    <FilesMatch \.php$>
      SetHandler "proxy:unix:/run/php/php-fpm.sock|fcgi://localhost/"
    </FilesMatch>
  </Directory>

  ErrorLog ${APACHE_LOG_DIR}/mms_error.log
  CustomLog ${APACHE_LOG_DIR}/mms_access.log combined

  SSLEngine On
  SSLCertificateFile /etc/letsencrypt/live/mms.csc20.com/fullchain.pem
  SSLCertificateKeyFile /etc/letsencrypt/live/mms.csc20.com/privkey.pem

  Header always set X-Content-Type-Options "nosniff"
  Header always set X-Frame-Options "SAMEORIGIN"
  Header always set Referrer-Policy "strict-origin-when-cross-origin"
</VirtualHost>
EOF

# --- Generate DB schema (DDL only) ---
cat > "$BUILD_ROOT/sql/schema.sql" <<'EOF'
-- Create database & root password (optional; adjust for your env)
CREATE DATABASE IF NOT EXISTS `mms` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `mms`;

-- (Optional) set root password; comment out if using unix_socket auth
-- ALTER USER 'root'@'localhost' IDENTIFIED BY 'cachicamo';

-- campaigns (index/list of campaigns)
CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `campaign_name` VARCHAR(255) NOT NULL,
  `campaign_ext_id` VARCHAR(64) NOT NULL,
  `originating_number` VARCHAR(20) NOT NULL,
  `phone_numbers_file` VARCHAR(255) NULL,
  `image_file` VARCHAR(255) NULL,
  `text_file` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_id` INT UNSIGNED NULL,
  `user_name` VARCHAR(128) NULL,
  `archived` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_campaigns_ext` (`campaign_ext_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- template recipients table (per-campaign tables are created LIKE this)
CREATE TABLE IF NOT EXISTS `campaign` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `srcnum` VARCHAR(20) NOT NULL,
  `campaignID` VARCHAR(64) NOT NULL,
  `destnum` VARCHAR(40) NOT NULL,
  `sent` TINYINT(1) NOT NULL DEFAULT 0,
  `operator_msg_id` VARCHAR(40) NULL,
  `dlr_id` VARCHAR(40) NULL,
  `dlr_done_at` DATETIME NULL,
  `dlr_delivery_status` VARCHAR(40) NULL,
  `dlr_delivery_error_code` VARCHAR(5) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_campaign_sent` (`sent`),
  KEY `idx_campaign_destnum` (`destnum`),
  KEY `idx_campaign_operator_msg_id` (`operator_msg_id`),
  KEY `idx_campaign_dlr_id` (`dlr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- inbound log (legacy) + flags we added
CREATE TABLE IF NOT EXISTS `smsrec` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_num` VARCHAR(20) NULL,
  `to_num` VARCHAR(20) NULL,
  `message` VARCHAR(1024) NULL,
  `message_norm` VARCHAR(128) NULL,
  `msg_id` VARCHAR(64) NULL,
  `insert_date` DATETIME NULL,
  `replied` SMALLINT(5) UNSIGNED NULL,
  `keyword_code` VARCHAR(32) NULL,
  `has_conversation` TINYINT(1) NOT NULL DEFAULT 0,
  `pending_from_customer` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_smsrec_from_to` (`from_num`,`to_num`),
  KEY `idx_smsrec_msgid` (`msg_id`),
  KEY `idx_smsrec_insert` (`insert_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- keyword engine
CREATE TABLE IF NOT EXISTS `sms_keywords` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `keyword_code` VARCHAR(32) NOT NULL,     -- canonical: OPTOUT, HELP, etc.
  `variant_norm` VARCHAR(128) NOT NULL,    -- normalized variant: "stop", "parar", etc.
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_kw_variant` (`variant_norm`),
  KEY `idx_kw_code` (`keyword_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sms_responses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `keyword_code` VARCHAR(32) NOT NULL,     -- matches sms_keywords.keyword_code
  `response_text` VARCHAR(1600) NOT NULL,
  `auto_reply` TINYINT(1) NOT NULL DEFAULT 1,
  `is_optout` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_resp_code` (`keyword_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- opt-outs per pair
CREATE TABLE IF NOT EXISTS `sms_optouts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_num` VARCHAR(20) NOT NULL,
  `to_num` VARCHAR(20) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_pair` (`from_num`,`to_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- conversations
CREATE TABLE IF NOT EXISTS `sms_conversations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_num` VARCHAR(20) NOT NULL,
  `to_num` VARCHAR(20) NOT NULL,
  `status` ENUM('open','closed') NOT NULL DEFAULT 'open',
  `last_customer_msg_at` DATETIME NULL,
  `last_operator_msg_at` DATETIME NULL,
  `unread_customer_count` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_conv_pair` (`from_num`,`to_num`),
  KEY `idx_conv_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sms_conversation_msgs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` INT UNSIGNED NOT NULL,
  `direction` ENUM('IN','OUT') NOT NULL,
  `body` VARCHAR(1600) NOT NULL,
  `provider_msg_id` VARCHAR(64) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_msg_conv` (`conversation_id`),
  KEY `idx_msg_provider` (`provider_msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- DLR storage
CREATE TABLE IF NOT EXISTS `mms_dlr` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dlr_id` VARCHAR(64) NULL,
  `message_id` VARCHAR(64) NULL,
  `transaction_id` VARCHAR(64) NULL,
  `from_num` VARCHAR(32) NULL,
  `to_nums` VARCHAR(255) NULL,
  `mm_status_code` VARCHAR(64) NULL,
  `provider_type` VARCHAR(64) NULL,
  `raw_json` MEDIUMTEXT NULL,
  `received_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mms_msg` (`message_id`),
  KEY `idx_mms_tx` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sms_dlr` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dlr_id` VARCHAR(64) NULL,
  `object` VARCHAR(32) NULL,
  `type` VARCHAR(32) NULL,
  `from_num` VARCHAR(32) NULL,
  `to_num` VARCHAR(32) NULL,
  `sms_id` VARCHAR(64) NULL,
  `inserted_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `submitted_at` DATETIME NULL,
  `done_at` DATETIME NULL,
  `delivery_status` VARCHAR(32) NULL,
  `delivery_error_code` VARCHAR(32) NULL,
  `raw_json` MEDIUMTEXT NULL,
  `received_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sms_id` (`sms_id`),
  KEY `idx_sms_status` (`delivery_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- audit table
CREATE TABLE IF NOT EXISTS `msg_sent_audit` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `campaigns_id` INT UNSIGNED NOT NULL,
  `campaign_ex_id` VARCHAR(20) NOT NULL,
  `datesent` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `destnum` VARCHAR(40) NOT NULL,
  `srcnum` VARCHAR(20) NOT NULL,
  `operator_msg_id` VARCHAR(40) NULL,
  `dlr_id` VARCHAR(40) NULL,
  `dlr_done_at` DATETIME NULL,
  `dlr_delivery_status` VARCHAR(40) NULL,
  `dlr_delivery_error_code` VARCHAR(5) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_msa_campaigns_id` (`campaigns_id`),
  KEY `idx_msa_campaign_ex_id` (`campaign_ex_id`),
  KEY `idx_msa_destnum` (`destnum`),
  KEY `idx_msa_datesent` (`datesent`),
  KEY `idx_msa_operator_msg_id` (`operator_msg_id`),
  KEY `idx_msa_dlr_id` (`dlr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- seed OPTOUT keyword (optional)
INSERT IGNORE INTO sms_keywords(keyword_code, variant_norm) VALUES
('OPTOUT','stop'),('OPTOUT','cancel'),('OPTOUT','unsubscribe');

INSERT IGNORE INTO sms_responses(keyword_code, response_text, auto_reply, is_optout) VALUES
('OPTOUT','You have been removed from our list. Thank you',1,1);
EOF

# --- Optional full DB dump (data) if mysqldump is available ---
if command -v mysqldump >/dev/null 2>&1; then
  echo "[*] Creating optional data dump (data_dump.sql)"
  mysqldump --routines --triggers --events --single-transaction \
    -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" > "$BUILD_ROOT/sql/data_dump.sql" || true
else
  echo "[*] mysqldump not found; skipping data dump"
fi

# --- Bootstrap script for Debian 12 ---
cat > "$BUILD_ROOT/scripts/bootstrap_debian12.sh" <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

APP_SRC="$(pwd)/app"
VHOST_SRC="$(pwd)/apache/alliance_sms.conf"
SCHEMA_SQL="$(pwd)/sql/schema.sql"
DATA_SQL="$(pwd)/sql/data_dump.sql"

# 1) System packages
export DEBIAN_FRONTEND=noninteractive
apt-get update
apt-get install -y apache2 mariadb-server \
  php php-fpm php-mysql php-curl php-mbstring php-xml php-zip php-gd php-imagick \
  imagemagick unzip zip curl certbot python3-certbot-apache

# 2) PHP-FPM + Apache modules
a2enmod proxy proxy_fcgi setenvif rewrite headers ssl
a2enconf php*-fpm || true

# 3) Deploy app
mkdir -p /var/www/html/alliance_sms
rsync -a "$APP_SRC"/ /var/www/html/alliance_sms/
mkdir -p /var/www/html/alliance_sms/logs
chown -R www-data:www-data /var/www/html/alliance_sms
chmod -R 775 /var/www/html/alliance_sms/logs

# 4) Apache vhost
cp "$VHOST_SRC" /etc/apache2/sites-available/alliance_sms.conf
a2ensite alliance_sms.conf
systemctl reload apache2

# 5) MariaDB — create DB & schema
# NOTE: If root uses unix_socket auth, the following works without password.
mariadb -e "CREATE DATABASE IF NOT EXISTS mms;"
mariadb mms < "$SCHEMA_SQL" || true

# Optionally import existing data (if present)
if [ -f "$DATA_SQL" ]; then
  mariadb mms < "$DATA_SQL" || true
fi

echo
echo "=== IMPORTANT ==="
echo "Run certbot to get real certificates:"
echo "  sudo certbot --apache -d alliance.csc20.com -d sms.csc20.com -d mms.csc20.com"
echo "Then: sudo systemctl reload apache2"
EOF
chmod +x "$BUILD_ROOT/scripts/bootstrap_debian12.sh"

# --- README ---
cat > "$BUILD_ROOT/README_DEPLOY.md" <<'EOF'
# Alliance SMS — Release Package

## Contents
- `app/` — application code (place at `/var/www/html/alliance_sms`)
- `apache/alliance_sms.conf` — Apache vhost for `alliance.csc20.com`, `sms.csc20.com`, `mms.csc20.com`
- `sql/schema.sql` — full schema (DDL). Creates DB `mms` and all required tables.
- `sql/data_dump.sql` — optional data dump (if available at build time).
- `scripts/bootstrap_debian12.sh` — one-shot installer for Debian 12.

## Quick install (new server, Debian 12)
```bash
unzip alliance_sms_package_*.zip
cd alliance_sms_release_*/
sudo bash scripts/bootstrap_debian12.sh
sudo certbot --apache -d alliance.csc20.com -d sms.csc20.com -d mms.csc20.com
sudo systemctl reload apache2

##****************ZIPIT******************
( cd "$BUILD_ROOT/.." && zip -r9 "$OUT_ZIP" "$(basename "$BUILD_ROOT")" >/dev/null )
echo "[✓] Package created: $OUT_ZIP"
echo
echo "Next steps:"
echo " scp $OUT_ZIP <user>@<new-server>:/tmp/"
echo " ssh <user>@<new-server>"
echo " cd /tmp && unzip $(basename "$OUT_ZIP") && cd $(basename "$BUILD_ROOT")"
echo " sudo bash scripts/bootstrap_debian12.sh"
##*****************************************


---

### How to use

```bash
sudo bash /var/www/html/alliance_sms/make_release.sh
# → prints the ZIP path, e.g. /tmp/alliance_sms_package_20250821_074501.zip

# copy to new server
scp /tmp/alliance_sms_package_*.zip user@new.box:/tmp/

# on new server
ssh user@new.box
cd /tmp
unzip alliance_sms_package_*.zip
cd alliance_sms_release_*/
sudo bash scripts/bootstrap_debian12.sh
sudo certbot --apache -d alliance.csc20.com -d sms.csc20.com -d mms.csc20.com
sudo systemctl reload apache2