foodlinkk-command-center/migrations/005_email_accounts.sql

-- Email accounts configurable via Settings UI

CREATE TABLE IF NOT EXISTS email_accounts (
    id SERIAL PRIMARY KEY,
    label VARCHAR(128) NOT NULL DEFAULT 'Primary',
    email_address VARCHAR(255) NOT NULL,
    provider VARCHAR(32) DEFAULT 'custom',
    is_active BOOLEAN DEFAULT FALSE,
    smtp_host VARCHAR(255),
    smtp_port INT DEFAULT 587,
    smtp_user VARCHAR(255),
    smtp_password TEXT,
    imap_host VARCHAR(255),
    imap_port INT DEFAULT 993,
    imap_user VARCHAR(255),
    imap_password TEXT,
    imap_use_ssl BOOLEAN DEFAULT TRUE,
    sync_enabled BOOLEAN DEFAULT FALSE,
    last_sync_at TIMESTAMPTZ,
    last_test_at TIMESTAMPTZ,
    last_test_status VARCHAR(64),
    last_test_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_email_accounts_active ON email_accounts(is_active) WHERE is_active = TRUE;