-- Athena Auth: canonical email event taxonomy + template assignment.

CREATE TABLE IF NOT EXISTS email_event_types (
    event_type TEXT PRIMARY KEY,
    category TEXT NOT NULL,
    description TEXT NOT NULL,
    default_template_key TEXT,
    required_variables JSONB NOT NULL DEFAULT '[]',
    optional_variables JSONB NOT NULL DEFAULT '[]',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_system BOOLEAN NOT NULL DEFAULT TRUE,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE email_templates
    ADD COLUMN IF NOT EXISTS event_type TEXT;

INSERT INTO email_event_types (
    event_type,
    category,
    description,
    default_template_key,
    required_variables,
    optional_variables,
    is_active,
    is_system,
    metadata
)
VALUES
    (
        'user.email.verify',
        'user_lifecycle',
        'Verify a user email address after signup or manual verification resend.',
        'verification_email',
        '["verification_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.password.reset',
        'user_security',
        'Send a password reset link.',
        'password_reset_email',
        '["reset_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.email.change.confirmation',
        'user_security',
        'Confirm a pending user email address change.',
        'change_email_confirmation_email',
        '["verification_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.account.delete.confirmation',
        'user_security',
        'Confirm account deletion before destructive delete.',
        'account_deletion_confirmation_email',
        '["verification_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'user.sign-in.otp',
        'user_security',
        'Deliver one-time passcode for two-factor authentication.',
        'two_factor_otp_email',
        '["otp_code"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"auth"}'::jsonb
    ),
    (
        'organization.member.invite',
        'organization_lifecycle',
        'Invite a member to join an organization.',
        'organization_invitation_email',
        '["organization_name","role","inviter_identity","invitation_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"organization"}'::jsonb
    ),
    (
        'user.sign-in.email',
        'user_security',
        'Email-based sign-in link or magic-link authentication flow.',
        NULL,
        '["sign_in_url"]'::jsonb,
        '["app_name"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'user.sign-up.welcome',
        'user_lifecycle',
        'Welcome message after account creation.',
        NULL,
        '["user_name"]'::jsonb,
        '["app_name","dashboard_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'user.password.changed',
        'user_security',
        'Security notification after password change completes.',
        NULL,
        '[]'::jsonb,
        '["app_name","support_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'user.security.alert',
        'user_security',
        'General security alert notification (suspicious sign-in, new device, policy event).',
        NULL,
        '["alert_title","alert_details"]'::jsonb,
        '["app_name","support_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.create',
        'organization_lifecycle',
        'Organization creation confirmation and onboarding.',
        NULL,
        '["organization_name"]'::jsonb,
        '["app_name","organization_url"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.added',
        'organization_lifecycle',
        'Notify when a member is added without invite flow.',
        NULL,
        '["organization_name","member_identity"]'::jsonb,
        '["app_name","actor_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.removed',
        'organization_lifecycle',
        'Notify when a member is removed from an organization.',
        NULL,
        '["organization_name","member_identity"]'::jsonb,
        '["app_name","actor_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.role.updated',
        'organization_lifecycle',
        'Notify when organization member role is changed.',
        NULL,
        '["organization_name","member_identity","new_role"]'::jsonb,
        '["app_name","previous_role","actor_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.invite.reminder',
        'organization_lifecycle',
        'Reminder message for pending organization invitations.',
        NULL,
        '["organization_name","invitation_url"]'::jsonb,
        '["app_name","role","inviter_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    ),
    (
        'organization.member.invite.revoked',
        'organization_lifecycle',
        'Notification that an invitation has been revoked.',
        NULL,
        '["organization_name","invited_email"]'::jsonb,
        '["app_name","inviter_identity"]'::jsonb,
        TRUE,
        TRUE,
        '{"scope":"future"}'::jsonb
    )
ON CONFLICT (event_type) DO UPDATE
SET
    category = EXCLUDED.category,
    description = EXCLUDED.description,
    default_template_key = EXCLUDED.default_template_key,
    required_variables = EXCLUDED.required_variables,
    optional_variables = EXCLUDED.optional_variables,
    is_active = EXCLUDED.is_active,
    is_system = EXCLUDED.is_system,
    metadata = EXCLUDED.metadata,
    updated_at = NOW();

UPDATE email_templates AS t
SET event_type = m.event_type
FROM (
    VALUES
        ('verification_email', 'user.email.verify'),
        ('password_reset_email', 'user.password.reset'),
        ('change_email_confirmation_email', 'user.email.change.confirmation'),
        ('account_deletion_confirmation_email', 'user.account.delete.confirmation'),
        ('two_factor_otp_email', 'user.sign-in.otp'),
        ('organization_invitation_email', 'organization.member.invite')
) AS m(template_key, event_type)
WHERE t.event_type IS NULL
  AND t.template_key = m.template_key;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'fk_email_templates_event_type'
          AND conrelid = 'email_templates'::regclass
    ) THEN
        ALTER TABLE email_templates
            ADD CONSTRAINT fk_email_templates_event_type
            FOREIGN KEY (event_type)
            REFERENCES email_event_types (event_type)
            ON UPDATE CASCADE
            ON DELETE SET NULL;
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_email_event_types_category
    ON email_event_types (category);

CREATE INDEX IF NOT EXISTS idx_email_event_types_is_active
    ON email_event_types (is_active);

CREATE INDEX IF NOT EXISTS idx_email_templates_event_type
    ON email_templates (event_type);

CREATE INDEX IF NOT EXISTS idx_email_templates_event_type_locale_active
    ON email_templates (event_type, locale, is_active);
