-- Casino System Database Schema
-- Drop existing tables if they exist

-- Users table (Admin, Agent, Player)
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100),
  phone VARCHAR(20) UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('player', 'agent', 'admin') DEFAULT 'player',
  balance DECIMAL(10,2) DEFAULT 0.00,
  status ENUM('active', 'suspended', 'blocked') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Agent Float Balance Management
CREATE TABLE agent_float_balance (
  id INT PRIMARY KEY AUTO_INCREMENT,
  agent_id INT NOT NULL,
  float_balance DECIMAL(10,2) DEFAULT 0.00,
  initial_float DECIMAL(10,2) DEFAULT 0.00,
  total_deposits_handled DECIMAL(10,2) DEFAULT 0.00,
  total_withdrawals_handled DECIMAL(10,2) DEFAULT 0.00,
  commission_earned DECIMAL(10,2) DEFAULT 0.00,
  house_edge_owed DECIMAL(10,2) DEFAULT 0.00,
  is_active BOOLEAN DEFAULT TRUE,
  last_settlement_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_agent_active (agent_id, is_active)
);

-- Deposits table
CREATE TABLE deposits (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tracking_id VARCHAR(50) UNIQUE NOT NULL,
  player_id INT NOT NULL,
  agent_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_method ENUM('bkash', 'nagad', 'rocket') NOT NULL,
  agent_number VARCHAR(20),
  cashout_number VARCHAR(20),
  trx_id VARCHAR(50),
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  commission_amount DECIMAL(10,2) DEFAULT 0.00,
  commission_rate DECIMAL(5,2) DEFAULT 5.00,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (player_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_status (status),
  INDEX idx_agent_date (agent_id, created_at)
);

-- Withdrawals table
CREATE TABLE withdrawals (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tracking_id VARCHAR(50) UNIQUE NOT NULL,
  player_id INT NOT NULL,
  agent_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_method ENUM('bkash', 'nagad', 'rocket') NOT NULL,
  player_number VARCHAR(20) NOT NULL,
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  commission_amount DECIMAL(10,2) DEFAULT 0.00,
  commission_rate DECIMAL(5,2) DEFAULT 2.00,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (player_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_status (status),
  INDEX idx_agent_date (agent_id, created_at)
);

-- Commission Tracking
CREATE TABLE commission_tracking (
  id INT PRIMARY KEY AUTO_INCREMENT,
  agent_id INT NOT NULL,
  transaction_type ENUM('deposit', 'withdrawal') NOT NULL,
  transaction_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  commission_rate DECIMAL(5,2) NOT NULL,
  commission_amount DECIMAL(10,2) NOT NULL,
  date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_agent_date (agent_id, date)
);

-- House Edge Tracking
CREATE TABLE house_edge_tracking (
  id INT PRIMARY KEY AUTO_INCREMENT,
  player_id INT NOT NULL,
  agent_id INT NOT NULL,
  game_type VARCHAR(50),
  bet_amount DECIMAL(10,2) NOT NULL,
  player_loss DECIMAL(10,2) NOT NULL,
  house_edge_rate DECIMAL(5,2) DEFAULT 5.00,
  house_edge_amount DECIMAL(10,2) NOT NULL,
  date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (player_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_agent_date (agent_id, date),
  INDEX idx_player_date (player_id, date)
);

-- Daily Settlements
CREATE TABLE settlements (
  id INT PRIMARY KEY AUTO_INCREMENT,
  agent_id INT NOT NULL,
  settlement_date DATE NOT NULL,
  initial_float DECIMAL(10,2) NOT NULL,
  total_deposits DECIMAL(10,2) NOT NULL,
  total_withdrawals DECIMAL(10,2) NOT NULL,
  commission_earned DECIMAL(10,2) NOT NULL,
  house_edge_owed DECIMAL(10,2) NOT NULL,
  remaining_float DECIMAL(10,2) NOT NULL,
  net_settlement DECIMAL(10,2) NOT NULL,
  status ENUM('pending', 'completed') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_agent_date (agent_id, settlement_date),
  INDEX idx_date (settlement_date)
);

-- Transaction Logs
CREATE TABLE transaction_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  transaction_type ENUM('deposit', 'withdrawal', 'game_win', 'game_loss', 'commission', 'house_edge', 'float_topup', 'settlement') NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  balance_before DECIMAL(10,2) NOT NULL,
  balance_after DECIMAL(10,2) NOT NULL,
  reference_id VARCHAR(50),
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_type_date (user_id, transaction_type, created_at)
);

-- Notifications
CREATE TABLE notifications (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  type ENUM('info', 'success', 'warning', 'error') DEFAULT 'info',
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  data JSON,
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_unread (user_id, is_read, created_at)
);

-- Insert default admin user
INSERT INTO users (username, email, phone, password, role, balance) VALUES
('admin_joybangla', 'admin@joybangla.com', '01780684828', '$2b$10$tfgEpZpHCc1IAhJGZqKvJOxK8qK8qK8qK8qK8qK8qK8qK8qK8qK8qK', 'admin', 0.00);

-- Insert demo agents
INSERT INTO users (username, email, phone, password, role, balance) VALUES
('agent_dhaka', 'agent1@joybangla.com', '01787203830', '$2b$10$tfgEpZpHCc1IAhJGZqKvJOxK8qK8qK8qK8qK8qK8qK8qK8qK8qK8qK', 'agent', 0.00),
('agent_chittagong', 'agent2@joybangla.com', '01850001643', '$2b$10$tfgEpZpHCc1IAhJGZqKvJOxK8qK8qK8qK8qK8qK8qK8qK8qK8qK8qK', 'agent', 0.00);

-- Insert demo player
INSERT INTO users (username, email, phone, password, role, balance) VALUES
('player_demo', 'player@joybangla.com', '01747382749', '$2b$10$tfgEpZpHCc1IAhJGZqKvJOxK8qK8qK8qK8qK8qK8qK8qK8qK8qK8qK', 'player', 5000.00);

-- Initialize agent float balances
INSERT INTO agent_float_balance (agent_id, float_balance, initial_float, is_active) 
SELECT id, 50000.00, 50000.00, TRUE FROM users WHERE role = 'agent';
