CREATE TABLE IF NOT EXISTS activation_email_queue (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    email VARCHAR(190) NOT NULL,
    full_name VARCHAR(190) NOT NULL,
    activation_link TEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    attempts INTEGER NOT NULL DEFAULT 0,
    next_attempt_at TIMESTAMP NOT NULL DEFAULT NOW(),
    locked_at TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    last_error TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

ALTER TABLE activation_email_queue
    ADD COLUMN IF NOT EXISTS email_kind VARCHAR(50) NOT NULL DEFAULT 'activation_initial';

CREATE INDEX IF NOT EXISTS idx_activation_email_queue_status_next_attempt
    ON activation_email_queue(status, next_attempt_at);

CREATE INDEX IF NOT EXISTS idx_activation_email_queue_user_kind
    ON activation_email_queue(user_id, email_kind);