import { randomUUID } from "crypto";
import { InferSelectModel, sql } from "drizzle-orm";
import {
    bigint,
    boolean,
    index,
    integer,
    pgTable,
    primaryKey,
    real,
    serial,
    text,
    unique,
    varchar
} from "drizzle-orm/pg-core";

export const domains = pgTable("domains", {
    domainId: varchar("domainId").primaryKey(),
    baseDomain: varchar("baseDomain").notNull(),
    configManaged: boolean("configManaged").notNull().default(false),
    type: varchar("type"), // "ns", "cname", "wildcard"
    verified: boolean("verified").notNull().default(false),
    failed: boolean("failed").notNull().default(false),
    tries: integer("tries").notNull().default(0),
    certResolver: varchar("certResolver"),
    customCertResolver: varchar("customCertResolver"),
    preferWildcardCert: boolean("preferWildcardCert"),
    errorMessage: text("errorMessage"),
    lastCheckedAt: integer("lastCheckedAt")
});

export const dnsRecords = pgTable("dnsRecords", {
    id: serial("id").primaryKey(),
    domainId: varchar("domainId")
        .notNull()
        .references(() => domains.domainId, { onDelete: "cascade" }),
    recordType: varchar("recordType").notNull(), // "NS" | "CNAME" | "A" | "TXT"
    baseDomain: varchar("baseDomain"),
    value: varchar("value").notNull(),
    verified: boolean("verified").notNull().default(false)
});

export const orgs = pgTable("orgs", {
    orgId: varchar("orgId").primaryKey(),
    name: varchar("name").notNull(),
    subnet: varchar("subnet"),
    utilitySubnet: varchar("utilitySubnet"), // this is the subnet for utility addresses
    createdAt: text("createdAt"),
    requireTwoFactor: boolean("requireTwoFactor"),
    maxSessionLengthHours: integer("maxSessionLengthHours"),
    passwordExpiryDays: integer("passwordExpiryDays"),
    settingsLogRetentionDaysRequest: integer("settingsLogRetentionDaysRequest") // where 0 = dont keep logs and -1 = keep forever, and 9001 = end of the following year
        .notNull()
        .default(7),
    settingsLogRetentionDaysAccess: integer("settingsLogRetentionDaysAccess") // where 0 = dont keep logs and -1 = keep forever and 9001 = end of the following year
        .notNull()
        .default(0),
    settingsLogRetentionDaysAction: integer("settingsLogRetentionDaysAction") // where 0 = dont keep logs and -1 = keep forever and 9001 = end of the following year
        .notNull()
        .default(0),
    settingsLogRetentionDaysConnection: integer(
        "settingsLogRetentionDaysConnection"
    ) // where 0 = dont keep logs and -1 = keep forever and 9001 = end of the following year
        .notNull()
        .default(0),
    sshCaPrivateKey: text("sshCaPrivateKey"), // Encrypted SSH CA private key (PEM format)
    sshCaPublicKey: text("sshCaPublicKey"), // SSH CA public key (OpenSSH format)
    isBillingOrg: boolean("isBillingOrg"),
    billingOrgId: varchar("billingOrgId"),
    settingsEnableGlobalNewtAutoUpdate: boolean(
        "settingsEnableGlobalNewtAutoUpdate"
    )
        .notNull()
        .default(false)
});

export const orgDomains = pgTable("orgDomains", {
    orgId: varchar("orgId")
        .notNull()
        .references(() => orgs.orgId, { onDelete: "cascade" }),
    domainId: varchar("domainId")
        .notNull()
        .references(() => domains.domainId, { onDelete: "cascade" })
});

export const sites = pgTable(
    "sites",
    {
        siteId: serial("siteId").primaryKey(),
        orgId: varchar("orgId")
            .references(() => orgs.orgId, {
                onDelete: "cascade"
            })
            .notNull(),
        niceId: varchar("niceId").notNull(),
        exitNodeId: integer("exitNode").references(() => exitNodes.exitNodeId, {
            onDelete: "set null"
        }),
        name: varchar("name").notNull(),
        pubKey: varchar("pubKey"),
        subnet: varchar("subnet"),
        megabytesIn: real("bytesIn").default(0),
        megabytesOut: real("bytesOut").default(0),
        lastBandwidthUpdate: varchar("lastBandwidthUpdate"),
        type: varchar("type").notNull(), // "newt" or "wireguard"
        online: boolean("online").notNull().default(false),
        lastPing: integer("lastPing"),
        address: varchar("address"),
        endpoint: varchar("endpoint"),
        localEndpoints: varchar("localEndpoints"), // JSON encoded list of string ips on the local machine to try to connect to
        publicKey: varchar("publicKey"),
        lastHolePunch: bigint("lastHolePunch", { mode: "number" }),
        listenPort: integer("listenPort"),
        dockerSocketEnabled: boolean("dockerSocketEnabled")
            .notNull()
            .default(true),
        autoUpdateEnabled: boolean("autoUpdateEnabled")
            .notNull()
            .default(false),
        autoUpdateOverrideOrg: boolean("autoUpdateOverrideOrg")
            .notNull()
            .default(false),
        status: varchar("status")
            .$type<"pending" | "approved">()
            .default("approved")
    },
    (t) => [
        index("idx_sites_exitnodeid").on(t.exitNodeId),
        index("idx_sites_exitnode_type_siteid").on(
            t.exitNodeId,
            t.type,
            t.siteId
        ),
        index("idx_sites_orgid_niceid").on(t.orgId, t.niceId)
    ]
);

export const resources = pgTable(
    "resources",
    {
        resourceId: serial("resourceId").primaryKey(),
        resourcePolicyId: integer("resourcePolicyId").references(
            () => resourcePolicies.resourcePolicyId,
            { onDelete: "set null" }
        ),
        defaultResourcePolicyId: integer("defaultResourcePolicyId").references(
            () => resourcePolicies.resourcePolicyId,
            {
                onDelete: "restrict"
            }
        ),
        resourceGuid: varchar("resourceGuid", { length: 36 })
            .unique()
            .notNull()
            .$defaultFn(() => randomUUID()),
        orgId: varchar("orgId")
            .references(() => orgs.orgId, {
                onDelete: "cascade"
            })
            .notNull(),
        niceId: text("niceId").notNull(),
        name: varchar("name").notNull(),
        subdomain: varchar("subdomain"),
        fullDomain: varchar("fullDomain"),
        domainId: varchar("domainId").references(() => domains.domainId, {
            onDelete: "set null"
        }),
        ssl: boolean("ssl").notNull().default(false),
        blockAccess: boolean("blockAccess").notNull().default(false),
        proxyPort: integer("proxyPort"),
        sso: boolean("sso"),
        emailWhitelistEnabled: boolean("emailWhitelistEnabled"),
        applyRules: boolean("applyRules"),
        enabled: boolean("enabled").notNull().default(true),
        stickySession: boolean("stickySession").notNull().default(false),
        tlsServerName: varchar("tlsServerName"),
        setHostHeader: varchar("setHostHeader"),
        enableProxy: boolean("enableProxy").default(true),
        skipToIdpId: integer("skipToIdpId").references(() => idp.idpId, {
            onDelete: "set null"
        }),
        headers: text("headers"), // comma-separated list of headers to add to the request
        proxyProtocol: boolean("proxyProtocol").notNull().default(false),
        proxyProtocolVersion: integer("proxyProtocolVersion").default(1),
        maintenanceModeEnabled: boolean("maintenanceModeEnabled")
            .notNull()
            .default(false),
        maintenanceModeType: text("maintenanceModeType", {
            enum: ["forced", "automatic"]
        }).default("forced"), // "forced" = always show, "automatic" = only when down
        maintenanceTitle: text("maintenanceTitle"),
        maintenanceMessage: text("maintenanceMessage"),
        maintenanceEstimatedTime: text("maintenanceEstimatedTime"),
        postAuthPath: text("postAuthPath"),
        health: varchar("health").default("unknown"), // "healthy", "unhealthy", "unknown"
        wildcard: boolean("wildcard").notNull().default(false),
        mode: text("mode").default("http").notNull(), // rdp, ssh, http, vnc
        pamMode: varchar("pamMode", { length: 32 })
            .$type<"passthrough" | "push">()
            .default("passthrough"),
        authDaemonMode: varchar("authDaemonMode", { length: 32 })
            .$type<"site" | "remote" | "native">()
            .default("site"),
        authDaemonPort: integer("authDaemonPort").default(22123),
        status: varchar("status")
            .$type<"pending" | "approved">()
            .default("approved")
    },
    (t) => [
        index("idx_resources_fulldomain")
            .on(t.fullDomain)
            .where(sql`${t.fullDomain} IS NOT NULL`),
        index("idx_resources_niceid").on(t.niceId),
        index("idx_resources_orgid_niceid").on(t.orgId, t.niceId)
    ]
);

export const labels = pgTable("labels", {
    labelId: serial("labelId").primaryKey(),
    name: varchar("name").notNull(),
    color: varchar("color").notNull(),
    orgId: varchar("orgId")
        .references(() => orgs.orgId, {
            onDelete: "cascade"
        })
        .notNull()
});

export const launcherViews = pgTable("launcherViews", {
    viewId: serial("viewId").primaryKey(),
    orgId: varchar("orgId")
        .notNull()
        .references(() => orgs.orgId, { onDelete: "cascade" }),
    userId: varchar("userId").references(() => users.userId, {
        onDelete: "cascade"
    }),
    name: varchar("name").notNull(),
    config: text("config").notNull(),
    isDefault: boolean("isDefault").notNull().default(false),
    createdAt: varchar("createdAt").notNull(),
    updatedAt: varchar("updatedAt").notNull()
});

export const siteLabels = pgTable(
    "siteLabels",
    {
        siteLabelId: serial("siteLabelId").primaryKey(),
        siteId: integer("siteId")
            .references(() => sites.siteId, {
                onDelete: "cascade"
            })
            .notNull(),
        labelId: integer("labelId")
            .references(() => labels.labelId, {
                onDelete: "cascade"
            })
            .notNull()
    },
    (t) => [unique("site_label_uniq").on(t.siteId, t.labelId)]
);

export const resourceLabels = pgTable(
    "resourceLabels",
    {
        resourceLabelId: serial("resourceLabelId").primaryKey(),
        resourceId: integer("resourceId")
            .references(() => resources.resourceId, {
                onDelete: "cascade"
            })
            .notNull(),
        labelId: integer("labelId")
            .references(() => labels.labelId, {
                onDelete: "cascade"
            })
            .notNull()
    },
    (t) => [unique("resource_label_uniq").on(t.resourceId, t.labelId)]
);

export const siteResourceLabels = pgTable(
    "siteResourceLabels",
    {
        siteResourceLabelId: serial("siteResourceLabelId").primaryKey(),
        siteResourceId: integer("siteResourceId")
            .references(() => siteResources.siteResourceId, {
                onDelete: "cascade"
            })
            .notNull(),
        labelId: integer("labelId")
            .references(() => labels.labelId, {
                onDelete: "cascade"
            })
            .notNull()
    },
    (t) => [unique("site_resource_label_uniq").on(t.siteResourceId, t.labelId)]
);

export const clientLabels = pgTable(
    "clientLabels",
    {
        clientLabelId: serial("clientLabelId").primaryKey(),
        clientId: integer("clientId")
            .references(() => clients.clientId, {
                onDelete: "cascade"
            })
            .notNull(),
        labelId: integer("labelId")
            .references(() => labels.labelId, {
                onDelete: "cascade"
            })
            .notNull()
    },
    (t) => [unique("client_label_uniq").on(t.clientId, t.labelId)]
);

export const targets = pgTable(
    "targets",
    {
        targetId: serial("targetId").primaryKey(),
        resourceId: integer("resourceId")
            .references(() => resources.resourceId, {
                onDelete: "cascade"
            })
            .notNull(),
        siteId: integer("siteId")
            .references(() => sites.siteId, {
                onDelete: "cascade"
            })
            .notNull(),
        ip: varchar("ip").notNull(),
        method: varchar("method"),
        port: integer("port").notNull(),
        internalPort: integer("internalPort"),
        enabled: boolean("enabled").notNull().default(true),
        path: text("path"),
        pathMatchType: text("pathMatchType"), // exact, prefix, regex
        rewritePath: text("rewritePath"), // if set, rewrites the path to this value before sending to the target
        rewritePathType: text("rewritePathType"), // exact, prefix, regex, stripPrefix
        priority: integer("priority").notNull().default(100),
        mode: varchar("mode")
            .$type<"http" | "tcp" | "udp" | "ssh" | "rdp" | "vnc">()
            .notNull()
            .default("http"),
        authToken: varchar("authToken")
    },
    (t) => [
        index("idx_targets_resourceid_siteid").on(t.resourceId, t.siteId),
        index("idx_targets_site_enabled_priority_target_resource")
            .on(t.siteId, t.priority.desc(), t.targetId, t.resourceId)
            .where(sql`${t.enabled} = true`)
    ]
);

export const targetHealthCheck = pgTable(
    "targetHealthCheck",
    {
        targetHealthCheckId: serial("targetHealthCheckId").primaryKey(),
        targetId: integer("targetId").references(() => targets.targetId, {
            onDelete: "cascade"
        }),
        orgId: varchar("orgId")
            .references(() => orgs.orgId, {
                onDelete: "cascade"
            })
            .notNull(),
        siteId: integer("siteId")
            .references(() => sites.siteId, {
                onDelete: "cascade"
            })
            .notNull(),
        name: varchar("name"),
        hcEnabled: boolean("hcEnabled").notNull().default(false),
        hcPath: varchar("hcPath"),
        hcScheme: varchar("hcScheme"),
        hcMode: varchar("hcMode").default("http"),
        hcHostname: varchar("hcHostname"),
        hcPort: integer("hcPort"),
        hcInterval: integer("hcInterval").default(30), // in seconds
        hcUnhealthyInterval: integer("hcUnhealthyInterval").default(30), // in seconds
        hcTimeout: integer("hcTimeout").default(5), // in seconds
        hcHeaders: varchar("hcHeaders"),
        hcFollowRedirects: boolean("hcFollowRedirects").default(true),
        hcMethod: varchar("hcMethod").default("GET"),
        hcStatus: integer("hcStatus"), // http code
        hcHealth: text("hcHealth")
            .$type<"unknown" | "healthy" | "unhealthy">()
            .default("unknown"), // "unknown", "healthy", "unhealthy"
        hcTlsServerName: text("hcTlsServerName"),
        hcHealthyThreshold: integer("hcHealthyThreshold").default(1),
        hcUnhealthyThreshold: integer("hcUnhealthyThreshold").default(1)
    },
    (t) => [index("idx_targethealthcheck_targetid").on(t.targetId)]
);

export const exitNodes = pgTable("exitNodes", {
    exitNodeId: serial("exitNodeId").primaryKey(),
    name: varchar("name").notNull(),
    address: varchar("address").notNull(),
    endpoint: varchar("endpoint").notNull(),
    publicKey: varchar("publicKey").notNull(),
    listenPort: integer("listenPort").notNull(),
    reachableAt: varchar("reachableAt"),
    maxConnections: integer("maxConnections"),
    online: boolean("online").notNull().default(false),
    lastPing: integer("lastPing"),
    type: text("type").default("gerbil"), // gerbil, remoteExitNode
    region: varchar("region")
});

export const siteResources = pgTable(
    "siteResources",
    {
        // this is for the clients
        siteResourceId: serial("siteResourceId").primaryKey(),
        orgId: varchar("orgId")
            .notNull()
            .references(() => orgs.orgId, { onDelete: "cascade" }),
        networkId: integer("networkId").references(() => networks.networkId, {
            onDelete: "set null"
        }),
        defaultNetworkId: integer("defaultNetworkId").references(
            () => networks.networkId,
            {
                onDelete: "restrict"
            }
        ),
        niceId: varchar("niceId").notNull(),
        name: varchar("name").notNull(),
        ssl: boolean("ssl").notNull().default(false),
        mode: varchar("mode")
            .$type<"host" | "cidr" | "http" | "ssh">()
            .notNull(), // "host" | "cidr" | "http"
        scheme: varchar("scheme").$type<"http" | "https">(), // only for when we are doing https or http mode
        proxyPort: integer("proxyPort"), // only for port mode
        destinationPort: integer("destinationPort"), // only for port mode
        destination: varchar("destination"), // ip, cidr, hostname; validate against the mode
        enabled: boolean("enabled").notNull().default(true),
        alias: varchar("alias"),
        aliasAddress: varchar("aliasAddress"),
        tcpPortRangeString: varchar("tcpPortRangeString")
            .notNull()
            .default("*"),
        udpPortRangeString: varchar("udpPortRangeString")
            .notNull()
            .default("*"),
        disableIcmp: boolean("disableIcmp").notNull().default(false),
        authDaemonPort: integer("authDaemonPort").default(22123),
        pamMode: varchar("pamMode", { length: 32 })
            .$type<"passthrough" | "push">()
            .default("passthrough"),
        authDaemonMode: varchar("authDaemonMode", { length: 32 })
            .$type<"site" | "remote" | "native">()
            .default("site"),
        domainId: varchar("domainId").references(() => domains.domainId, {
            onDelete: "set null"
        }),
        subdomain: varchar("subdomain"),
        fullDomain: varchar("fullDomain"),
        status: varchar("status")
            .$type<"pending" | "approved">()
            .default("approved")
    },
    (t) => [index("idx_siteresources_orgid_niceid").on(t.orgId, t.niceId)]
);

export const networks = pgTable(
    "networks",
    {
        networkId: serial("networkId").primaryKey(),
        niceId: text("niceId"),
        name: text("name"),
        scope: varchar("scope")
            .$type<"global" | "resource">()
            .notNull()
            .default("global"),
        orgId: varchar("orgId")
            .references(() => orgs.orgId, {
                onDelete: "cascade"
            })
            .notNull()
    },
    (t) => [index("idx_networks_orgid").on(t.orgId)]
);

export const siteNetworks = pgTable(
    "siteNetworks",
    {
        siteId: integer("siteId")
            .notNull()
            .references(() => sites.siteId, {
                onDelete: "cascade"
            }),
        networkId: integer("networkId")
            .notNull()
            .references(() => networks.networkId, { onDelete: "cascade" })
    },
    (t) => [
        index("idx_sitenetworks_siteid").on(t.siteId),
        index("idx_sitenetworks_networkid").on(t.networkId)
    ]
);

export const clientSiteResources = pgTable(
    "clientSiteResources",
    {
        clientId: integer("clientId")
            .notNull()
            .references(() => clients.clientId, { onDelete: "cascade" }),
        siteResourceId: integer("siteResourceId")
            .notNull()
            .references(() => siteResources.siteResourceId, {
                onDelete: "cascade"
            })
    },
    (t) => [
        index("idx_clientsiteresources_clientid").on(t.clientId),
        index("idx_clientsiteresources_siteresourceid").on(t.siteResourceId)
    ]
);

export const roleSiteResources = pgTable(
    "roleSiteResources",
    {
        roleId: integer("roleId")
            .notNull()
            .references(() => roles.roleId, { onDelete: "cascade" }),
        siteResourceId: integer("siteResourceId")
            .notNull()
            .references(() => siteResources.siteResourceId, {
                onDelete: "cascade"
            })
    },
    (t) => [index("idx_rolesiteresources_siteresourceid").on(t.siteResourceId)]
);

export const userSiteResources = pgTable(
    "userSiteResources",
    {
        userId: varchar("userId")
            .notNull()
            .references(() => users.userId, { onDelete: "cascade" }),
        siteResourceId: integer("siteResourceId")
            .notNull()
            .references(() => siteResources.siteResourceId, {
                onDelete: "cascade"
            })
    },
    (t) => [
        index("idx_usersiteresources_userid").on(t.userId),
        index("idx_usersiteresources_siteresourceid").on(t.siteResourceId)
    ]
);

export const users = pgTable("user", {
    userId: varchar("id").primaryKey(),
    email: varchar("email"),
    username: varchar("username").notNull(),
    name: varchar("name"),
    type: varchar("type").notNull(), // "internal", "oidc"
    idpId: integer("idpId").references(() => idp.idpId, {
        onDelete: "cascade"
    }),
    passwordHash: varchar("passwordHash"),
    twoFactorEnabled: boolean("twoFactorEnabled").notNull().default(false),
    twoFactorSetupRequested: boolean("twoFactorSetupRequested").default(false),
    twoFactorSecret: varchar("twoFactorSecret"),
    emailVerified: boolean("emailVerified").notNull().default(false),
    dateCreated: varchar("dateCreated").notNull(),
    termsAcceptedTimestamp: varchar("termsAcceptedTimestamp"),
    termsVersion: varchar("termsVersion"),
    marketingEmailConsent: boolean("marketingEmailConsent").default(false),
    serverAdmin: boolean("serverAdmin").notNull().default(false),
    lastPasswordChange: bigint("lastPasswordChange", { mode: "number" }),
    locale: varchar("locale")
});

export const newts = pgTable(
    "newt",
    {
        newtId: varchar("id").primaryKey(),
        secretHash: varchar("secretHash").notNull(),
        dateCreated: varchar("dateCreated").notNull(),
        version: varchar("version"),
        siteId: integer("siteId").references(() => sites.siteId, {
            onDelete: "cascade"
        })
    },
    (t) => [index("idx_newt_siteid").on(t.siteId)]
);

export const twoFactorBackupCodes = pgTable("twoFactorBackupCodes", {
    codeId: serial("id").primaryKey(),
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    codeHash: varchar("codeHash").notNull()
});

export const sessions = pgTable("session", {
    sessionId: varchar("id").primaryKey(),
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull(),
    issuedAt: bigint("issuedAt", { mode: "number" }),
    deviceAuthUsed: boolean("deviceAuthUsed").notNull().default(false)
});

export const newtSessions = pgTable("newtSession", {
    sessionId: varchar("id").primaryKey(),
    newtId: varchar("newtId")
        .notNull()
        .references(() => newts.newtId, { onDelete: "cascade" }),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull()
});

export const userOrgs = pgTable("userOrgs", {
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    orgId: varchar("orgId")
        .references(() => orgs.orgId, {
            onDelete: "cascade"
        })
        .notNull(),
    isOwner: boolean("isOwner").notNull().default(false),
    autoProvisioned: boolean("autoProvisioned").default(false),
    pamUsername: varchar("pamUsername") // cleaned username for ssh and such
});

export const emailVerificationCodes = pgTable("emailVerificationCodes", {
    codeId: serial("id").primaryKey(),
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    email: varchar("email").notNull(),
    code: varchar("code").notNull(),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull()
});

export const passwordResetTokens = pgTable("passwordResetTokens", {
    tokenId: serial("id").primaryKey(),
    email: varchar("email").notNull(),
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    tokenHash: varchar("tokenHash").notNull(),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull()
});

export const actions = pgTable("actions", {
    actionId: varchar("actionId").primaryKey(),
    name: varchar("name"),
    description: varchar("description")
});

export const roles = pgTable("roles", {
    roleId: serial("roleId").primaryKey(),
    orgId: varchar("orgId")
        .references(() => orgs.orgId, {
            onDelete: "cascade"
        })
        .notNull(),
    isAdmin: boolean("isAdmin"),
    name: varchar("name").notNull(),
    description: varchar("description"),
    requireDeviceApproval: boolean("requireDeviceApproval").default(false),
    sshSudoMode: varchar("sshSudoMode", { length: 32 }).default("none"), // "none" | "full" | "commands"
    sshSudoCommands: text("sshSudoCommands").default("[]"),
    sshCreateHomeDir: boolean("sshCreateHomeDir").default(true),
    sshUnixGroups: text("sshUnixGroups").default("[]")
});

export const userOrgRoles = pgTable(
    "userOrgRoles",
    {
        userId: varchar("userId")
            .notNull()
            .references(() => users.userId, { onDelete: "cascade" }),
        orgId: varchar("orgId")
            .notNull()
            .references(() => orgs.orgId, { onDelete: "cascade" }),
        roleId: integer("roleId")
            .notNull()
            .references(() => roles.roleId, { onDelete: "cascade" })
    },
    (t) => [unique().on(t.userId, t.orgId, t.roleId)]
);

export const roleActions = pgTable(
    "roleActions",
    {
        roleId: integer("roleId")
            .notNull()
            .references(() => roles.roleId, { onDelete: "cascade" }),
        actionId: varchar("actionId")
            .notNull()
            .references(() => actions.actionId, { onDelete: "cascade" }),
        orgId: varchar("orgId")
            .notNull()
            .references(() => orgs.orgId, { onDelete: "cascade" })
    },
    (t) => [
        index("idx_roleActions_roleId_orgId_actionId").on(
            t.roleId,
            t.orgId,
            t.actionId
        )
    ]
);

export const userActions = pgTable(
    "userActions",
    {
        userId: varchar("userId")
            .notNull()
            .references(() => users.userId, { onDelete: "cascade" }),
        actionId: varchar("actionId")
            .notNull()
            .references(() => actions.actionId, { onDelete: "cascade" }),
        orgId: varchar("orgId")
            .notNull()
            .references(() => orgs.orgId, { onDelete: "cascade" })
    },
    (t) => [
        index("idx_userActions_userId_orgId_actionId").on(
            t.userId,
            t.orgId,
            t.actionId
        )
    ]
);

export const roleSites = pgTable("roleSites", {
    roleId: integer("roleId")
        .notNull()
        .references(() => roles.roleId, { onDelete: "cascade" }),
    siteId: integer("siteId")
        .notNull()
        .references(() => sites.siteId, { onDelete: "cascade" })
});

export const userSites = pgTable("userSites", {
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    siteId: integer("siteId")
        .notNull()
        .references(() => sites.siteId, { onDelete: "cascade" })
});

export const roleResources = pgTable("roleResources", {
    roleId: integer("roleId")
        .notNull()
        .references(() => roles.roleId, { onDelete: "cascade" }),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" })
});

export const userResources = pgTable("userResources", {
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" })
});

export const rolePolicies = pgTable("rolePolicies", {
    roleId: integer("roleId")
        .notNull()
        .references(() => roles.roleId, { onDelete: "cascade" }),
    resourcePolicyId: integer("resourcePolicyId")
        .notNull()
        .references(() => resourcePolicies.resourcePolicyId, {
            onDelete: "cascade"
        })
});

export const userPolicies = pgTable("userPolicies", {
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    resourcePolicyId: integer("resourcePolicyId")
        .notNull()
        .references(() => resourcePolicies.resourcePolicyId, {
            onDelete: "cascade"
        })
});

export const resourcePolicyWhiteList = pgTable("resourcePolicyWhitelist", {
    whitelistId: serial("id").primaryKey(),
    email: varchar("email").notNull(),
    resourcePolicyId: integer("resourcePolicyId")
        .notNull()
        .references(() => resourcePolicies.resourcePolicyId, {
            onDelete: "cascade"
        })
});

export const userInvites = pgTable("userInvites", {
    inviteId: varchar("inviteId").primaryKey(),
    orgId: varchar("orgId")
        .notNull()
        .references(() => orgs.orgId, { onDelete: "cascade" }),
    email: varchar("email").notNull(),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull(),
    tokenHash: varchar("token").notNull()
});

export const userInviteRoles = pgTable(
    "userInviteRoles",
    {
        inviteId: varchar("inviteId")
            .notNull()
            .references(() => userInvites.inviteId, { onDelete: "cascade" }),
        roleId: integer("roleId")
            .notNull()
            .references(() => roles.roleId, { onDelete: "cascade" })
    },
    (t) => [primaryKey({ columns: [t.inviteId, t.roleId] })]
);

export const resourcePincode = pgTable("resourcePincode", {
    pincodeId: serial("pincodeId").primaryKey(),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" }),
    pincodeHash: varchar("pincodeHash").notNull(),
    digitLength: integer("digitLength").notNull()
});

export const resourcePassword = pgTable("resourcePassword", {
    passwordId: serial("passwordId").primaryKey(),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" }),
    passwordHash: varchar("passwordHash").notNull()
});

export const resourceHeaderAuth = pgTable("resourceHeaderAuth", {
    headerAuthId: serial("headerAuthId").primaryKey(),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" }),
    headerAuthHash: varchar("headerAuthHash").notNull()
});

export const resourceHeaderAuthExtendedCompatibility = pgTable(
    "resourceHeaderAuthExtendedCompatibility",
    {
        headerAuthExtendedCompatibilityId: serial(
            "headerAuthExtendedCompatibilityId"
        ).primaryKey(),
        resourceId: integer("resourceId")
            .notNull()
            .references(() => resources.resourceId, { onDelete: "cascade" }),
        extendedCompatibilityIsActivated: boolean(
            "extendedCompatibilityIsActivated"
        )
            .notNull()
            .default(true)
    }
);

export const resourcePolicyPincode = pgTable("resourcePolicyPincode", {
    pincodeId: serial("pincodeId").primaryKey(),
    pincodeHash: varchar("pincodeHash").notNull(),
    digitLength: integer("digitLength").notNull(),
    resourcePolicyId: integer("resourcePolicyId")
        .notNull()
        .references(() => resourcePolicies.resourcePolicyId, {
            onDelete: "cascade"
        })
});

export const resourcePolicyPassword = pgTable("resourcePolicyPassword", {
    passwordId: serial("passwordId").primaryKey(),
    passwordHash: varchar("passwordHash").notNull(),
    resourcePolicyId: integer("resourcePolicyId")
        .notNull()
        .references(() => resourcePolicies.resourcePolicyId, {
            onDelete: "cascade"
        })
});

export const resourcePolicyHeaderAuth = pgTable("resourcePolicyHeaderAuth", {
    headerAuthId: serial("headerAuthId").primaryKey(),
    headerAuthHash: varchar("headerAuthHash").notNull(),
    extendedCompatibility: boolean("extendedCompatibility")
        .notNull()
        .default(true),
    resourcePolicyId: integer("resourcePolicyId")
        .notNull()
        .references(() => resourcePolicies.resourcePolicyId, {
            onDelete: "cascade"
        })
});

export const resourceAccessToken = pgTable("resourceAccessToken", {
    accessTokenId: varchar("accessTokenId").primaryKey(),
    orgId: varchar("orgId")
        .notNull()
        .references(() => orgs.orgId, { onDelete: "cascade" }),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" }),
    userId: varchar("userId").references(() => users.userId, {
        onDelete: "cascade"
    }),
    path: varchar("path"),
    tokenHash: varchar("tokenHash").notNull(),
    sessionLength: bigint("sessionLength", { mode: "number" }).notNull(),
    expiresAt: bigint("expiresAt", { mode: "number" }),
    title: varchar("title"),
    description: varchar("description"),
    persistSession: boolean("persistSession").notNull().default(false),
    createdAt: bigint("createdAt", { mode: "number" }).notNull()
});

export const resourceSessions = pgTable("resourceSessions", {
    sessionId: varchar("id").primaryKey(),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" }),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull(),
    sessionLength: bigint("sessionLength", { mode: "number" }).notNull(),
    doNotExtend: boolean("doNotExtend").notNull().default(false),
    isRequestToken: boolean("isRequestToken"),
    userSessionId: varchar("userSessionId").references(
        () => sessions.sessionId,
        {
            onDelete: "cascade"
        }
    ),
    passwordId: integer("passwordId").references(
        () => resourcePassword.passwordId,
        {
            onDelete: "cascade"
        }
    ),
    pincodeId: integer("pincodeId").references(
        () => resourcePincode.pincodeId,
        {
            onDelete: "cascade"
        }
    ),
    whitelistId: integer("whitelistId").references(
        () => resourceWhitelist.whitelistId,
        {
            onDelete: "cascade"
        }
    ),
    accessTokenId: varchar("accessTokenId").references(
        () => resourceAccessToken.accessTokenId,
        {
            onDelete: "cascade"
        }
    ),
    policyPasswordId: integer("policyPasswordId").references(
        () => resourcePolicyPassword.passwordId,
        {
            onDelete: "cascade"
        }
    ),
    policyPincodeId: integer("policyPincodeId").references(
        () => resourcePolicyPincode.pincodeId,
        {
            onDelete: "cascade"
        }
    ),
    policyWhitelistId: integer("policyWhitelistId").references(
        () => resourcePolicyWhiteList.whitelistId,
        {
            onDelete: "cascade"
        }
    ),
    issuedAt: bigint("issuedAt", { mode: "number" })
});

export const resourceWhitelist = pgTable("resourceWhitelist", {
    whitelistId: serial("id").primaryKey(),
    email: varchar("email").notNull(),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" })
});

export const resourceOtp = pgTable("resourceOtp", {
    otpId: serial("otpId").primaryKey(),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" }),
    email: varchar("email").notNull(),
    otpHash: varchar("otpHash").notNull(),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull()
});

export const versionMigrations = pgTable("versionMigrations", {
    version: varchar("version").primaryKey(),
    executedAt: bigint("executedAt", { mode: "number" }).notNull()
});

export const resourceRules = pgTable("resourceRules", {
    ruleId: serial("ruleId").primaryKey(),
    resourceId: integer("resourceId")
        .notNull()
        .references(() => resources.resourceId, { onDelete: "cascade" }),
    enabled: boolean("enabled").notNull().default(true),
    priority: integer("priority").notNull(),
    action: varchar("action").notNull(), // ACCEPT, DROP, PASS
    match: varchar("match")
        .$type<
            | "CIDR"
            | "PATH"
            | "IP"
            | "COUNTRY"
            | "COUNTRY_IS_NOT"
            | "ASN"
            | "REGION"
        >()
        .notNull(), // CIDR, PATH, IP
    value: varchar("value").notNull()
});

export const resourcePolicyRules = pgTable("resourcePolicyRules", {
    ruleId: serial("ruleId").primaryKey(),
    resourcePolicyId: integer("resourcePolicyId")
        .notNull()
        .references(() => resourcePolicies.resourcePolicyId, {
            onDelete: "cascade"
        }),
    enabled: boolean("enabled").notNull().default(true),
    priority: integer("priority").notNull(),
    action: varchar("action").$type<"ACCEPT" | "DROP" | "PASS">().notNull(),
    match: varchar("match")
        .$type<
            | "CIDR"
            | "PATH"
            | "IP"
            | "COUNTRY"
            | "COUNTRY_IS_NOT"
            | "ASN"
            | "REGION"
        >()
        .notNull(),
    value: varchar("value").notNull()
});

export const resourcePolicies = pgTable(
    "resourcePolicies",
    {
        resourcePolicyId: serial("resourcePolicyId").primaryKey(),
        sso: boolean("sso").notNull().default(true),
        applyRules: boolean("applyRules").notNull().default(false),
        scope: varchar("scope")
            .$type<"global" | "resource">()
            .notNull()
            .default("global"),
        emailWhitelistEnabled: boolean("emailWhitelistEnabled")
            .notNull()
            .default(false),
        idpId: integer("idpId").references(() => idp.idpId, {
            onDelete: "set null"
        }),
        niceId: text("niceId").notNull(),
        name: varchar("name").notNull(),
        orgId: varchar("orgId")
            .references(() => orgs.orgId, {
                onDelete: "cascade"
            })
            .notNull()
    },
    (t) => [index("idx_resourcepolicies_orgid_niceid").on(t.orgId, t.niceId)]
);

export const supporterKey = pgTable("supporterKey", {
    keyId: serial("keyId").primaryKey(),
    key: varchar("key").notNull(),
    githubUsername: varchar("githubUsername").notNull(),
    phrase: varchar("phrase"),
    tier: varchar("tier"),
    valid: boolean("valid").notNull().default(false)
});

export const idp = pgTable("idp", {
    idpId: serial("idpId").primaryKey(),
    name: varchar("name").notNull(),
    type: varchar("type").notNull(),
    defaultRoleMapping: varchar("defaultRoleMapping"),
    defaultOrgMapping: varchar("defaultOrgMapping"),
    autoProvision: boolean("autoProvision").notNull().default(false),
    tags: text("tags")
});

export const idpOidcConfig = pgTable("idpOidcConfig", {
    idpOauthConfigId: serial("idpOauthConfigId").primaryKey(),
    idpId: integer("idpId")
        .notNull()
        .references(() => idp.idpId, { onDelete: "cascade" }),
    variant: varchar("variant").notNull().default("oidc"),
    clientId: varchar("clientId").notNull(),
    clientSecret: varchar("clientSecret").notNull(),
    authUrl: varchar("authUrl").notNull(),
    tokenUrl: varchar("tokenUrl").notNull(),
    identifierPath: varchar("identifierPath").notNull(),
    emailPath: varchar("emailPath"),
    namePath: varchar("namePath"),
    scopes: varchar("scopes").notNull()
});

export const licenseKey = pgTable("licenseKey", {
    licenseKeyId: varchar("licenseKeyId").primaryKey().notNull(),
    instanceId: varchar("instanceId").notNull(),
    token: varchar("token").notNull()
});

export const hostMeta = pgTable("hostMeta", {
    hostMetaId: varchar("hostMetaId").primaryKey().notNull(),
    createdAt: bigint("createdAt", { mode: "number" }).notNull()
});

export const apiKeys = pgTable("apiKeys", {
    apiKeyId: varchar("apiKeyId").primaryKey(),
    name: varchar("name").notNull(),
    apiKeyHash: varchar("apiKeyHash").notNull(),
    lastChars: varchar("lastChars").notNull(),
    createdAt: varchar("dateCreated").notNull(),
    isRoot: boolean("isRoot").notNull().default(false)
});

export const apiKeyActions = pgTable("apiKeyActions", {
    apiKeyId: varchar("apiKeyId")
        .notNull()
        .references(() => apiKeys.apiKeyId, { onDelete: "cascade" }),
    actionId: varchar("actionId")
        .notNull()
        .references(() => actions.actionId, { onDelete: "cascade" })
});

export const apiKeyOrg = pgTable("apiKeyOrg", {
    apiKeyId: varchar("apiKeyId")
        .notNull()
        .references(() => apiKeys.apiKeyId, { onDelete: "cascade" }),
    orgId: varchar("orgId")
        .references(() => orgs.orgId, {
            onDelete: "cascade"
        })
        .notNull()
});

export const idpOrg = pgTable("idpOrg", {
    idpId: integer("idpId")
        .notNull()
        .references(() => idp.idpId, { onDelete: "cascade" }),
    orgId: varchar("orgId")
        .notNull()
        .references(() => orgs.orgId, { onDelete: "cascade" }),
    roleMapping: varchar("roleMapping"),
    orgMapping: varchar("orgMapping")
});

export const clients = pgTable(
    "clients",
    {
        clientId: serial("clientId").primaryKey(),
        orgId: varchar("orgId")
            .references(() => orgs.orgId, {
                onDelete: "cascade"
            })
            .notNull(),
        exitNodeId: integer("exitNode").references(() => exitNodes.exitNodeId, {
            onDelete: "set null"
        }),
        userId: text("userId").references(() => users.userId, {
            // optionally tied to a user and in this case delete when the user deletes
            onDelete: "cascade"
        }),
        niceId: varchar("niceId").notNull(),
        olmId: text("olmId"), // to lock it to a specific olm optionally
        name: varchar("name").notNull(),
        pubKey: varchar("pubKey"),
        subnet: varchar("subnet").notNull(),
        megabytesIn: real("bytesIn"),
        megabytesOut: real("bytesOut"),
        lastBandwidthUpdate: varchar("lastBandwidthUpdate"),
        lastPing: integer("lastPing"),
        type: varchar("type").notNull(), // "olm"
        online: boolean("online").notNull().default(false),
        // endpoint: varchar("endpoint"),
        lastHolePunch: integer("lastHolePunch"),
        maxConnections: integer("maxConnections"),
        archived: boolean("archived").notNull().default(false),
        blocked: boolean("blocked").notNull().default(false),
        approvalState: varchar("approvalState").$type<
            "pending" | "approved" | "denied"
        >()
    },
    (t) => [
        index("idx_clients_userid").on(t.userId),
        index("idx_clients_orgid_niceid").on(t.orgId, t.niceId)
    ]
);

export const clientSitesAssociationsCache = pgTable(
    "clientSitesAssociationsCache",
    {
        clientId: integer("clientId") // not a foreign key here so after its deleted the rebuild function can delete it and send the message
            .notNull(),
        siteId: integer("siteId").notNull(),
        isRelayed: boolean("isRelayed").notNull().default(false),
        isJitMode: boolean("isJitMode").notNull().default(false),
        endpoint: varchar("endpoint"),
        publicKey: varchar("publicKey") // this will act as the session's public key for hole punching so we can track when it changes
    },
    (t) => [
        primaryKey({ columns: [t.clientId, t.siteId] }),
        index("idx_clientsitesassociationscache_siteid").on(t.siteId)
    ]
);

export const clientSiteResourcesAssociationsCache = pgTable(
    "clientSiteResourcesAssociationsCache",
    {
        clientId: integer("clientId") // not a foreign key here so after its deleted the rebuild function can delete it and send the message
            .notNull(),
        siteResourceId: integer("siteResourceId").notNull()
    },
    (t) => [
        primaryKey({ columns: [t.clientId, t.siteResourceId] }),
        index("idx_clientSiteResourcesAssociationsCache_siteResourceId").on(
            t.siteResourceId,
            t.clientId
        )
    ]
);

export const clientPostureSnapshots = pgTable("clientPostureSnapshots", {
    snapshotId: serial("snapshotId").primaryKey(),

    clientId: integer("clientId").references(() => clients.clientId, {
        onDelete: "cascade"
    }),

    collectedAt: integer("collectedAt").notNull()
});

export const olms = pgTable(
    "olms",
    {
        olmId: varchar("id").primaryKey(),
        secretHash: varchar("secretHash").notNull(),
        dateCreated: varchar("dateCreated").notNull(),
        version: text("version"),
        agent: text("agent"),
        name: varchar("name"),
        clientId: integer("clientId").references(() => clients.clientId, {
            // we will switch this depending on the current org it wants to connect to
            onDelete: "set null"
        }),
        userId: text("userId").references(() => users.userId, {
            // optionally tied to a user and in this case delete when the user deletes
            onDelete: "cascade"
        }),
        archived: boolean("archived").notNull().default(false)
    },
    (t) => [index("idx_olms_clientid").on(t.clientId)]
);

export const currentFingerprint = pgTable("currentFingerprint", {
    fingerprintId: serial("id").primaryKey(),

    olmId: text("olmId")
        .references(() => olms.olmId, { onDelete: "cascade" })
        .notNull(),

    firstSeen: integer("firstSeen").notNull(),
    lastSeen: integer("lastSeen").notNull(),
    lastCollectedAt: integer("lastCollectedAt").notNull(),

    username: text("username"),
    hostname: text("hostname"),
    platform: text("platform"),
    osVersion: text("osVersion"),
    kernelVersion: text("kernelVersion"),
    arch: text("arch"),
    deviceModel: text("deviceModel"),
    serialNumber: text("serialNumber"),
    platformFingerprint: varchar("platformFingerprint"),

    // Platform-agnostic checks

    biometricsEnabled: boolean("biometricsEnabled").notNull().default(false),
    diskEncrypted: boolean("diskEncrypted").notNull().default(false),
    firewallEnabled: boolean("firewallEnabled").notNull().default(false),
    autoUpdatesEnabled: boolean("autoUpdatesEnabled").notNull().default(false),
    tpmAvailable: boolean("tpmAvailable").notNull().default(false),

    // Windows-specific posture check information

    windowsAntivirusEnabled: boolean("windowsAntivirusEnabled")
        .notNull()
        .default(false),

    // macOS-specific posture check information

    macosSipEnabled: boolean("macosSipEnabled").notNull().default(false),
    macosGatekeeperEnabled: boolean("macosGatekeeperEnabled")
        .notNull()
        .default(false),
    macosFirewallStealthMode: boolean("macosFirewallStealthMode")
        .notNull()
        .default(false),

    // Linux-specific posture check information

    linuxAppArmorEnabled: boolean("linuxAppArmorEnabled")
        .notNull()
        .default(false),
    linuxSELinuxEnabled: boolean("linuxSELinuxEnabled").notNull().default(false)
});

export const fingerprintSnapshots = pgTable("fingerprintSnapshots", {
    snapshotId: serial("id").primaryKey(),

    fingerprintId: integer("fingerprintId").references(
        () => currentFingerprint.fingerprintId,
        {
            onDelete: "set null"
        }
    ),

    username: text("username"),
    hostname: text("hostname"),
    platform: text("platform"),
    osVersion: text("osVersion"),
    kernelVersion: text("kernelVersion"),
    arch: text("arch"),
    deviceModel: text("deviceModel"),
    serialNumber: text("serialNumber"),
    platformFingerprint: varchar("platformFingerprint"),

    // Platform-agnostic checks

    biometricsEnabled: boolean("biometricsEnabled").notNull().default(false),
    diskEncrypted: boolean("diskEncrypted").notNull().default(false),
    firewallEnabled: boolean("firewallEnabled").notNull().default(false),
    autoUpdatesEnabled: boolean("autoUpdatesEnabled").notNull().default(false),
    tpmAvailable: boolean("tpmAvailable").notNull().default(false),

    // Windows-specific posture check information

    windowsAntivirusEnabled: boolean("windowsAntivirusEnabled")
        .notNull()
        .default(false),

    // macOS-specific posture check information

    macosSipEnabled: boolean("macosSipEnabled").notNull().default(false),
    macosGatekeeperEnabled: boolean("macosGatekeeperEnabled")
        .notNull()
        .default(false),
    macosFirewallStealthMode: boolean("macosFirewallStealthMode")
        .notNull()
        .default(false),

    // Linux-specific posture check information

    linuxAppArmorEnabled: boolean("linuxAppArmorEnabled")
        .notNull()
        .default(false),
    linuxSELinuxEnabled: boolean("linuxSELinuxEnabled")
        .notNull()
        .default(false),

    hash: text("hash").notNull(),
    collectedAt: integer("collectedAt").notNull()
});

export const olmSessions = pgTable("clientSession", {
    sessionId: varchar("id").primaryKey(),
    olmId: varchar("olmId")
        .notNull()
        .references(() => olms.olmId, { onDelete: "cascade" }),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull()
});

export const userClients = pgTable("userClients", {
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, { onDelete: "cascade" }),
    clientId: integer("clientId")
        .notNull()
        .references(() => clients.clientId, { onDelete: "cascade" })
});

export const roleClients = pgTable("roleClients", {
    roleId: integer("roleId")
        .notNull()
        .references(() => roles.roleId, { onDelete: "cascade" }),
    clientId: integer("clientId")
        .notNull()
        .references(() => clients.clientId, { onDelete: "cascade" })
});

export const securityKeys = pgTable("webauthnCredentials", {
    credentialId: varchar("credentialId").primaryKey(),
    userId: varchar("userId")
        .notNull()
        .references(() => users.userId, {
            onDelete: "cascade"
        }),
    publicKey: varchar("publicKey").notNull(),
    signCount: integer("signCount").notNull(),
    transports: varchar("transports"),
    name: varchar("name"),
    lastUsed: varchar("lastUsed").notNull(),
    dateCreated: varchar("dateCreated").notNull(),
    securityKeyName: varchar("securityKeyName")
});

export const webauthnChallenge = pgTable("webauthnChallenge", {
    sessionId: varchar("sessionId").primaryKey(),
    challenge: varchar("challenge").notNull(),
    securityKeyName: varchar("securityKeyName"),
    userId: varchar("userId").references(() => users.userId, {
        onDelete: "cascade"
    }),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull() // Unix timestamp
});

export const setupTokens = pgTable("setupTokens", {
    tokenId: varchar("tokenId").primaryKey(),
    token: varchar("token").notNull(),
    used: boolean("used").notNull().default(false),
    dateCreated: varchar("dateCreated").notNull(),
    dateUsed: varchar("dateUsed")
});

// Blueprint runs
export const blueprints = pgTable("blueprints", {
    blueprintId: serial("blueprintId").primaryKey(),
    orgId: text("orgId")
        .references(() => orgs.orgId, {
            onDelete: "cascade"
        })
        .notNull(),
    name: varchar("name").notNull(),
    source: varchar("source").notNull(),
    createdAt: integer("createdAt").notNull(),
    succeeded: boolean("succeeded").notNull(),
    contents: text("contents").notNull(),
    message: text("message")
});
export const requestAuditLog = pgTable(
    "requestAuditLog",
    {
        id: serial("id").primaryKey(),
        timestamp: integer("timestamp").notNull(), // this is EPOCH time in seconds
        orgId: text("orgId").references(() => orgs.orgId, {
            onDelete: "cascade"
        }),
        action: boolean("action").notNull(),
        reason: integer("reason").notNull(),
        actorType: text("actorType"),
        actor: text("actor"),
        actorId: text("actorId"),
        resourceId: integer("resourceId"),
        siteResourceId: integer("siteResourceId"),
        ip: text("ip"),
        location: text("location"),
        userAgent: text("userAgent"),
        metadata: text("metadata"),
        headers: text("headers"), // JSON blob
        query: text("query"), // JSON blob
        originalRequestURL: text("originalRequestURL"),
        scheme: text("scheme"),
        host: text("host"),
        path: text("path"),
        method: text("method"),
        tls: boolean("tls")
    },
    (table) => [
        index("idx_requestAuditLog_timestamp").on(table.timestamp),
        index("idx_requestAuditLog_org_timestamp").on(
            table.orgId,
            table.timestamp
        )
    ]
);

export const deviceWebAuthCodes = pgTable("deviceWebAuthCodes", {
    codeId: serial("codeId").primaryKey(),
    code: text("code").notNull().unique(),
    ip: text("ip"),
    city: text("city"),
    deviceName: text("deviceName"),
    applicationName: text("applicationName").notNull(),
    expiresAt: bigint("expiresAt", { mode: "number" }).notNull(),
    createdAt: bigint("createdAt", { mode: "number" }).notNull(),
    verified: boolean("verified").notNull().default(false),
    userId: varchar("userId").references(() => users.userId, {
        onDelete: "cascade"
    })
});

export const roundTripMessageTracker = pgTable("roundTripMessageTracker", {
    messageId: serial("messageId").primaryKey(),
    wsClientId: varchar("clientId"),
    messageType: varchar("messageType"),
    sentAt: bigint("sentAt", { mode: "number" }).notNull(),
    receivedAt: bigint("receivedAt", { mode: "number" }),
    error: text("error"),
    complete: boolean("complete").notNull().default(false)
});

export const statusHistory = pgTable(
    "statusHistory",
    {
        id: serial("id").primaryKey(),
        entityType: varchar("entityType").notNull(),
        entityId: integer("entityId").notNull(),
        orgId: varchar("orgId")
            .notNull()
            .references(() => orgs.orgId, { onDelete: "cascade" }),
        status: varchar("status").notNull(),
        timestamp: integer("timestamp").notNull()
    },
    (table) => [
        index("idx_statusHistory_entity").on(
            table.entityType,
            table.entityId,
            table.timestamp
        ),
        index("idx_statusHistory_org_timestamp").on(
            table.orgId,
            table.timestamp
        )
    ]
);

export type Org = InferSelectModel<typeof orgs>;
export type User = InferSelectModel<typeof users>;
export type Site = InferSelectModel<typeof sites>;
export type Resource = InferSelectModel<typeof resources>;
export type ExitNode = InferSelectModel<typeof exitNodes>;
export type Target = InferSelectModel<typeof targets>;
export type Session = InferSelectModel<typeof sessions>;
export type Newt = InferSelectModel<typeof newts>;
export type NewtSession = InferSelectModel<typeof newtSessions>;
export type EmailVerificationCode = InferSelectModel<
    typeof emailVerificationCodes
>;
export type TwoFactorBackupCode = InferSelectModel<typeof twoFactorBackupCodes>;
export type PasswordResetToken = InferSelectModel<typeof passwordResetTokens>;
export type Role = InferSelectModel<typeof roles>;
export type Action = InferSelectModel<typeof actions>;
export type RoleAction = InferSelectModel<typeof roleActions>;
export type UserAction = InferSelectModel<typeof userActions>;
export type RoleSite = InferSelectModel<typeof roleSites>;
export type UserSite = InferSelectModel<typeof userSites>;
export type RoleResource = InferSelectModel<typeof roleResources>;
export type UserResource = InferSelectModel<typeof userResources>;
export type UserInvite = InferSelectModel<typeof userInvites>;
export type UserInviteRole = InferSelectModel<typeof userInviteRoles>;
export type UserOrg = InferSelectModel<typeof userOrgs>;
export type UserOrgRole = InferSelectModel<typeof userOrgRoles>;
export type ResourceSession = InferSelectModel<typeof resourceSessions>;
export type ResourcePincode = InferSelectModel<typeof resourcePincode>;
export type ResourcePassword = InferSelectModel<typeof resourcePassword>;
export type ResourceHeaderAuth = InferSelectModel<typeof resourceHeaderAuth>;
export type ResourceHeaderAuthExtendedCompatibility = InferSelectModel<
    typeof resourceHeaderAuthExtendedCompatibility
>;
export type ResourceOtp = InferSelectModel<typeof resourceOtp>;
export type ResourceAccessToken = InferSelectModel<typeof resourceAccessToken>;
export type ResourceWhitelist = InferSelectModel<typeof resourceWhitelist>;
export type ResourcePolicyPincode = InferSelectModel<
    typeof resourcePolicyPincode
>;
export type ResourcePolicyPassword = InferSelectModel<
    typeof resourcePolicyPassword
>;
export type ResourcePolicyHeaderAuth = InferSelectModel<
    typeof resourcePolicyHeaderAuth
>;

export type VersionMigration = InferSelectModel<typeof versionMigrations>;
export type ResourceRule = InferSelectModel<typeof resourceRules>;
export type Domain = InferSelectModel<typeof domains>;
export type DnsRecord = InferSelectModel<typeof dnsRecords>;
export type SupporterKey = InferSelectModel<typeof supporterKey>;
export type Idp = InferSelectModel<typeof idp>;
export type ApiKey = InferSelectModel<typeof apiKeys>;
export type ApiKeyAction = InferSelectModel<typeof apiKeyActions>;
export type ApiKeyOrg = InferSelectModel<typeof apiKeyOrg>;
export type Client = InferSelectModel<typeof clients>;
export type ClientSite = InferSelectModel<typeof clientSitesAssociationsCache>;
export type Olm = InferSelectModel<typeof olms>;
export type OlmSession = InferSelectModel<typeof olmSessions>;
export type UserClient = InferSelectModel<typeof userClients>;
export type RoleClient = InferSelectModel<typeof roleClients>;
export type OrgDomains = InferSelectModel<typeof orgDomains>;
export type SiteResource = InferSelectModel<typeof siteResources>;
export type SetupToken = InferSelectModel<typeof setupTokens>;
export type HostMeta = InferSelectModel<typeof hostMeta>;
export type TargetHealthCheck = InferSelectModel<typeof targetHealthCheck>;
export type IdpOidcConfig = InferSelectModel<typeof idpOidcConfig>;
export type Blueprint = InferSelectModel<typeof blueprints>;
export type LicenseKey = InferSelectModel<typeof licenseKey>;
export type SecurityKey = InferSelectModel<typeof securityKeys>;
export type WebauthnChallenge = InferSelectModel<typeof webauthnChallenge>;
export type DeviceWebAuthCode = InferSelectModel<typeof deviceWebAuthCodes>;
export type RequestAuditLog = InferSelectModel<typeof requestAuditLog>;
export type RoundTripMessageTracker = InferSelectModel<
    typeof roundTripMessageTracker
>;
export type Network = InferSelectModel<typeof networks>;
export type StatusHistory = InferSelectModel<typeof statusHistory>;
export type Label = InferSelectModel<typeof labels>;
export type LauncherView = InferSelectModel<typeof launcherViews>;
export type ResourcePolicy = InferSelectModel<typeof resourcePolicies>;
export type RolePolicy = InferSelectModel<typeof rolePolicies>;
export type UserPolicy = InferSelectModel<typeof userPolicies>;
export type ResourcePolicyRule = InferSelectModel<typeof resourcePolicyRules>;
