backend-Java/src/main/resources/db/migration/postgresql/V1__init.sql

CREATE TABLE "chain" (
    "chain_id"   SERIAL PRIMARY KEY,
    "chain_name" TEXT NOT NULL
);

CREATE TABLE "company" (
    "company_id" SERIAL PRIMARY KEY,
    "company_name" TEXT NOT NULL,
    "company_flag" TEXT NOT NULL
);

CREATE TABLE "role" (
    "role_id" SERIAL PRIMARY KEY,
    "company_id" INTEGER NOT NULL,
    "role_name" TEXT NOT NULL,
    "role_permission" JSONB NOT NULL,
    "role_flag" TEXT NOT NULL,
    CHECK (jsonb_typeof("role_permission") = 'object'),
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE "status" (
    "status_id" SERIAL PRIMARY KEY,
    "status_status" TEXT NOT NULL
);

CREATE TABLE "cpr" (
    "cpr_id" SERIAL PRIMARY KEY
);

CREATE TABLE "commodities" (
    "commodities_id" SERIAL PRIMARY KEY,
    "commodities_name" TEXT NOT NULL,
    "commodities_flag" TEXT NOT NULL
);

CREATE TABLE "wallet" (
    "wallet_id" SERIAL PRIMARY KEY,
    "company_id" INTEGER NOT NULL,
    "wallet_public_key" TEXT NOT NULL,
    "wallet_private_key" TEXT NOT NULL,
    "wallet_flag" TEXT NOT NULL,
    "chain_id" INTEGER NOT NULL,
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id"),
    FOREIGN KEY ("chain_id") REFERENCES "chain" ("chain_id")
);

CREATE TABLE "currency" (
    "currency_id"   SERIAL PRIMARY KEY,
    "currency_external_id" TEXT NOT NULL,
    "currency_name" TEXT NOT NULL,
    "currency_digits" INTEGER NOT NULL,
    "chain_id" INTEGER NOT NULL,
    "currency_flag" TEXT NOT NULL,
    FOREIGN KEY ("chain_id") REFERENCES "chain" ("chain_id")
);

CREATE TABLE "token" (
    "token_id" SERIAL PRIMARY KEY,
    "token_external_id" TEXT NOT NULL,
    "token_commodities_amount" INTEGER NOT NULL,
    "token_flag" TEXT NOT NULL,
    "token_commodities_value" INTEGER NOT NULL,
    "wallet_id" INTEGER NOT NULL,
    "chain_id" INTEGER NOT NULL,
    "commodities_id" INTEGER NOT NULL,
    "cpr_id" INTEGER NOT NULL,
    FOREIGN KEY ("wallet_id") REFERENCES "wallet" ("wallet_id"),
    FOREIGN KEY ("chain_id") REFERENCES "chain" ("chain_id"),
    FOREIGN KEY ("commodities_id") REFERENCES "commodities" ("commodities_id"),
    FOREIGN KEY ("cpr_id") REFERENCES "cpr" ("cpr_id")
);

CREATE TABLE "user" (
    "user_id" SERIAL PRIMARY KEY,
    "user_name" TEXT NOT NULL,
    "user_email" TEXT NOT NULL UNIQUE,
    "user_password" TEXT NOT NULL,
    "user_phone" TEXT NOT NULL,
    "user_address" TEXT NOT NULL,
    "user_city" TEXT NOT NULL,
    "user_state" TEXT NOT NULL,
    "user_zip" TEXT NOT NULL,
    "user_country" TEXT NOT NULL,
    "user_kyc" INTEGER NOT NULL,
    "user_birthdate" INTEGER NOT NULL,
    "user_cpf" TEXT NOT NULL,
    "company_id" INTEGER NOT NULL,
    "role_id" INTEGER NOT NULL,
    "user_flag" TEXT NOT NULL,
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id"),
    FOREIGN KEY ("role_id") REFERENCES "role" ("role_id")
);

CREATE TABLE "tx_coin" (
    "tx_coin_id" TEXT PRIMARY KEY,
    "tx_coin_value" TEXT NOT NULL,
    "tx_coin_flag" TEXT NOT NULL,
    "tx_coin_ts" INTEGER NOT NULL,
    "tx_coin_from_address" TEXT NOT NULL,
    "tx_coin_to_address" TEXT NOT NULL,
    "currency_id" INTEGER NOT NULL,
    "chain_id" INTEGER NOT NULL,
    FOREIGN KEY ("currency_id") REFERENCES "currency" ("currency_id"),
    FOREIGN KEY ("chain_id") REFERENCES "chain" ("chain_id")
);

CREATE TABLE "tx_token" (
    "tx_token_id" TEXT PRIMARY KEY,
    "tx_token_flag" TEXT NOT NULL,
    "tx_token_ts" INTEGER NOT NULL,
    "tx_token_from_address" TEXT NOT NULL,
    "tx_token_to_address" TEXT NOT NULL,
    "token_id" INTEGER NOT NULL,
    "chain_id" INTEGER NOT NULL,
    FOREIGN KEY ("token_id") REFERENCES "token" ("token_id"),
    FOREIGN KEY ("chain_id") REFERENCES "chain" ("chain_id")
);

CREATE TABLE "orderbook" (
    "orderbook_id" SERIAL PRIMARY KEY,
    "orderbook_flag" TEXT NOT NULL,
    "orderbook_ts" INTEGER NOT NULL,
    "orderbook_is_token" BOOLEAN NOT NULL, -- true = venda, false = compra
    "orderbook_amount" TEXT NOT NULL,
    "status_id" INTEGER NOT NULL,
    "user_id" INTEGER NOT NULL,
    "wallet_id" INTEGER NOT NULL,
    "token_id" INTEGER,
    "currency_id" INTEGER,
    "chain_id" INTEGER NOT NULL,
    FOREIGN KEY ("status_id") REFERENCES "status" ("status_id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("user_id"),
    FOREIGN KEY ("wallet_id") REFERENCES "wallet" ("wallet_id"),
    FOREIGN KEY ("token_id") REFERENCES "token" ("token_id"),
    FOREIGN KEY ("currency_id") REFERENCES "currency" ("currency_id"),
    FOREIGN KEY ("chain_id") REFERENCES "chain" ("chain_id")
);