Spaces:
Paused
Paused
File size: 7,088 Bytes
959b027 |
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 |
-- users_bot_telegram table
CREATE TABLE IF NOT EXISTS public.users_bot_telegram (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
telegram_id BIGINT NOT NULL, -- Telegram user ID (not unique)
username VARCHAR(255), -- Telegram username
first_name VARCHAR(255), -- User's first name
last_name VARCHAR(255), -- User's last name
email VARCHAR(255) UNIQUE NOT NULL, -- User's email address
password_hash VARCHAR(255) NOT NULL, -- Hashed password for authentication
language VARCHAR(10) NOT NULL DEFAULT 'en', -- User's preferred language
role VARCHAR(10) NOT NULL DEFAULT 'user', -- User role (user/admin/etc.)
balance DECIMAL(10, 2) NOT NULL DEFAULT 0, -- User's account balance
is_banned BOOLEAN NOT NULL DEFAULT false, -- Whether the user is banned
last_login TIMESTAMPTZ, -- Timestamp of last login
bot_id UUID REFERENCES public.bots(id) ON DELETE SET NULL, -- Reference to associated bot
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- Timestamp when record was created
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- Timestamp when record was last updated
UNIQUE(telegram_id, bot_id) -- Composite unique constraint
);
COMMENT ON TABLE public.users_bot_telegram IS 'Stores Telegram user accounts and their associated data.';
-- Enable Row Level Security
ALTER TABLE public.users_bot_telegram ENABLE ROW LEVEL SECURITY;
-- Policies
CREATE POLICY "Users can manage their own data." ON public.users_bot_telegram
FOR ALL USING (true);
-- Trigger for updating timestamps
CREATE TRIGGER update_users_bot_telegram_timestamp BEFORE UPDATE ON public.users_bot_telegram
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
-- Transactions table
CREATE TABLE IF NOT EXISTS public.transactions (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
user_id INTEGER NOT NULL REFERENCES public.users_bot_telegram(id) ON DELETE CASCADE, -- Reference to user
agent_id INTEGER REFERENCES public.users_bot_telegram(id) ON DELETE SET NULL, -- Reference to agent who processed transaction
type VARCHAR(20) NOT NULL, -- Type of transaction (deposit/withdrawal/etc.)
amount DECIMAL(10, 2) NOT NULL, -- Transaction amount
reference_id VARCHAR(255), -- External reference ID
description TEXT, -- Transaction description
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() -- Timestamp when transaction was created
);
COMMENT ON TABLE public.transactions IS 'Stores financial transactions for users.';
-- Recharge cards table
CREATE TABLE IF NOT EXISTS public.recharge_cards (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
code VARCHAR(50) UNIQUE NOT NULL, -- Unique card code
amount DECIMAL(10, 2) NOT NULL, -- Card value amount
is_used BOOLEAN NOT NULL DEFAULT false, -- Whether card has been used
is_reusable BOOLEAN NOT NULL DEFAULT false, -- Whether card can be reused
created_by INTEGER NOT NULL REFERENCES public.users_bot_telegram(id) ON DELETE CASCADE, -- Who created the card
used_by INTEGER REFERENCES public.users_bot_telegram(id) ON DELETE SET NULL, -- Who used the card
used_at TIMESTAMPTZ, -- When card was used
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When card was created
expires_at TIMESTAMPTZ -- When card expires
);
COMMENT ON TABLE public.recharge_cards IS 'Stores recharge cards for user balance top-ups.';
-- Phone numbers table
CREATE TABLE IF NOT EXISTS public.phone_numbers (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
user_id INTEGER NOT NULL REFERENCES public.users_bot_telegram(id) ON DELETE CASCADE, -- Owner of the number
country_code VARCHAR(10) NOT NULL, -- Country code for number
service VARCHAR(50) NOT NULL, -- Service the number is for
number VARCHAR(50) NOT NULL, -- Phone number
price DECIMAL(10, 2) NOT NULL, -- Cost of the number
status VARCHAR(20) NOT NULL DEFAULT 'pending', -- Current status of number
fivesim_id VARCHAR(50) NOT NULL, -- Reference to 5sim.net ID
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When number was acquired
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When number was last updated
expires_at TIMESTAMPTZ -- When number expires
);
COMMENT ON TABLE public.phone_numbers IS 'Stores phone numbers acquired for verification services.';
-- Trigger for updating timestamps
CREATE TRIGGER update_phone_numbers_timestamp BEFORE UPDATE ON public.phone_numbers
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
-- SMS messages table
CREATE TABLE IF NOT EXISTS public.sms_messages (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
phone_number_id INTEGER NOT NULL REFERENCES public.phone_numbers(id) ON DELETE CASCADE, -- Associated phone number
code VARCHAR(50) NOT NULL, -- Verification code from SMS
text TEXT NOT NULL, -- Full SMS text
received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When SMS was received
is_delivered BOOLEAN NOT NULL DEFAULT false, -- Whether SMS was delivered to user
delivered_at TIMESTAMPTZ -- When SMS was delivered to user
);
COMMENT ON TABLE public.sms_messages IS 'Stores SMS messages received for verification services.';
-- Settings table
CREATE TABLE IF NOT EXISTS public.settings (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
key VARCHAR(50) UNIQUE NOT NULL, -- Setting key
value TEXT NOT NULL, -- Setting value
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When setting was created
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() -- When setting was last updated
);
COMMENT ON TABLE public.settings IS 'Stores system configuration settings.';
-- Trigger for updating timestamps
CREATE TRIGGER update_settings_timestamp BEFORE UPDATE ON public.settings
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
-- Add bots table if it doesn't exist
CREATE TABLE IF NOT EXISTS public.bots (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
user_id UUID NOT NULL,
bot_token VARCHAR(255) NOT NULL UNIQUE,
is_active BOOLEAN NOT NULL DEFAULT true,
currency VARCHAR(10) NOT NULL DEFAULT 'USD',
profit_type VARCHAR(10) NOT NULL DEFAULT 'percentage',
profit_value_percentage DECIMAL(5,2) DEFAULT 0,
profit_value_fix DECIMAL(10,2) DEFAULT 0,
last_activity TIMESTAMPTZ,
version VARCHAR(20) NOT NULL DEFAULT '1.0.0',
-- API Keys and External Service Configuration
fivesim_api_key VARCHAR(255),
paypal_client_id VARCHAR(255),
paypal_client_secret VARCHAR(255),
crypto_wallet_address VARCHAR(255),
admin_contact VARCHAR(255),
-- Group Join Settings
join_group_required BOOLEAN DEFAULT false,
group_channel_username VARCHAR(255),
settings JSONB DEFAULT '{}',
state JSONB DEFAULT '{}',
suffix_email VARCHAR(255) DEFAULT 'saerosms.com',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE public.bots IS 'Stores bot configurations and settings';
-- Enable Row Level Security
ALTER TABLE public.bots ENABLE ROW LEVEL SECURITY;
-- Trigger for updating timestamps
CREATE TRIGGER update_bots_timestamp BEFORE UPDATE ON public.bots
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
|