-- Create lottery tables
CREATE TABLE IF NOT EXISTS lottery_rounds (
  id INT PRIMARY KEY AUTO_INCREMENT,
  round_name VARCHAR(100) NOT NULL,
  ticket_price DECIMAL(10,2) NOT NULL,
  total_tickets INT NOT NULL,
  prize_amount DECIMAL(10,2) NOT NULL,
  total_winners INT DEFAULT 1,
  start_time TIMESTAMP NOT NULL,
  end_time TIMESTAMP NOT NULL,
  status ENUM('active', 'ended', 'drawing') DEFAULT 'active',
  winning_number VARCHAR(10),
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS lottery_tickets (
  id INT PRIMARY KEY AUTO_INCREMENT,
  round_id INT NOT NULL,
  player_id INT NOT NULL,
  ticket_number VARCHAR(10) NOT NULL UNIQUE,
  purchase_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  win_status TINYINT DEFAULT 0,
  prize_won DECIMAL(10,2) DEFAULT 0,
  FOREIGN KEY (round_id) REFERENCES lottery_rounds(id),
  FOREIGN KEY (player_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS lottery_winners (
  id INT PRIMARY KEY AUTO_INCREMENT,
  round_id INT NOT NULL,
  player_id INT NOT NULL,
  ticket_number VARCHAR(10) NOT NULL,
  prize_amount DECIMAL(10,2) NOT NULL,
  draw_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (round_id) REFERENCES lottery_rounds(id),
  FOREIGN KEY (player_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS lottery_settings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  is_enabled TINYINT DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO lottery_settings (is_enabled) VALUES (1) ON DUPLICATE KEY UPDATE id=id;
