CREATE TABLE users (
    `user_id` VARCHAR(64) PRIMARY KEY,
    `firebase_id` VARCHAR(128),
    `telegram_id` VARCHAR(64),
    `whatsapp_id` VARCHAR(64),
    `web_id` VARCHAR(64),
    `wallet_address` VARCHAR(156),
    `pinb_balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `username` VARCHAR(255),
    `is_bot` BOOLEAN DEFAULT FALSE, -- If user originates from a bot (Telegram/WhatsApp)
    `email` VARCHAR(80) NOT NULL,
    `telegram_user_name` VARCHAR(40) DEFAULT NULL,
    `phone` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_firebase_id (firebase_id),
    UNIQUE KEY idx_telegram_id (telegram_id),
    UNIQUE KEY idx_whatsapp_number (whatsapp_id),
    UNIQUE KEY idx_wallet_address (wallet_address)
    UNIQUE KEY idx_wallet_address (web_id)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--insert some test users
INSERT INTO users (
    user_id, firebase_id, telegram_id, whatsapp_id, ,web_id, wallet_address, 
    pinb_balance, username, is_bot, email, telegram_user_name, phone
) VALUES 
('usr_5464567', 'fid_7657656', 'tid_1234567', 'wa_8234567', '0x1234567890abcdef',
 100.50, 'anna.smith', false, 'anna.smith@email.com', '@annasmith', '+12025550101'),

('usr_6234567', 'fid_8234567', 'tid_2345678', 'wa_7234567', '0x2345678901bcdef0',
 250.75, 'john.davis', false, 'john.davis@email.com', '@johndavis', '+12025550102'),

('usr_7234567', 'fid_9234567', 'tid_3456789', 'wa_6234567', '0x3456789012cdef01',
 175.25, 'maria.garcia', false, 'maria.garcia@email.com', '@mariagarcia', '+12025550103'),

('usr_8234567', 'fid_1234567', 'tid_4567890', 'wa_5234567', '0x4567890123def012',
 300.00, 'david.wilson', false, 'david.wilson@email.com', '@davidwilson', '+12025550104'),

('usr_9234567', 'fid_2345678', 'tid_5678901', 'wa_4234567', '0x5678901234ef0123',
 150.80, 'sophie.chen', false, 'sophie.chen@email.com', '@sophiechen', '+12025550105');

-- stub response

/*
{
  "status": "success",
  "data": {
    "items": [
      {
        "user_id": "usr_5464567",
        "firebase_id": "fid_7657656",
        "telegram_id": "tid_1234567",
        "whatsapp_id": "wa_8234567",
        "wallet_address": "0x1234567890abcdef",
        "pinb_balance": 100.50,
        "username": "anna.smith",
        "is_bot": false,
        "email": "anna.smith@email.com",
        "telegram_user_name": "@annasmith",
        "phone": "+12025550101",
        "created_at": "2025-09-10T10:00:00Z",
        "updated_at": "2025-09-10T10:00:00Z"
      },
      {
        "user_id": "usr_6234567",
        "firebase_id": "fid_8234567",
        "telegram_id": "tid_2345678",
        "whatsapp_id": "wa_7234567",
        "wallet_address": "0x2345678901bcdef0",
        "pinb_balance": 250.75,
        "username": "john.davis",
        "is_bot": false,
        "email": "john.davis@email.com",
        "telegram_user_name": "@johndavis",
        "phone": "+12025550102",
        "created_at": "2025-09-10T10:00:00Z",
        "updated_at": "2025-09-10T10:00:00Z"
      },
      {
        "user_id": "usr_7234567",
        "firebase_id": "fid_9234567",
        "telegram_id": "tid_3456789",
        "whatsapp_id": "wa_6234567",
        "wallet_address": "0x3456789012cdef01",
        "pinb_balance": 175.25,
        "username": "maria.garcia",
        "is_bot": false,
        "email": "maria.garcia@email.com",
        "telegram_user_name": "@mariagarcia",
        "phone": "+12025550103",
        "created_at": "2025-09-10T10:00:00Z",
        "updated_at": "2025-09-10T10:00:00Z"
      }
    ],
    "pagination": {
      "total": 5,
      "page": 1,
      "limit": 3,
      "hasMore": true
    }
  }
}
*/