CREATE TABLE collection (
    "address" varchar(42) NOT NULL,
    "name" varchar NULL,
    CONSTRAINT data_unique_collection UNIQUE ("address"),
    PRIMARY KEY ("address")
);


CREATE TABLE nft_transfer (
    "collection" varchar(42) NOT NULL,
    "block_number" BIGINT NOT NULL,
    "transaction_hash" varchar(66) NOT NULL,
    "transaction_index" NUMERIC NULL,
    "tx_to" varchar(42) NULL,
    "tx_from" varchar(42) NULL,
    "log_index" BIGINT NOT NULL,
    "transfer_index" BIGINT NOT NULL,
    "timestamp" timestamp NULL,
    "to" varchar(42) NULL,
    "from" varchar(42) NULL,
    "amount" NUMERIC NULL,
    "token_id" NUMERIC NULL,
    "gas_price" NUMERIC NULL,
    "created_at" TIMESTAMP DEFAULT NOW(),
    "trade_currency" varchar NULL,
	"trade_price" numeric NULL,
	"trade_marketplace" int2 NULL,
    CONSTRAINT unique_nft_transfer UNIQUE (
        "transaction_hash",
        "log_index",
        "transfer_index"
    )
);

CREATE TABLE nft (
    "address" varchar(42),
    "token_id" NUMERIC,
    "owner" varchar NULL,
    CONSTRAINT data_unique_nft UNIQUE ("address", "token_id"),
    PRIMARY KEY ("address", "token_id")
);

create materialized view nft_collection_stats as
SELECT
    c."address",
    c."name",
    coalesce(T.transfers_total, 0) as transfers_total,
    coalesce(H.transfers_hourly, 0) as transfers_hourly,
    coalesce(H.receivers_hourly, 0) as receivers_hourly,
    coalesce(H.senders_hourly, 0) as senders_hourly,
    coalesce(D.transfers_daily, 0) as transfers_daily,
    coalesce(D.receivers_daily, 0) as receivers_daily,
    coalesce(D.senders_daily, 0) as senders_daily,
    N."supply",
    N."owners"
FROM
    collection c
    LEFT JOIN (
        SELECT
            collection,
            SUM(1) as transfers_total
        FROM
            nft_transfer nt
        GROUP BY
            collection
    ) T ON T.collection = c.address
    LEFT JOIN (
        SELECT
            collection,
            SUM(1) as transfers_daily,
            COUNT(distinct("to")) as receivers_daily,
            COUNT(distinct("from")) as senders_daily
        FROM
            nft_transfer nt
        where
            nt."timestamp" > (CURRENT_DATE - '1 day' :: interval)
        GROUP BY
            collection
    ) D ON D.collection = c.address
    LEFT JOIN (
        SELECT
            collection,
            SUM(1) as transfers_hourly,
            COUNT(distinct("to")) as receivers_hourly,
            COUNT(distinct("from")) as senders_hourly
        FROM
            nft_transfer nt
        where
            nt."timestamp" > (CURRENT_DATE - '1 hour' :: interval)
        GROUP BY
            collection
    ) H ON H.collection = c.address
    LEFT JOIN (
        SELECT
            address,
            SUM(1) as supply,
            COUNT(distinct("owner")) as owners
        FROM
            nft
        GROUP BY
            address
    ) N ON N.address = c.address
where
    c."type" = 721
    or c."type" = 1155 CREATE UNIQUE INDEX nft_collection_stats_idx ON nft_collection_stats (address);