CREATE TABLE IF NOT EXISTS "user" (
    "user_id"          BIGSERIAL PRIMARY KEY,
    "user_name"        TEXT,
    "user_email"       TEXT UNIQUE,
    "user_api_key"     TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS "investment_type"(
    "investment_type_id"            BIGSERIAL PRIMARY KEY,
    "investment_type_name"  TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS "btc_value"(
    "btc_value_id"            BIGSERIAL PRIMARY KEY,
    "btc_value_value"         NUMERIC(18, 8),
    "btc_value_date"          DATE UNIQUE
);

CREATE TABLE IF NOT EXISTS "investment_rentability"(
    "investment_rentability_id"            BIGSERIAL PRIMARY KEY,
    "investment_rentability_investment_type_id"         BIGINT,
    "investment_rentability_btcv_start"    BIGINT,
    "investment_rentability_btcv_end"      BIGINT,
    "investment_rentability_api_rent"      NUMERIC(10, 4),
    FOREIGN KEY ("investment_rentability_investment_type_id")             REFERENCES "investment_type" ("investment_type_id") ON DELETE CASCADE,
    FOREIGN KEY ("investment_rentability_btcv_start")        REFERENCES "btc_value" ("btc_value_id") ON DELETE CASCADE,
    FOREIGN KEY ("investment_rentability_btcv_end")          REFERENCES "btc_value" ("btc_value_id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS "investment"(
    "investment_id"         BIGSERIAL PRIMARY KEY,
    "investment_user_id"       BIGINT,
    "investment_type_id"      BIGINT,
    "investment_start"      DATE,
    "investment_end"        DATE,
    "investment_amt"        NUMERIC(18, 4),
    FOREIGN KEY ("investment_user_id")                 REFERENCES "user" ("user_id") ON DELETE CASCADE,
    FOREIGN KEY ("investment_type_id")                REFERENCES "investment_type" ("investment_type_id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS "fee"(
    "fee_id" BIGSERIAL PRIMARY KEY,
    "fee_op"  NUMERIC(10, 4),
    "fee_prorata" NUMERIC(10, 4)
);

CREATE TABLE IF NOT EXISTS "withdraws"(
    "withdraws_id"        BIGSERIAL PRIMARY KEY,
    "withdraws_tp"        TEXT,
    "withdraws_amt"       NUMERIC(18, 4),
    "withdraws_investment_id"      BIGINT,
    "withdraws_fee_id"    BIGINT,
    "withdraws_fee_amt"   NUMERIC(10, 4),
    "withdraws_date"      DATE,
    FOREIGN KEY ("withdraws_investment_id")                 REFERENCES "investment" ("investment_id") ON DELETE CASCADE,
    FOREIGN KEY ("withdraws_fee_id")                 REFERENCES "fee" ("fee_id") ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS staff (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT DEFAULT 'admin' 
);

CREATE INDEX IF NOT EXISTS idx_investment_rentability_investment_type_id ON investment_rentability (investment_rentability_investment_type_id);
CREATE INDEX IF NOT EXISTS idx_investment_rentability_btcv_start ON investment_rentability (investment_rentability_btcv_start);
CREATE INDEX IF NOT EXISTS idx_investment_rentability_btcv_end ON investment_rentability (investment_rentability_btcv_end);
CREATE INDEX IF NOT EXISTS idx_investment_user_id ON investment (investment_user_id);
CREATE INDEX IF NOT EXISTS idx_investment_investment_type_id ON investment (investment_type_id);
CREATE INDEX IF NOT EXISTS idx_withdraws_investment_id ON withdraws (withdraws_investment_id);
CREATE INDEX IF NOT EXISTS idx_withdraws_fee_id ON withdraws (withdraws_fee_id);