File size: 7,460 Bytes
9d4bd7c |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 |
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 """
"""
|