-- C38 SUPPORT — multilingual support (MySQL / MariaDB)
-- Run once. Review the CONVERT lines for any extra tables in your schema.

-- 1. UTF-8 everywhere (å ä ö, Cyrillic, č ć š đ ž)
ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE users          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tickets        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ticket_replies CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE <other_table> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. Per-user language preference (clients AND agents)
ALTER TABLE users
    ADD COLUMN preferred_language ENUM('mk','en','sr','sv') NOT NULL DEFAULT 'en'
    AFTER email;

-- 3. System-wide default chosen by administrators
CREATE TABLE IF NOT EXISTS settings (
    name  VARCHAR(64)  NOT NULL PRIMARY KEY,
    value VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (name, value) VALUES ('default_language', 'en')
    ON DUPLICATE KEY UPDATE value = value;

-- Note: the PDO DSN must also use utf8mb4, e.g.
--   mysql:host=localhost;dbname=c38;charset=utf8mb4
