File size: 4,773 Bytes
41eef54 |
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 |
from tortoise import BaseDBAsyncClient
async def upgrade(db: BaseDBAsyncClient) -> str:
return """
CREATE TABLE IF NOT EXISTS "messages" (
"id" UUID NOT NULL PRIMARY KEY,
"device_id" VARCHAR(100),
"event" VARCHAR(100),
"message_id" VARCHAR(100),
"webhook_id" VARCHAR(100),
"message_content" TEXT NOT NULL,
"phone_number" VARCHAR(20) NOT NULL,
"received_at" TIMESTAMPTZ NOT NULL,
"sim_number" INT,
"parsed_data" JSONB,
"created_time" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS "plans" (
"id" UUID NOT NULL PRIMARY KEY,
"name" VARCHAR(100) NOT NULL UNIQUE,
"amount" DECIMAL(10,2) NOT NULL,
"duration" INT NOT NULL,
"download_speed" DOUBLE PRECISION NOT NULL,
"upload_speed" DOUBLE PRECISION NOT NULL,
"expire_date" TIMESTAMPTZ,
"is_promo" BOOL NOT NULL DEFAULT False,
"promo_duration_days" INT,
"is_valid" BOOL NOT NULL DEFAULT True
);
COMMENT ON COLUMN "plans"."name" IS 'Name of the subscription plan';
COMMENT ON COLUMN "plans"."amount" IS 'Cost of the plan';
COMMENT ON COLUMN "plans"."duration" IS 'Duration of the subscription in hours';
COMMENT ON COLUMN "plans"."download_speed" IS 'Download speed in Mbps';
COMMENT ON COLUMN "plans"."upload_speed" IS 'Upload speed in Mbps';
COMMENT ON COLUMN "plans"."expire_date" IS 'Expiration date of the plan';
COMMENT ON COLUMN "plans"."is_promo" IS 'Indicates if the plan is a promotional plan';
COMMENT ON COLUMN "plans"."promo_duration_days" IS 'Number of days the promotion is valid';
COMMENT ON COLUMN "plans"."is_valid" IS 'Indicates if the plan is valid';
CREATE TABLE IF NOT EXISTS "portals" (
"id" SERIAL NOT NULL PRIMARY KEY,
"name" VARCHAR(50) NOT NULL UNIQUE,
"description" VARCHAR(255) NOT NULL,
"url" VARCHAR(255) NOT NULL
);
COMMENT ON COLUMN "portals"."name" IS 'Name of the portal, e.g., Android or MikroTik';
COMMENT ON COLUMN "portals"."description" IS 'Description of the portal';
COMMENT ON COLUMN "portals"."url" IS 'URL of the portal, must start with http or https';
CREATE TABLE IF NOT EXISTS "users" (
"id" VARCHAR(5) NOT NULL PRIMARY KEY,
"name" VARCHAR(100) NOT NULL,
"password" VARCHAR(100) NOT NULL,
"phoneNumber" VARCHAR(15) NOT NULL UNIQUE,
"balance" DECIMAL(10,2) NOT NULL DEFAULT 0,
"mac_address" VARCHAR(17) NOT NULL,
"createdAt" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"lastLogin" TIMESTAMPTZ NOT NULL,
"failed_attempts" INT NOT NULL DEFAULT 0,
"account_locked" BOOL NOT NULL DEFAULT False,
"reset_token" VARCHAR(6) UNIQUE,
"reset_token_expiration" TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS "payments" (
"id" UUID NOT NULL PRIMARY KEY,
"amount" DECIMAL(10,2) NOT NULL,
"status" VARCHAR(50) NOT NULL DEFAULT 'pending',
"payment_method" VARCHAR(50) NOT NULL,
"transaction_id" VARCHAR(100) UNIQUE,
"created_time" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updated_time" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"plan_id" UUID REFERENCES "plans" ("id") ON DELETE CASCADE,
"user_id" VARCHAR(5) REFERENCES "users" ("id") ON DELETE CASCADE
);
COMMENT ON COLUMN "payments"."amount" IS 'Payment amount';
COMMENT ON COLUMN "payments"."status" IS 'Payment status (e.g., pending, completed, failed, balance-assigned)';
COMMENT ON COLUMN "payments"."payment_method" IS 'Payment method';
COMMENT ON COLUMN "payments"."transaction_id" IS 'Unique transaction ID for payment (for methods like Lipa Number)';
COMMENT ON COLUMN "payments"."plan_id" IS 'Plan associated with the payment';
CREATE TABLE IF NOT EXISTS "subscriptions" (
"id" UUID NOT NULL PRIMARY KEY,
"active" BOOL NOT NULL DEFAULT True,
"duration" INT NOT NULL,
"download_mb" DOUBLE PRECISION NOT NULL DEFAULT 0,
"upload_mb" DOUBLE PRECISION NOT NULL DEFAULT 0,
"created_time" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
"expiration_time" TIMESTAMPTZ,
"plan_id" UUID REFERENCES "plans" ("id") ON DELETE CASCADE,
"user_id" VARCHAR(5) NOT NULL REFERENCES "users" ("id") ON DELETE CASCADE
);
COMMENT ON COLUMN "subscriptions"."duration" IS 'Duration in hours';
COMMENT ON COLUMN "subscriptions"."download_mb" IS 'Download usage in megabytes';
COMMENT ON COLUMN "subscriptions"."upload_mb" IS 'Upload usage in megabytes';
COMMENT ON COLUMN "subscriptions"."plan_id" IS 'Plan associated with the subscription';
CREATE TABLE IF NOT EXISTS "aerich" (
"id" SERIAL NOT NULL PRIMARY KEY,
"version" VARCHAR(255) NOT NULL,
"app" VARCHAR(100) NOT NULL,
"content" JSONB NOT NULL
);"""
async def downgrade(db: BaseDBAsyncClient) -> str:
return """
"""
|