-- SMS carriers (email-to-text gateways) + SMTP-oriented reminders
USE BarberShop;

CREATE TABLE IF NOT EXISTS sms_carriers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sms_domain VARCHAR(150) NOT NULL,
    mms_domain VARCHAR(150) NULL,
    country CHAR(2) DEFAULT 'US',
    status VARCHAR(30) DEFAULT 'active',
    notes VARCHAR(255) NULL,
    is_active TINYINT(1) DEFAULT 1,
    UNIQUE KEY uq_carrier_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO sms_carriers (name, sms_domain, mms_domain, status, notes, is_active) VALUES
('Verizon', 'vtext.com', 'vzwpix.com', 'degraded', 'Free gateway degrading - full shutdown announced ~Mar 2027', 1),
('AT&T', 'txt.att.net', 'mms.att.net', 'retired', 'Gateway shut down Jun 2025 - kept for reference only', 0),
('T-Mobile', 'tmomail.net', 'tmomail.net', 'retired', 'Gateway unreliable or offline since late 2024', 0),
('US Cellular', 'email.uscc.net', 'mms.uscc.net', 'active', 'May still accept email-to-SMS', 1),
('Google Fi', 'msg.fi.google.com', 'msg.fi.google.com', 'active', 'May still accept email-to-SMS', 1),
('Xfinity Mobile', 'vtext.com', 'vzwpix.com', 'degraded', 'Uses Verizon network / vtext', 1),
('Visible', 'vtext.com', 'vzwpix.com', 'degraded', 'Verizon MVNO / vtext', 1),
('Mint Mobile', 'tmomail.net', 'tmomail.net', 'retired', 'T-Mobile MVNO - gateway retired', 0),
('Cricket', 'mms.cricketwireless.net', 'mms.cricketwireless.net', 'retired', 'AT&T MVNO - gateway retired with AT&T', 0),
('Boost Mobile', 'sms.myboostmobile.com', 'myboostmobile.com', 'unknown', 'Status varies after network changes', 1),
('Metro by T-Mobile', 'mymetropcs.com', 'mymetropcs.com', 'retired', 'T-Mobile brand - treat as retired', 0),
('Other / Manual', 'custom', NULL, 'manual', 'Use custom gateway domain on the user record', 1)
ON DUPLICATE KEY UPDATE name = VALUES(name), status = VALUES(status), notes = VALUES(notes), is_active = VALUES(is_active);
