-- DROP FIELD Database Schema

-- Enable PostGIS extension for geospatial queries
CREATE EXTENSION IF NOT EXISTS postgis;

-- Users table
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255),
    total_points INTEGER DEFAULT 0 NOT NULL,
    rank INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_rank ON users(rank);
CREATE INDEX idx_users_total_points ON users(total_points DESC);

-- Drops table
CREATE TABLE drops (
    drop_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    dropper_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    level INTEGER NOT NULL CHECK (level >= 1 AND level <= 100),
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    lat DECIMAL(10, 8) NOT NULL,
    lng DECIMAL(11, 8) NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('hidden', 'available', 'picked', 'expired')),
    drop_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    available_at TIMESTAMP WITH TIME ZONE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    picker_id UUID REFERENCES users(user_id) ON DELETE SET NULL,
    picked_at TIMESTAMP WITH TIME ZONE,
    is_admin_drop BOOLEAN DEFAULT FALSE,
    rescue_points_granted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_drops_status ON drops(status);
CREATE INDEX idx_drops_dropper ON drops(dropper_id);
CREATE INDEX idx_drops_picker ON drops(picker_id);
CREATE INDEX idx_drops_available_at ON drops(available_at);
CREATE INDEX idx_drops_expires_at ON drops(expires_at);
CREATE INDEX idx_drops_location ON drops USING GIST(location);

-- User daily drops tracking table
CREATE TABLE user_daily_drops (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    drop_date DATE NOT NULL DEFAULT CURRENT_DATE,
    drop_count INTEGER DEFAULT 0,
    last_drop_at TIMESTAMP WITH TIME ZONE,
    UNIQUE(user_id, drop_date)
);

CREATE INDEX idx_user_daily_drops_user_date ON user_daily_drops(user_id, drop_date);

-- Pickup transactions table
CREATE TABLE pickups (
    pickup_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    drop_id UUID NOT NULL REFERENCES drops(drop_id) ON DELETE CASCADE,
    picker_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    skill_result VARCHAR(10) NOT NULL CHECK (skill_result IN ('success', 'fail')),
    base_points INTEGER 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 INTEGER NOT NULL,
    picker_points INTEGER NOT NULL,
    dropper_points INTEGER NOT NULL,
    picked_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_pickups_drop ON pickups(drop_id);
CREATE INDEX idx_pickups_picker ON pickups(picker_id);
CREATE INDEX idx_pickups_picked_at ON pickups(picked_at);

-- Rescue points table (for expired drops)
CREATE TABLE rescue_points (
    rescue_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    drop_id UUID NOT NULL REFERENCES drops(drop_id) ON DELETE CASCADE,
    dropper_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    rescue_points INTEGER NOT NULL,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_rescue_points_drop ON rescue_points(drop_id);
CREATE INDEX idx_rescue_points_dropper ON rescue_points(dropper_id);

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for updated_at
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_drops_updated_at BEFORE UPDATE ON drops
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function to calculate distance between two points (in meters)
CREATE OR REPLACE FUNCTION calculate_distance(
    lat1 DECIMAL,
    lng1 DECIMAL,
    lat2 DECIMAL,
    lng2 DECIMAL
) RETURNS DECIMAL AS $$
BEGIN
    RETURN ST_Distance(
        ST_MakePoint(lng1, lat1)::geography,
        ST_MakePoint(lng2, lat2)::geography
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;
