backend-php/migrations/migrations_v1.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,
    "cpr_additive" TEXT NOT NULL,
    "cpr_agents_sender_phone" TEXT NOT NULL,
    "cpr_agents_sender_cep" TEXT NOT NULL,
    "cpr_agents_creditor_document_number" TEXT NOT NULL,
    "cpr_agents_creditor_status" BOOLEAN NOT NULL,
    "cpr_agents_creditor_person_type" INTEGER NOT NULL,
    "cpr_agents_endorsement_date" TEXT NOT NULL,
    "cpr_agents_endorser" BOOLEAN NOT NULL,
    "cpr_agents_creditor_id" INTEGER NOT NULL,
    "cpr_agents_wallet_holder" TEXT NOT NULL,
    "cpr_agents_cnpj_holder" TEXT NOT NULL,
    "cpr_agents_debtor_cep" TEXT NOT NULL,
    "cpr_agents_debtor_email" TEXT NOT NULL,
    "cpr_agents_debtor_name_corporate_name" TEXT NOT NULL,
    "cpr_agents_debtor_phone_number" TEXT NOT NULL,
    "cpr_agents_debtor_document_number" TEXT NOT NULL,
    "cpr_agents_debtor_status" BOOLEAN NOT NULL,
    "cpr_agents_debtor_person_type" INTEGER NOT NULL,
    "cpr_agents_debtor_id" INTEGER NOT NULL,
    "cpr_agents_sender_email" TEXT NOT NULL,
    "cpr_agents_sender_credit_agency" TEXT NOT NULL,
    "cpr_agents_sender_credit_bank" TEXT NOT NULL,
    "cpr_agents_sender_credit_checking_account" TEXT NOT NULL,
    "cpr_agents_guarantor_phone" TEXT NOT NULL,
    "cpr_agents_guarantor_email" TEXT NOT NULL,
    "cpr_agents_guarantor_cep" TEXT NOT NULL,
    "cpr_agents_guarantor_document_number" TEXT NOT NULL,
    "cpr_agents_guarantor_status" BOOLEAN NOT NULL,
    "cpr_agents_guarantor_person_type" INTEGER NOT NULL,
    "cpr_agents_guarantor_id" INTEGER NOT NULL,
    "cpr_agents_guarantor_type" INTEGER NOT NULL,
    "cpr_agents_sender_document_number" TEXT NOT NULL,
    "cpr_agents_sender_person_type" INTEGER NOT NULL,
    "cpr_area_total" NUMERIC NOT NULL,
    "cpr_area_registry" TEXT NOT NULL,
    "cpr_area_cep" TEXT NOT NULL,
    "cpr_area_address" TEXT NOT NULL,
    "cpr_area_property_unitary_fraction" TEXT NOT NULL,
    "cpr_area_id" INTEGER NOT NULL,
    "cpr_area_latitude" NUMERIC NOT NULL,
    "cpr_area_logitude" NUMERIC NOT NULL,
    "cpr_area_registration" TEXT NOT NULL,
    "cpr_area_farm_name" TEXT NOT NULL,
    "cpr_area_product_cpr_necessery_area" NUMERIC NOT NULL,
    "cpr_area_product_total_productive_area" NUMERIC NOT NULL,
    "cpr_area_product_class_type_ph" TEXT NOT NULL,
    "cpr_area_product_culture" TEXT NOT NULL,
    "cpr_area_product_culture_specificity" TEXT NOT NULL,
    "cpr_area_product_packaging_method" TEXT NOT NULL,
    "cpr_area_product_id" INTEGER NOT NULL,
    "cpr_area_product_assessment_index" TEXT NOT NULL,
    "cpr_area_product_institution_responsible_index" TEXT NOT NULL,
    "cpr_area_product_delivery_location_city" TEXT NOT NULL,
    "cpr_area_product_delivery_location_regional" TEXT NOT NULL,
    "cpr_area_product_delivery_location_fu" TEXT NOT NULL,
    "cpr_area_product_volume_price" NUMERIC NOT NULL,
    "cpr_area_product_production" TEXT NOT NULL,
    "cpr_area_product_productivity" TEXT NOT NULL,
    "cpr_area_product_harvest" TEXT NOT NULL,
    "cpr_area_product_situation" TEXT NOT NULL,
    "cpr_area_product_status" BOOLEAN NOT NULL,
    "cpr_area_product_volume_mesuring_unit" TEXT NOT NULL,
    "cpr_area_product_register_value" TEXT NOT NULL,
    "cpr_area_product_volume_quantity" TEXT NOT NULL,
    "cpr_area_owner" TEXT NOT NULL,
    "cpr_area_legal_reserve" NUMERIC NOT NULL,
    "cpr_area_status" BOOLEAN NOT NULL,
    "cpr_contracts_registration_authorized_and_clear_fi_record" BOOLEAN NOT NULL, --autorizo_registro_e_declaro_que_foi_dado_baixa_no_registro_em_quaisquer_outras_if
    "cpr_contracts_credit_operation_contract_code" TEXT NOT NULL,
    "cpr_contracts_contract_scr" TEXT NOT NULL,
    "cpr_contracts_coin_code" TEXT NOT NULL,
    "cpr_contracts_other_fi_discharge_date" TIMESTAMPTZ NOT NULL,
    "cpr_contracts_fi_hiring_date" TIMESTAMPTZ NOT NULL,
    "cpr_contracts_original_register_date" TIMESTAMPTZ NOT NULL,
    "cpr_contracts_transaction_date" DATE NOT NULL,
    "cpr_contracts_scr_standardized_identification" TEXT NOT NULL,
    "cpr_contracts_indexation" TEXT NOT NULL,
    "cpr_contracts_operation_nature" TEXT NOT NULL,
    "cpr_contracts_indexing_percentage" NUMERIC NOT NULL,
    "cpr_contracts_installments_quantity_contracted" INTEGER NOT NULL,
    "cpr_contracts_extern_reference" TEXT NOT NULL,
    "cpr_contracts_operation_interest_rate" NUMERIC NOT NULL,
    "cpr_contracts_asset_type" TEXT NOT NULL,
    "cpr_contracts_contract_financed_value" NUMERIC NOT NULL,
    "cpr_contracts_credit_liquid_value" NUMERIC NOT NULL,
    "cpr_contracts_credit_total_value" NUMERIC NOT NULL,
    "cpr_emission_delivery_date" DATE NOT NULL,
    "cpr_emission_id" INTEGER NOT NULL,
    "cpr_emission_location" TEXT NOT NULL,
    "cpr_emission_issuer_corporate_name" TEXT NOT NULL,
    "cpr_emission_cpr_type" TEXT NOT NULL, --fisico, financeiro
    "cpr_guarantee_status" BOOLEAN NOT NULL,
    "cpr_guarantee_id" INTEGER NOT NULL,
    "cpr_guarantee_type" TEXT NOT NULL,
    "cpr_installment_control_code" TEXT NOT NULL,
    "cpr_installment_due_date" TEXT NOT NULL,
    "cpr_installment_register_id" INTEGER NOT NULL,
    "cpr_installment_number" INTEGER NOT NULL,
    "cpr_installment_assignment_price" INTEGER NOT NULL,
    "cpr_installment_status" INTEGER NOT NULL,
    "cpr_installment_value" NUMERIC NOT NULL,
    "cpr_installment_main_value" NUMERIC NOT NULL,
    "cpr_status" TEXT NOT NULL --1 - ACEITO, 2 - LIQUIDADO, 3 - RECUSADO, 4 - CANCELADO
);

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_address" 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")
);