-- Migration 040: Antrag auf Vereinsbeitritt (pending → accept/reject durch Vereins-/Plattform-Admin)

CREATE TABLE IF NOT EXISTS club_membership_requests (
    id SERIAL PRIMARY KEY,
    profile_id INT NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    club_id INT NOT NULL REFERENCES clubs(id) ON DELETE CASCADE,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'accepted', 'rejected', 'withdrawn')),
    message TEXT,
    decided_by_profile_id INT REFERENCES profiles(id) ON DELETE SET NULL,
    decided_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_club_membership_requests_pending
    ON club_membership_requests (profile_id, club_id)
    WHERE status = 'pending';

CREATE INDEX IF NOT EXISTS idx_club_membership_requests_club_status
    ON club_membership_requests (club_id, status);

CREATE INDEX IF NOT EXISTS idx_club_membership_requests_profile
    ON club_membership_requests (profile_id);

DROP TRIGGER IF EXISTS club_membership_requests_update ON club_membership_requests;
CREATE TRIGGER club_membership_requests_update
    BEFORE UPDATE ON club_membership_requests
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();