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_type_code" TEXT NOT NULL,
    "cpr_otc_register_account_code" TEXT NOT NULL,
    "cpr_otc_payment_agent_account_code" TEXT NOT NULL,
    "cpr_otc_custodian_account_code" TEXT NOT NULL,
    "cpr_internal_control_number" TEXT NOT NULL,
    "cpr_electronic_emission_indicator" TEXT NOT NULL,
    "cpr_isin_code" TEXT NOT NULL,
    "cpr_issue_date" DATE NOT NULL,
    "cpr_maturity_date" DATE NOT NULL,
    "cpr_issue_quantity" TEXT NOT NULL,
    "cpr_issue_value" TEXT NOT NULL,
    "cpr_issue_financial_value" TEXT NOT NULL,
    "cpr_unit_value" TEXT NOT NULL,
    "cpr_reference_date" DATE NOT NULL,
    "cpr_profitability_start_date" DATE NOT NULL,
    "cpr_automatic_expiration_indicator" TEXT NOT NULL,
    "cpr_collateral_type_code" TEXT NOT NULL,
    "cpr_collateral_type_name" TEXT NOT NULL,
    "cpr_constitution_process_indicator" TEXT NOT NULL,
    "cpr_otc_bondsman_account_code" TEXT NOT NULL,
    "cpr_document_number" TEXT NOT NULL,
    "cpr_product_name" TEXT NOT NULL,
    "cpr_product_class_name" TEXT NOT NULL,
    "cpr_product_harvest" TEXT NOT NULL,
    "cpr_product_description" TEXT NOT NULL,
    "cpr_product_quantity" TEXT NOT NULL,
    "cpr_measure_unit_name" TEXT NOT NULL,
    "cpr_packaging_way_name" TEXT NOT NULL,
    "cpr_product_status_code" TEXT NOT NULL,
    "cpr_production_type_code" TEXT NOT NULL,
    "cpr_issuer_name" TEXT NOT NULL,
    "cpr_document_number" TEXT NOT NULL,
    "cpr_person_type_acronym" TEXT NOT NULL,
    "cpr_state_acronym" TEXT NOT NULL,
    "cpr_city_name" TEXT NOT NULL,
    "cpr_ibge_code" TEXT NOT NULL,
    "cpr_issuer_legal_nature_code" TEXT NOT NULL,
    "cpr_otc_favored_account_code" TEXT NOT NULL,
    "cpr_document_number" TEXT NOT NULL,
    "cpr_person_type_acronym" TEXT NOT NULL,
    "cpr_self_number" TEXT NOT NULL,
    "cpr_settlement_modality_type_code" TEXT NOT NULL,
    "cpr_otc_settlement_bank_account_code" TEXT NOT NULL,
    "cpr_deposit_quantity" TEXT NOT NULL,
    "cpr_deposit_unit_price_value" TEXT NOT NULL,
    "cpr_payment_method_code" TEXT NOT NULL,
    "cpr_index_code" TEXT NOT NULL,
    "cpr_index_short_name" TEXT NOT NULL,
    "cpr_vcp_indicator_type_code" TEXT NOT NULL,
    "cpr_indexador_percentage_value" TEXT NOT NULL,
    "cpr_interest_rate_spread_percentage" TEXT NOT NULL,
    "cpr_interest_rate_criteria_type_code" TEXT NOT NULL,
    "cpr_interest_payment_date" DATE NOT NULL,
    "cpr_interest_payment_value" TEXT NOT NULL,
    "cpr_interest_payment_frequency_code" TEXT NOT NULL,
    "cpr_interest_months_quantity" TEXT NOT NULL,
    "cpr_time_unit_type_code" TEXT NOT NULL,
    "cpr_deadline_type_code" TEXT NOT NULL,
    "cpr_payment_start_date" DATE NOT NULL,
    "cpr_amortization_type_code" TEXT NOT NULL,
    "cpr_amortization_months_quantity" TEXT NOT NULL,
    "cpr_time_unit_type_code" TEXT NOT NULL,
    "cpr_deadline_type_code" TEXT NOT NULL,
    "cpr_amortization_start_date" DATE NOT NULL,
    "cpr_scr_type_code" TEXT NOT NULL,
    "cpr_scr_customer_detail" TEXT NOT NULL,
    "cpr_person_type_acronym" TEXT NOT NULL,
    "cpr_document_number" TEXT NOT NULL,
    "cpr_contract_code" TEXT NOT NULL,
    "cpr_operation_modality_type_code" TEXT NOT NULL,
    "cpr_bacen_reference_code" TEXT NOT NULL,
    "cpr_finality_code" TEXT NOT NULL,
    "cpr_ipoc_code" TEXT NOT NULL,
    "cpr_calculation_type_code" TEXT NOT NULL,
    "cpr_initial_exchange_value" TEXT NOT NULL,
    "cpr_fixing_type_code" TEXT NOT NULL,
    "cpr_data_source_type_code" TEXT NOT NULL,
    "cpr_adjustment_frequency_type_code" TEXT NOT NULL,
    "cpr_adjustment_pro_rata_type_code" TEXT NOT NULL,
    "cpr_adjustment_type_code" TEXT NOT NULL,
    "cpr_creditor_name" TEXT NOT NULL,
    "cpr_document_number" TEXT NOT NULL,
    "cpr_ballast_type_code" TEXT NOT NULL,
    "cpr_lot_number" TEXT NOT NULL,
    "cpr_ballast_quantity" TEXT NOT NULL,
    "cpr_currency_code" TEXT NOT NULL,
    "cpr_transaction_identification" TEXT NOT NULL,
    "cpr_additional_text" TEXT NOT NULL,
    "cpr_number" TEXT NOT NULL,
    "cpr_contract_number" TEXT NOT NULL,
    "cpr_event_type_code" TEXT NOT NULL,
    "cpr_event_original_date" DATE NOT NULL,
    "cpr_unit_price_value" TEXT NOT NULL,
    "cpr_interest_unit_price_value" TEXT NOT NULL,
    "cpr_residual_value" TEXT NOT NULL,
    "cpr_amortization_percentage" TEXT NOT NULL,
    "cpr_event_quantity" TEXT NOT NULL,
    "cpr_production_place_name" TEXT NOT NULL,
    "cpr_property_registration_number" TEXT NOT NULL,
    "cpr_notary_name" TEXT NOT NULL,
    "cpr_total_production_area_in_hectares_number" TEXT NOT NULL,
    "cpr_total_area_in_hectares_number" TEXT NOT NULL,
    "cpr_car_code" TEXT NOT NULL,
    "cpr_latitude_code" TEXT NOT NULL,
    "cpr_longitude_code" TEXT NOT NULL,
    "cpr_zip_code" TEXT NOT NULL,
    "cpr_green_cpr_indicator" TEXT NOT NULL,
    "cpr_green_cpr_certificate_name" TEXT NOT NULL,
    "cpr_green_cpr_certificate_cnpj_number" TEXT NOT NULL,
    "cpr_green_cpr_georeferencing_description" TEXT NOT NULL,
    "cpr_green_cpr_declaration_indicator" TEXT NOT NULL,
    "cpr_document_deadline_days_number" TEXT NOT NULL,
    "cpr_place_name" TEXT NOT NULL,
    "cpr_state_acronym" TEXT NOT NULL,
    "cpr_city_name" TEXT NOT NULL,
    "cpr_ibge_code" TEXT NOT NULL,
    "cpr_guarantee_limit_type_code" TEXT NOT NULL,
    "cpr_mother_code" TEXT NOT NULL,
    "cpr_children_codes" JSONB NOT NULL  -- Armazena o array como JSONB, já que é uma lista
);

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