-- Create Database
CREATE DATABASE IF NOT EXISTS earning_platform;
USE earning_platform;

-- Users Table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uid VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  phone VARCHAR(20),
  password VARCHAR(255) NOT NULL,
  profile_image VARCHAR(255),
  registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status ENUM('active', 'inactive') DEFAULT 'active'
);

-- Admin Settings Table
CREATE TABLE admin_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  logo_url VARCHAR(255),
  deposit_bank_name VARCHAR(255),
  deposit_account_number VARCHAR(255),
  deposit_account_holder VARCHAR(255),
  deposit_routing_number VARCHAR(255),
  deposit_method_text LONGTEXT,
  site_title VARCHAR(255),
  site_description LONGTEXT,
  currency_symbol VARCHAR(5) DEFAULT '$',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Deposit Table
CREATE TABLE deposits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  deposit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  approved_date DATETIME,
  notes TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Profit Settings Table (Admin sets profit percentage)
CREATE TABLE profit_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  deposit_amount DECIMAL(10, 2) NOT NULL,
  profit_percentage DECIMAL(5, 2) NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tasks Table
CREATE TABLE tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description LONGTEXT,
  task_type VARCHAR(100),
  position INT DEFAULT 0,
  status ENUM('active', 'inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User Task Completion Table
CREATE TABLE user_tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  task_id INT NOT NULL,
  completion_status ENUM('pending', 'completed', 'verified') DEFAULT 'pending',
  profit_earned DECIMAL(10, 2) DEFAULT 0,
  completed_date DATETIME,
  verified_date DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (task_id) REFERENCES tasks(id),
  UNIQUE KEY unique_user_task (user_id, task_id)
);

-- Notifications Table
CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  message LONGTEXT,
  type ENUM('info', 'success', 'warning', 'error') DEFAULT 'info',
  read_status ENUM('unread', 'read') DEFAULT 'unread',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Messages/Inbox Table
CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  subject VARCHAR(255),
  message LONGTEXT,
  read_status ENUM('unread', 'read') DEFAULT 'unread',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sender_id) REFERENCES users(id),
  FOREIGN KEY (receiver_id) REFERENCES users(id)
);

-- User Balance Table
CREATE TABLE user_balance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  total_deposit DECIMAL(10, 2) DEFAULT 0,
  total_profit DECIMAL(10, 2) DEFAULT 0,
  available_balance DECIMAL(10, 2) DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY unique_user (user_id)
);

-- Insert Default Admin Settings
INSERT INTO admin_settings (logo_url, deposit_bank_name, deposit_account_number, site_title, currency_symbol) 
VALUES ('https://via.placeholder.com/200x50?text=Your+Logo', 'Your Bank', '1234567890', 'Earning Platform', '$');

-- Insert Sample Profit Settings
INSERT INTO profit_settings (deposit_amount, profit_percentage) VALUES
(10, 5),
(50, 8),
(100, 10),
(500, 15),
(1000, 20);

-- Insert Sample Tasks
INSERT INTO tasks (title, description, task_type, position) VALUES
('Sign Up Completion', 'Complete your profile with all details', 'profile', 1),
('Email Verification', 'Verify your email address', 'verification', 2),
('Phone Verification', 'Verify your phone number', 'verification', 3),
('First Deposit', 'Make your first deposit', 'deposit', 4),
('Refer a Friend', 'Invite your friend and earn commission', 'referral', 5);

-- Create Admin User
INSERT INTO users (uid, name, email, phone, password) 
VALUES ('ADMIN001', 'Administrator', 'admin@example.com', '1234567890', MD5('admin123'));

-- Create indexes for better performance
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_uid ON users(uid);
CREATE INDEX idx_deposit_user ON deposits(user_id);
CREATE INDEX idx_task_user ON user_tasks(user_id);
CREATE INDEX idx_notification_user ON notifications(user_id);