db_init.sql

-- TO CREATE THIS DATABASE RUN: psql -h localhost -U josephlarrivy -d postgres -f db_init.sql
-- TO CONNECT TO PSQL IN THE TERMINAL AND VIEW THIS DATABASE RUN: psql -h localhost -U josephlarrivy -d gatewayprojectdotnetdatabase 

-- Connect to the default "postgres" database (or another database that is not the one you want to drop)
\c postgres

-- Drop the existing database
DROP DATABASE IF EXISTS gatewayprojectdotnetdatabase;

-- Recreate the database
CREATE DATABASE gatewayprojectdotnetdatabase;

-- Switch to the newly created database
\c gatewayprojectdotnetdatabase



-- Create the Users table
CREATE TABLE Users (
    Id VARCHAR(12) NOT NULL UNIQUE,
    Email VARCHAR(255) NOT NULL UNIQUE,
    NormalizedEmail VARCHAR(255) NOT NULL UNIQUE,
    HashedPassword VARCHAR(255),
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    IsVerifiedByLoginCode BOOLEAN DEFAULT FALSE
);

CREATE TABLE VerificationCodes (
    Id SERIAL PRIMARY KEY,
    NormalizedEmail VARCHAR(255) NOT NULL REFERENCES Users(NormalizedEmail) ON DELETE CASCADE,
    Code VARCHAR(8) NOT NULL,
    CodeType VARCHAR(8) NOT NULL,
    IsUsed BOOLEAN DEFAULT FALSE,
    ExpiresAt TIMESTAMP NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE UnsuccessfulLoginAttempts (
    Id SERIAL PRIMARY KEY,
    NormalizedEmail VARCHAR(255) NOT NULL,
    AttemptedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    IpAddress VARCHAR(45) DEFAULT NULL,
    UserAgent TEXT DEFAULT NULL,
    Reason TEXT DEFAULT NULL
);

CREATE TABLE ApiKeys (
    Id SERIAL PRIMARY KEY,
    UserId VARCHAR(255) NOT NULL REFERENCES Users(Id) ON DELETE CASCADE,
    KeyName VARCHAR(255) NOT NULL,
    HashedKey VARCHAR(255) NOT NULL,
    ExpiresAt TIMESTAMP NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    IsDeactivated BOOLEAN DEFAULT FALSE,
    IsPaused BOOLEAN DEFAULT FALSE
);