-- DROP FIELD Database Schema for MySQL 8.0

-- Users table
CREATE TABLE IF NOT EXISTS users (
    user_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255),
    total_points INT DEFAULT 0 NOT NULL,
    `rank` INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_rank (`rank`),
    INDEX idx_users_total_points (total_points DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Drops table
CREATE TABLE IF NOT EXISTS drops (
    drop_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    dropper_id CHAR(36) NOT NULL,
    level INT NOT NULL CHECK (level >= 1 AND level <= 100),
    location POINT NOT NULL SRID 4326,
    lat DECIMAL(10, 8) NOT NULL,
    lng DECIMAL(11, 8) NOT NULL,
    status ENUM('hidden', 'available', 'picked', 'expired') NOT NULL,
    drop_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    available_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    picker_id CHAR(36),
    picked_at TIMESTAMP NULL,
    is_admin_drop BOOLEAN DEFAULT FALSE,
    rescue_points_granted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (dropper_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (picker_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_drops_status (status),
    INDEX idx_drops_dropper (dropper_id),
    INDEX idx_drops_picker (picker_id),
    INDEX idx_drops_available_at (available_at),
    INDEX idx_drops_expires_at (expires_at),
    SPATIAL INDEX idx_drops_location (location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User daily drops tracking table
CREATE TABLE IF NOT EXISTS user_daily_drops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    drop_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    drop_count INT DEFAULT 0,
    last_drop_at TIMESTAMP NULL,
    UNIQUE KEY unique_user_date (user_id, drop_date),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_user_daily_drops_user_date (user_id, drop_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pickup transactions table
CREATE TABLE IF NOT EXISTS pickups (
    pickup_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    drop_id CHAR(36) NOT NULL,
    picker_id CHAR(36) NOT NULL,
    skill_result ENUM('success', 'fail') NOT NULL,
    base_points INT NOT NULL,
    level_multiplier DECIMAL(4, 2) NOT NULL,
    region_multiplier DECIMAL(4, 2) NOT NULL,
    density_multiplier DECIMAL(4, 2) NOT NULL,
    skill_multiplier DECIMAL(4, 2) NOT NULL,
    total_points INT NOT NULL,
    picker_points INT NOT NULL,
    dropper_points INT NOT NULL,
    picked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (drop_id) REFERENCES drops(drop_id) ON DELETE CASCADE,
    FOREIGN KEY (picker_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_pickups_drop (drop_id),
    INDEX idx_pickups_picker (picker_id),
    INDEX idx_pickups_picked_at (picked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rescue points table (for expired drops)
CREATE TABLE IF NOT EXISTS rescue_points (
    rescue_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    drop_id CHAR(36) NOT NULL,
    dropper_id CHAR(36) NOT NULL,
    rescue_points INT NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (drop_id) REFERENCES drops(drop_id) ON DELETE CASCADE,
    FOREIGN KEY (dropper_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_rescue_points_drop (drop_id),
    INDEX idx_rescue_points_dropper (dropper_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert demo user
INSERT INTO users (user_id, username, email) 
VALUES ('demo-user-123', 'DemoUser', 'demo@example.com')
ON DUPLICATE KEY UPDATE username = username;
