
CREATE TABLE IF NOT EXISTS autolockout (
    RecNo        INTEGER     PRIMARY KEY AUTOINCREMENT,
    IPAddress    TEXT,
    FirstOffense DATETIME,
    OffenseCount INTEGER     DEFAULT 0,
    LockedOut    BOOLEAN     DEFAULT 0,
    LockExpires  DATETIME
);

CREATE INDEX IF NOT EXISTS idx_ipaddress ON autolockout (
    IPAddress
);

CREATE TABLE IF NOT EXISTS deliveryqueue (
    RecNo             INTEGER        NOT NULL
                                     PRIMARY KEY AUTOINCREMENT,
    Created           DATETIME,
    NextAttemptTime   DATETIME,
    FromAddress       TEXT,
    ToAddresses       TEXT,
    TargetDomain      TEXT,
    FileName          TEXT,
    Processing        BIT        DEFAULT (0),
    Processed         BIT        DEFAULT (0),
    IsBounce          BIT        DEFAULT (0),
    Attempts          INT        DEFAULT (0),
    IPAddress         TEXT,
    AuthUser          TEXT,
    Suspended         BIT        DEFAULT (0),
    History           TEXT,
    IsSpam            BIT,
    BoundIPAddress    TEXT,
    Ret               TEXT,
    EnvId             TEXT,
    ReturnPath        TEXT,
    ProcessingStarted DATETIME,
    Subject           TEXT,
    Priority          INT            DEFAULT (3),
    IsMailbagDelivery BIT        DEFAULT (0),
    MailbagServer     TEXT,
    MailbagPort       INT
);

CREATE TABLE IF NOT EXISTS messages (
    RecNo       INTEGER        PRIMARY KEY AUTOINCREMENT UNIQUE,
    GroupId     TEXT,
    UserId      TEXT,
    FolderName  TEXT,
    UIDL        TEXT,
    UID         INTEGER,
    Received    DATETIME,
    Size        INTEGER,
    Answered    BOOLEAN,
    Flagged     BOOLEAN,
    Deleted     BOOLEAN,
    Draft       BOOLEAN,
    Recent      BOOLEAN,
    Headers     TEXT,
    Subject     TEXT,
    FromAddress TEXT,
    Seen        BOOLEAN
);

CREATE INDEX IF NOT EXISTS IDX_Mailbox ON messages (
    GroupId,
    UserId,
    FolderName
);

CREATE TABLE IF NOT EXISTS abusedb (
    recno                INTEGER     PRIMARY KEY AUTOINCREMENT,
    ipaddress            TEXT,
    countrycode          TEXT,
    abuseconfidencescore INTEGER,
    lastreportedat       DATETIME
);
CREATE INDEX IF NOT EXISTS idx_abipaddress ON abusedb (
    ipaddress
);

CREATE TABLE IF NOT EXISTS domaingroups (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    GroupId  TEXT  NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS domains (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    GroupId  TEXT  NOT NULL,
    DomainName   TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS accounts (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    AccountId  TEXT  NOT NULL UNIQUE,
    GroupId  TEXT  NOT NULL,
    Logins   TEXT  NOT NULL,
    Password TEXT NOT NULL,
    FirstName TEXT,
    LastName  TEXT,
    KeepCopies   BOOLEAN     DEFAULT 0,
    ForwardTo   TEXT,
    Admin BOOLEAN DEFAULT 0
);

CREATE TABLE IF NOT EXISTS logins (
    RecNo      INTEGER      PRIMARY KEY AUTOINCREMENT,
    Login      TEXT,
    GroupId    TEXT  NOT NULL,
    AccountId  TEXT  NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_login ON logins (
    Login
);

CREATE TABLE IF NOT EXISTS dkim (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    DomainName   TEXT NOT NULL,
    Enable   BOOLEAN     DEFAULT 0,
    Selector TEXT NOT NULL,
    PrivateKey TEXT NOT NULL,
    Algorithm TEXT DEFAULT 'rsa-sha256',
    Canonicalization TEXT DEFAULT 'relaxed/relaxed'
);

CREATE TABLE IF NOT EXISTS serverdata (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    ServiceType TEXT NOT NULL,
    DateTimeValue   DATETIME
);
CREATE TABLE IF NOT EXISTS options (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Localhost TEXT NOT NULL,
    ServerName TEXT NOT NULL,
    DnsServers TEXT NOT NULL,
    Timeout integer DEFAULT 30,
    ImapIdleTimeout integer DEFAULT 1800,
    AllowRelay BOOLEAN DEFAULT 0,
    CheckSpf BOOLEAN DEFAULT 0,
    CheckDkim BOOLEAN DEFAULT 0,
    CheckDmarc BOOLEAN DEFAULT 0,
    BouncesFromName TEXT DEFAULT 'Bounced message',
    BouncesFromEmail TEXT DEFAULT 'bounces@localhost',
    SslProtocols TEXT DEFAULT 'tlsv12,tlsv13',
    SpamFolderName TEXT DEFAULT 'Spam',
    DeliveryQueuePageSize INTEGER DEFAULT 15,
    AutoLockoutPageSize INTEGER DEFAULT 15
);

CREATE TABLE IF NOT EXISTS abuseip (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Enable BOOLEAN DEFAULT 0,
    Key TEXT,
    UpdateFrequency INTEGER DEFAULT 3,
    MinConfidenceScore INTEGER DEFAULT 80
);

CREATE TABLE IF NOT EXISTS spamassassin (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Enable BOOLEAN DEFAULT 0,
    SpamAction INTEGER DEFAULT 0,
    Server TEXT,
    Port INTEGER DEFAULT 783,
    MaxSize INTEGER DEFAULT 200
);

CREATE TABLE IF NOT EXISTS openai (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Enable BOOLEAN DEFAULT 0,
    SpamAction INTEGER DEFAULT 0,
    Model TEXT DEFAULT 'gpt-4',
    ApiKey TEXT,
    EndPoint TEXT DEFAULT 'https://api.openai.com/v1/chat/completions',
    MaxSize INTEGER DEFAULT 200
);

CREATE TABLE IF NOT EXISTS gemini (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Enable BOOLEAN DEFAULT 0,
    SpamAction INTEGER DEFAULT 0,
    Model TEXT DEFAULT 'gemini-2.5-flash',
    ApiKey TEXT,
    MaxSize INTEGER DEFAULT 200
);

CREATE TABLE IF NOT EXISTS autolockoutsettings (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Enable BOOLEAN DEFAULT 0,
    ErrorCount INTEGER DEFAULT 3,
    TimePeriod INTEGER DEFAULT 10,
    LockOutFor INTEGER DEFAULT 14
);

CREATE TABLE IF NOT EXISTS smartserver (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Enable BOOLEAN DEFAULT 0,
    Domains TEXT,
    Host TEXT,
    Port INTEGER DEFAULT 25,
    Username TEXT,
    Password TEXT
);

CREATE TABLE IF NOT EXISTS spamstats (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    Name TEXT UNIQUE,
    Passed INTEGER DEFAULT 0,
    Rejected INTEGER DEFAULT 0,
    Quarantined INTEGER DEFAULT 0,
    Error INTEGER DEFAULT 0,
    Reported INTEGER DEFAULT 0,
    LastResetDateTime DATETIME
);

CREATE TABLE IF NOT EXISTS ipfilters (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    Address TEXT NOT NULL,
    FilterType INTEGER NOT NULL,
    Created DATETIME DEFAULT CURRENT_TIMESTAMP,
    IsTrusted BOOLEAN DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_address ON ipfilters (
    Address
);

CREATE TABLE IF NOT EXISTS mailbags (
    RecNo    INTEGER PRIMARY KEY AUTOINCREMENT,
    Id TEXT NOT NULL UNIQUE,
    DomainNames TEXT NOT NULL,
    DestinationServer TEXT NOT NULL,
    DestinationPort INTEGER DEFAULT 25,
    AcceptOnlyIfDown BOOLEAN DEFAULT 0,
    ValidUsers TEXT DEFAULT ''
);

CREATE TABLE IF NOT EXISTS connectionstats (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    Protocol TEXT NOT NULL UNIQUE,
    Total INTEGER DEFAULT 0,
    Successful INTEGER DEFAULT 0,
    LastReset DATETIME
);

CREATE TABLE IF NOT EXISTS deliverystats (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Total INTEGER DEFAULT 0,
    Local INTEGER DEFAULT 0,
    Remote INTEGER DEFAULT 0,
    Bounced INTEGER DEFAULT 0,
    Failed INTEGER DEFAULT 0,
    LastReset DATETIME
);

CREATE TABLE IF NOT EXISTS services (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    ServiceType TEXT NOT NULL UNIQUE,
    Enable BIT DEFAULT 0,
    Greeting TEXT,
    LimitConnections INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS bindings (
    RecNo    INTEGER      PRIMARY KEY AUTOINCREMENT,
    ServiceType TEXT,
    IPAddress TEXT DEFAULT '::',
    Port INTEGER DEFAULT 0,
    Secure BIT DEFAULT 0,
    DualMode BIT DEFAULT 0,
    Secondary BIT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS delivery (
    RecNo INTEGER PRIMARY KEY UNIQUE,
    Enable BOOLEAN DEFAULT 0,
    NextAttempt INTEGER DEFAULT 10,
    DeliveryQueuePeriod INTEGER DEFAULT 5,
    MaxDeliveryThreads INTEGER DEFAULT 3,
    MaxDeliveryAttempts INTEGER DEFAULT 10,
    MaxMailbagDeliveryAttempts INTEGER DEFAULT 50,
    BindIpAddress TEXT DEFAULT '',
    UseTLS BOOLEAN DEFAULT 1,
    UseClientCertificate BOOLEAN DEFAULT 1
);

CREATE TABLE IF NOT EXISTS accountoptions (
    RecNo           INTEGER PRIMARY KEY AUTOINCREMENT,
    AccountId       TEXT NOT NULL UNIQUE,
    MessagesPerPage INTEGER DEFAULT (20),
    TFAEnabled      BOOLEAN DEFAULT (0),
    TFASecretKey    TEXT,
    TFACodes        TEXT,
    LoginExpiryDays INTEGER DEFAULT 30,
    RecoveryEmail    TEXT
);

CREATE TABLE IF NOT EXISTS loginslog (
    RecNo           INTEGER PRIMARY KEY AUTOINCREMENT,
    AccountId       TEXT,
    LoginDateTime   DATETIME,
    IPAddress       TEXT,
    Protocol        TEXT
);
