migrations/models/0_20250525140513_init.py

from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS "stocks" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "symbol" VARCHAR(10) NOT NULL UNIQUE,
    "name" VARCHAR(200) NOT NULL,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS "stock_price_data" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "date" DATE NOT NULL,
    "opening_price" VARCHAR(40) NOT NULL,
    "closing_price" VARCHAR(40) NOT NULL,
    "high" VARCHAR(40) NOT NULL,
    "low" VARCHAR(40) NOT NULL,
    "volume" BIGINT NOT NULL,
    "turnover" BIGINT NOT NULL,
    "shares_in_issue" BIGINT NOT NULL,
    "market_cap" BIGINT NOT NULL,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "stock_id" INT NOT NULL REFERENCES "stocks" ("id") ON DELETE CASCADE,
    CONSTRAINT "uid_stock_price_stock_i_1f3075" UNIQUE ("stock_id", "date")
);
CREATE TABLE IF NOT EXISTS "import_tasks" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "task_type" VARCHAR(50) NOT NULL,
    "status" VARCHAR(9) NOT NULL  DEFAULT 'pending' /* PENDING: pending\nRUNNING: running\nCOMPLETED: completed\nFAILED: failed */,
    "details" JSON,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS "uttfund" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "symbol" VARCHAR(20) NOT NULL UNIQUE,
    "name" VARCHAR(100) NOT NULL
);
CREATE TABLE IF NOT EXISTS "uttfunddata" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "date" DATE NOT NULL,
    "nav_per_unit" REAL NOT NULL,
    "sale_price_per_unit" REAL NOT NULL,
    "repurchase_price_per_unit" REAL NOT NULL,
    "outstanding_number_of_units" BIGINT NOT NULL,
    "net_asset_value" BIGINT NOT NULL,
    "fund_id" INT NOT NULL REFERENCES "uttfund" ("id") ON DELETE CASCADE,
    CONSTRAINT "uid_uttfunddata_fund_id_4fe3c3" UNIQUE ("fund_id", "date")
);
CREATE TABLE IF NOT EXISTS "user" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "username" VARCHAR(50) NOT NULL UNIQUE,
    "email" VARCHAR(100) NOT NULL UNIQUE,
    "hashed_password" VARCHAR(128) NOT NULL,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS "watchlist" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "stock_id" INT REFERENCES "stocks" ("id") ON DELETE CASCADE,
    "user_id" INT NOT NULL REFERENCES "user" ("id") ON DELETE CASCADE,
    "utt_id" INT REFERENCES "uttfund" ("id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "portfolios" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "description" TEXT,
    "is_active" INT NOT NULL  DEFAULT 1,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "user_id" INT NOT NULL REFERENCES "user" ("id") ON DELETE CASCADE,
    CONSTRAINT "uid_portfolios_user_id_d03ed2" UNIQUE ("user_id", "name")
);
CREATE TABLE IF NOT EXISTS "portfolio_calendar" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "event_date" DATE NOT NULL,
    "event_type" VARCHAR(50) NOT NULL,
    "title" VARCHAR(200) NOT NULL,
    "description" TEXT,
    "asset_type" VARCHAR(10),
    "asset_id" INT,
    "estimated_amount" VARCHAR(40),
    "is_completed" INT NOT NULL  DEFAULT 0,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "portfolio_id" INT NOT NULL REFERENCES "portfolios" ("id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "portfolio_snapshots" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "snapshot_date" DATE NOT NULL,
    "total_value" VARCHAR(40) NOT NULL,
    "stock_value" VARCHAR(40) NOT NULL  DEFAULT 0,
    "bond_value" VARCHAR(40) NOT NULL  DEFAULT 0,
    "utt_value" VARCHAR(40) NOT NULL  DEFAULT 0,
    "cash_value" VARCHAR(40) NOT NULL  DEFAULT 0,
    "total_cost" VARCHAR(40) NOT NULL,
    "unrealized_gain_loss" VARCHAR(40) NOT NULL,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "portfolio_id" INT NOT NULL REFERENCES "portfolios" ("id") ON DELETE CASCADE,
    CONSTRAINT "uid_portfolio_s_portfol_dc81b0" UNIQUE ("portfolio_id", "snapshot_date")
) /* Daily snapshots for performance tracking */;
CREATE TABLE IF NOT EXISTS "portfolio_stocks" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "quantity" INT NOT NULL,
    "purchase_price" VARCHAR(40) NOT NULL,
    "purchase_date" DATE NOT NULL,
    "notes" TEXT,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "portfolio_id" INT NOT NULL REFERENCES "portfolios" ("id") ON DELETE CASCADE,
    "stock_id" INT NOT NULL REFERENCES "stocks" ("id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "portfolio_transactions" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "transaction_type" VARCHAR(20) NOT NULL,
    "asset_type" VARCHAR(10) NOT NULL,
    "asset_id" INT NOT NULL,
    "quantity" VARCHAR(40) NOT NULL,
    "price" VARCHAR(40) NOT NULL,
    "total_amount" VARCHAR(40) NOT NULL,
    "transaction_date" DATE NOT NULL,
    "notes" TEXT,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "portfolio_id" INT NOT NULL REFERENCES "portfolios" ("id") ON DELETE CASCADE
) /* Track all portfolio transactions for audit and reporting */;
CREATE TABLE IF NOT EXISTS "portfolio_utts" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "units_held" VARCHAR(40) NOT NULL,
    "purchase_price" VARCHAR(40) NOT NULL,
    "purchase_date" DATE NOT NULL,
    "notes" TEXT,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "portfolio_id" INT NOT NULL REFERENCES "portfolios" ("id") ON DELETE CASCADE,
    "utt_fund_id" INT NOT NULL REFERENCES "uttfund" ("id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "bonds" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "instrument_type" VARCHAR(50) NOT NULL,
    "auction_number" INT NOT NULL,
    "auction_date" DATE NOT NULL,
    "maturity_years" VARCHAR(10) NOT NULL,
    "maturity_date" DATE NOT NULL,
    "effective_date" DATE NOT NULL,
    "dtm" INT NOT NULL,
    "bond_auction_number" INT NOT NULL,
    "holding_number" INT NOT NULL,
    "face_value" BIGINT NOT NULL,
    "price_per_100" REAL NOT NULL,
    "coupon_rate" REAL NOT NULL,
    CONSTRAINT "uid_bonds_auction_2fb1f0" UNIQUE ("auction_number", "auction_date")
);
CREATE TABLE IF NOT EXISTS "portfolio_bonds" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "face_value_held" BIGINT NOT NULL,
    "purchase_price" VARCHAR(40) NOT NULL,
    "purchase_date" DATE NOT NULL,
    "notes" TEXT,
    "created_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
    "bond_id" INT NOT NULL REFERENCES "bonds" ("id") ON DELETE CASCADE,
    "portfolio_id" INT NOT NULL REFERENCES "portfolios" ("id") ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS "aerich" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "version" VARCHAR(255) NOT NULL,
    "app" VARCHAR(100) NOT NULL,
    "content" JSON NOT NULL
);"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """