-- =====================================================================
-- onboard-tenant.sql — create a new tenant + its first phone number
--
-- Usage:
--   1. Edit the SET @... values below for this tenant.
--   2. Run this whole file:
--        mysql -h <db_host> -u <user> -p ai_front_desk < onboard-tenant.sql
--
--      The pre-flight SELECT (Step 1) runs first and will show you any
--      existing row if this number is already assigned — if it prints
--      a row, STOP and fix @e164 before re-running, since the INSERT
--      below does not currently guard against it automatically. This
--      is unlike the retired PHP version, which aborted for you before
--      writing anything — reading the Step 1 output yourself is the
--      manual equivalent of that check.
--
-- Note: this only creates the local phone_numbers row that routes an
-- inbound call to a tenant. It does NOT provision/rent the number on
-- Telnyx or attach it to a Call Control connection — do that
-- separately in Telnyx Mission Control, using the same @connection_id
-- value you set below.
-- =====================================================================

-- Self-defending: works even if you forgot to pass the DB name on the
-- command line, or ran this via `mysql < file.sql` / `source file.sql`
-- without a preceding `USE`. Change this if your DB is named differently.
USE ai_front_desk;

-- ── Fill these in ────────────────────────────────────────────────────
SET @business_name             = 'Acme HVAC';
SET @e164                      = '+14045551234';           -- E.164: + country code + number, digits only
SET @industry                  = 'hvac';                   -- or NULL
SET @timezone                  = 'America/New_York';
SET @plan                      = 'starter';
SET @owner_phone               = '+14045550000';            -- or NULL — used by transferCall
SET @emergency_transfer_number = '+14045559999';            -- or NULL — falls back to owner_phone if unset
SET @connection_id             = 'your_telnyx_connection_id'; -- or NULL — Telnyx Call Control application ID
-- ────────────────────────────────────────────────────────────────────

-- STEP 1 — pre-flight check. If this returns a row, that number is
-- already assigned to a tenant. STOP and fix @e164 above before
-- continuing, or you'll get a duplicate-key error on the INSERT below
-- (harmless, but the transaction will roll back and nothing will be
-- created either way).
SELECT id AS existing_phone_number_id, tenant_id AS existing_tenant_id, status
FROM phone_numbers
WHERE e164_number = @e164;

-- STEP 2 — only proceed past this point if Step 1 returned zero rows.
START TRANSACTION;

INSERT INTO tenants
    (uuid, business_name, industry, timezone, status, plan, owner_phone, emergency_transfer_number, created_at, updated_at)
VALUES
    (UUID(), @business_name, @industry, @timezone, 'trial', @plan, @owner_phone, @emergency_transfer_number, NOW(), NOW());

SET @new_tenant_id = LAST_INSERT_ID();

INSERT INTO phone_numbers
    (tenant_id, e164_number, provider, connection_id, status, created_at, updated_at)
VALUES
    (@new_tenant_id, @e164, 'telnyx', @connection_id, 'active', NOW(), NOW());

COMMIT;

-- STEP 3 — confirm what was created.
SELECT
    t.id AS tenant_id, t.uuid, t.business_name, t.owner_phone, t.emergency_transfer_number,
    p.e164_number, p.connection_id, p.status
FROM tenants t
JOIN phone_numbers p ON p.tenant_id = t.id
WHERE t.id = @new_tenant_id;

-- Next steps (same as before, not automated by this script):
--   1. In Telnyx Mission Control, assign @e164 to the Call Control
--      application matching @connection_id, with its webhook URL
--      pointing at your /webhooks/telnyx.php.
--   2. Seed knowledge_base rows for this tenant — see seed-knowledge-base.sql.