-- Migration: Normalize all email addresses to lowercase
-- This ensures case-insensitive email handling throughout the system

PRAGMA foreign_keys = ON;

-- Update all existing email addresses to lowercase
UPDATE users SET email = LOWER(TRIM(email)) WHERE email IS NOT NULL;

-- Recreate the unique constraint to ensure lowercase uniqueness
-- (SQLite doesn't support ALTER COLUMN, so we need to drop and recreate)
CREATE TABLE users_new (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT,
    email       TEXT     UNIQUE NOT NULL,
    password    TEXT,
    salt        TEXT,
    verified    INTEGER  DEFAULT 0,
    name        TEXT,
    fname       TEXT,
    lname       TEXT,
    interface   INTEGER  DEFAULT 1,
    created     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated     DATETIME DEFAULT CURRENT_TIMESTAMP,
    timezone    TEXT,
    login       DATETIME DEFAULT null,
    config      TEXT     DEFAULT '{}'
);

-- Copy data from old table
INSERT INTO users_new 
SELECT 
    id,
    LOWER(TRIM(email)) as email,
    password,
    salt,
    verified,
    name,
    fname,
    lname,
    interface,
    created,
    updated,
    timezone,
    login,
    config
FROM users;

-- Drop old table and rename new table
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

-- Recreate indexes and triggers if they existed
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Create trigger to auto-update the updated timestamp
CREATE TRIGGER IF NOT EXISTS users_updated_trigger
AFTER UPDATE ON users
BEGIN
    UPDATE users
    SET updated = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END;