Database schema and layout

[fr1t2]

Develop the final database layout to include all functions that we need to track.

Initial layout

Table Name	Columns	Table Use
Auth Providers	id, service_id, auth_type_id, client_id, client_secret, auth_url, token_url, scopes, created_at, updated_at	Stores authentication provider details.
Auth Types	id, name	Stores different types of authentication methods.
Currencies	code, name, symbol, decimals	Stores information about supported currencies.
Future Tips	id, tip_id, recipient_username, amount, currency_code, is_paid_out, created_at	Stores tips for users who haven't registered yet.
Guilds	id, guild_id, is_allowed, announcement_channel_id, mod_channel_id, command_prefix, features_enabled, mod_role_id, admin_role_id, bot_join_date, created_at, updated_at	Stores guilds (servers) data for Discord integration.
Guild Channels	id, guild_id, channel_id, channel_type	Stores different types of channels associated with a guild.
Guild User Roles	id, guild_id, user_id, role	Associates users with roles within a guild.
Nonces	id, user_id, web3_wallet_id, nonce, is_used, created_at, updated_at	Tracks nonces for Web3 signature verification.
Promotions	id, name, description, start_date, end_date	Manages promotional events and campaigns.
Services	id, name	Defines platforms or services users can connect to.
Tips	id, transaction_id, sender_user_id, message, created_at	Records each tip initiated by a user.
Tip Recipients	id, tip_id, recipient_user_id, recipient_username, amount	Associates multiple recipients with a tip.
Transactions	id, user_id, transaction_type_id, currency_code, amount, tx_hash, status, created_at, updated_at	Records all blockchain transactions.
Transaction Types	id, name, description	Stores different types of transactions.
Users	id, username, email, password_hash, created_at, updated_at	Stores unique user information.
Users Agree	id, user_id, has_agreed, agreed_at	Records user agreement to terms and conditions.
User Authentications	id, user_id, provider_id, provider_user_id, access_token, refresh_token, token_expiry, additional_data, created_at, updated_at	Associates users with their authentication methods.
User Promotions	id, user_id, promotion_id, is_redeemed, redemption_date	Tracks user participation in promotions.
Wallets	id, user_id, currency_code, public_address, balance, qr_code, is_retired, retired_at, created_at, updated_at	Stores wallet information for users.
Withdrawals	id, transaction_id, to_address	Logs user-initiated withdrawals to external addresses.
Web3 Wallets	id, user_id, blockchain, public_address, is_primary, created_at, updated_at	Stores Web3 addresses linked to users.
Web3 Verifications	id, user_id, web3_wallet_id, signature, message, verified, created_at, updated_at	Stores details of Web3 verifications.

[fr1t2]

Add user_emails table

CREATE TABLE user_emails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  email VARCHAR(255) NOT NULL,
  is_primary BOOLEAN DEFAULT FALSE,
  is_valid BOOLEAN DEFAULT TRUE,
  is_subscribed BOOLEAN DEFAULT FALSE, -- Opt-in for mailing lists
  validation_token VARCHAR(255), -- Token for email validation process
  validated_at DATETIME, -- Timestamp when the email was validated
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY (user_id, email) -- Ensures each user cannot have duplicate email addresses
);

[fr1t2]

Remove from users tab le email VARCHAR(255),