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