src/bots/db.sql

-- 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();