model.sql

-- TODO: Solve for issues with multi NFT collections??
CREATE TABLE marketplaces (
    marketplace_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT,
    url TEXT
);

CREATE TABLE nfts (
    nft_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    nft_symbol TEXT,
    name TEXT,
    description TEXT,
    image TEXT,
    twitter TEXT,
    discord TEXT,
    website TEXT,
    code TEXT,
    mint_size NUMERIC,
    mint_price NUMERIC,
    mint_date TIMESTAMPTZ,
    has_issue BOOLEAN,
    notes TEXT,
    chain TEXT, -- TODO: Update this
    has_staking BOOLEAN,
    has_token BOOLEAN,
    is_doxxed BOOLEAN,
    mint_marketplace_id BIGINT REFERENCES marketplaces (marketplace_id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (nft_symbol)
);

CREATE TABLE categories (
    category_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    category_name TEXT
);

CREATE TABLE nfts_categories (
    category_id BIGINT REFERENCES categories (category_id),
    nft_id BIGINT REFERENCES nfts (nft_id)
);

CREATE TABLE nfts_marketplaces (
    marketplace_id BIGINT REFERENCES marketplaces (marketplace_id),
    nft_id BIGINT REFERENCES nfts (nft_id),
    marketplace_url TEXT
);

CREATE TABLE umbrellas (
    umbrella_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT,
    url TEXT
);

CREATE TABLE nfts_umbrellas (
    umbrella_id BIGINT REFERENCES umbrellas (umbrella_id),
    nft_id BIGINT REFERENCES nfts (nft_id)
);

CREATE TABLE discord (
    discord_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    type INT,
    expires_at TIMESTAMPTZ,
    guild_id BIGINT,
    name TEXT,
    splash TEXT,
    banner TEXT,
    description TEXT,
    icon TEXT,
    verification_level INT,
    vanity_url_code TEXT,
    premium_subscription_count INT,
    nsfw BOOLEAN,
    nsfw_level INT
);

CREATE TABLE discord_stats (
    discord_id BIGINT NOT NULL REFERENCES discord (discord_id),
    approximate_member_count NUMERIC,
    approximate_presence_count NUMERIC,
    created_at TIMESTAMPTZ
);

CREATE TABLE discord_nfts (
    discord_id BIGINT NOT NULL REFERENCES discord (discord_id),
    nft_symbol TEXT REFERENCES nfts (nft_symbol),
);

-- TODO: Setup a join on the query to build fetch, so that if it needs to 
-- restart we just start it where the last "stale" data left off
CREATE TABLE twitter (
    twitter_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    screen_name TEXT,
    name TEXT,
    protected BOOLEAN,
    age_gated BOOLEAN
);

CREATE TABLE twitter_stats (
    twitter_id BIGINT NOT NULL REFERENCES twitter (twitter_id),
    remote_id TEXT UNIQUE,
    followers_count NUMERIC,
    formatted_followers_count TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE twitter_nfts (
    twitter_id BIGINT NOT NULL REFERENCES twitter (twitter_id),
    nft_symbol TEXT NOT NULL REFERENCES nfts (nft_symbol)
);

CREATE TABLE nft_market (
    nft_symbol TEXT REFERENCES nfts (nft_symbol),
    floor_price NUMERIC,
    listed_count NUMERIC,
    average_24h_price NUMERIC,
    total_volume NUMERIC,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE nft_activity (
    activity_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    signature TEXT NOT NULL,
    type TEXT, -- TODO: fix as enum
    source TEXT,
    token_mint TEXT,
    nft_symbol TEXT NOT NULL REFERENCES nfts (nft_symbol),
    slot BIGINT,
    blocktime BIGINT,
    buyer TEXT,
    buyer_referral TEXT,
    seller TEXT,
    seller_referral TEXT,
    price NUMERIC,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ,
    UNIQUE (signature, type)
);