CREATE TABLE IF NOT EXISTS system_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    description VARCHAR(255) NULL,

    updated_by BIGINT UNSIGNED NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_settings_key (setting_key),

    CONSTRAINT fk_settings_updated_by
        FOREIGN KEY (updated_by) REFERENCES users (id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed the settings the spec calls out as "configurable rather than
-- hardcoded" (billing behavior for suspended/disconnected accounts).
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
    ('bill_suspended_customers', '0', 'Whether suspended customers still receive monthly invoices (1/0)'),
    ('bill_temp_disconnected_customers', '0', 'Whether temporarily disconnected customers still receive monthly invoices (1/0)'),
    ('reminder_days_before_due', '2', 'Days before due date to send Reminder 1'),
    ('reminder_days_after_due', '10', 'Days after due date to send Reminder 2 if unpaid');