-- Users + appointment reminders
USE BarberShop;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(150) NULL,
    sms_opt_in TINYINT(1) DEFAULT 1,
    email_opt_in TINYINT(1) DEFAULT 1,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_users_tenant_phone (tenant_id, phone),
    INDEX idx_users_tenant_email (tenant_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reminder_settings (
    tenant_id INT PRIMARY KEY,
    email_enabled TINYINT(1) DEFAULT 1,
    sms_enabled TINYINT(1) DEFAULT 0,
    hours_before INT DEFAULT 24,
    email_from_name VARCHAR(150) DEFAULT 'Company Name Here',
    email_from_address VARCHAR(150) NULL,
    email_subject VARCHAR(255) DEFAULT 'Appointment reminder',
    sms_template VARCHAR(500) DEFAULT 'Hi {name}, reminder: {service} on {date} at {time} with {barber}. Reply STOP to opt out.',
    email_template TEXT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_reminders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    booking_id INT NOT NULL,
    user_id INT NULL,
    channel VARCHAR(10) NOT NULL,
    scheduled_for DATETIME NOT NULL,
    sent_at DATETIME NULL,
    status VARCHAR(30) DEFAULT 'pending',
    provider_ref VARCHAR(100) NULL,
    error_message VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_reminders_due (status, scheduled_for),
    INDEX idx_reminders_booking (booking_id, channel)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO reminder_settings (tenant_id, email_enabled, sms_enabled, hours_before, email_from_name, email_subject)
VALUES (1, 1, 0, 24, 'Company Name Here', 'Reminder: your appointment at Company Name Here')
ON DUPLICATE KEY UPDATE hours_before = VALUES(hours_before);
