-- Rich Animals Game Bot Database Structure

CREATE DATABASE IF NOT EXISTS rich_animals_bot;
USE rich_animals_bot;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    telegram_id BIGINT PRIMARY KEY,
    username VARCHAR(255),
    balance DECIMAL(10,2) DEFAULT 0,
    income DECIMAL(10,2) DEFAULT 0,
    total_clicks BIGINT DEFAULT 0,
    referral_id BIGINT NULL,
    referral_count INT DEFAULT 0,
    last_click TIMESTAMP,
    last_claim TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Animals table
CREATE TABLE IF NOT EXISTS animals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    cost DECIMAL(10,2) NOT NULL,
    income_per_hour DECIMAL(10,2) NOT NULL,
    image_url VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- User Animals table
CREATE TABLE IF NOT EXISTS user_animals (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    animal_id INT NOT NULL,
    quantity INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(telegram_id) ON DELETE CASCADE,
    FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Referrals table
CREATE TABLE IF NOT EXISTS referrals (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    referrer_id BIGINT NOT NULL,
    referred_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(telegram_id),
    FOREIGN KEY (referred_id) REFERENCES users(telegram_id)
) ENGINE=InnoDB;

-- Withdrawals table
CREATE TABLE IF NOT EXISTS withdrawals (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    payment_address VARCHAR(255) NOT NULL,
    status ENUM('pending', 'completed', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(telegram_id)
) ENGINE=InnoDB;

-- Indexes
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_referral_id ON users(referral_id);
CREATE INDEX idx_last_click ON users(last_click);
CREATE INDEX idx_last_claim ON users(last_claim);
CREATE INDEX idx_user_animals_user ON user_animals(user_id);
CREATE INDEX idx_user_animals_animal ON user_animals(animal_id);
CREATE INDEX idx_referrals_referrer ON referrals(referrer_id);
CREATE INDEX idx_referrals_referred ON referrals(referred_id);
CREATE INDEX idx_withdrawals_user ON withdrawals(user_id);
CREATE INDEX idx_withdrawals_status ON withdrawals(status);

-- Sample data
-- Animals
INSERT INTO animals (name, cost, income_per_hour, image_url, description) VALUES
('Chicken', 100, 10, 'https://example.com/chicken.png', 'Basic animal that generates small income'),
('Cow', 1000, 100, 'https://example.com/cow.png', 'Medium income animal'),
('Elephant', 10000, 1000, 'https://example.com/elephant.png', 'High income animal');

-- Stored procedures
-- Daily rewards
DELIMITER //
CREATE PROCEDURE update_daily_rewards()
BEGIN
    UPDATE users 
    SET balance = balance + 100, 
        last_claim = CURRENT_TIMESTAMP
    WHERE last_claim < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY);
END //

-- Hourly income calculation
CREATE PROCEDURE update_hourly_income()
BEGIN
    UPDATE users u
    JOIN (
        SELECT ua.user_id, SUM(ua.quantity * a.income_per_hour) as total_income
        FROM user_animals ua
        JOIN animals a ON ua.animal_id = a.id
        GROUP BY ua.user_id
    ) sub ON u.telegram_id = sub.user_id
    SET u.income = sub.total_income;
END //
DELIMITER ;
