-- Rockview University Clubs & Societies Campus Registry
-- PostgreSQL schema. The PHP endpoints also create these tables automatically.

CREATE TABLE IF NOT EXISTS campus_clubs (
    id BIGSERIAL PRIMARY KEY,
    created_by BIGINT NOT NULL,
    name VARCHAR(180) NOT NULL,
    acronym VARCHAR(40),
    category VARCHAR(100) NOT NULL DEFAULT 'Other',
    purpose TEXT NOT NULL DEFAULT '',
    description TEXT NOT NULL DEFAULT '',
    campus VARCHAR(180) NOT NULL,
    patron_name VARCHAR(180),
    contact_email VARCHAR(180),
    contact_phone VARCHAR(80),
    meeting_schedule VARCHAR(220),
    meeting_venue VARCHAR(220),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    rejection_reason TEXT,
    approved_by BIGINT,
    approved_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT campus_clubs_status_check CHECK (status IN ('pending','active','rejected','inactive'))
);

CREATE TABLE IF NOT EXISTS campus_club_members (
    id BIGSERIAL PRIMARY KEY,
    club_id BIGINT NOT NULL REFERENCES campus_clubs(id) ON DELETE CASCADE,
    added_by BIGINT NOT NULL,
    full_name VARCHAR(180) NOT NULL,
    member_number VARCHAR(100),
    member_type VARCHAR(80) NOT NULL DEFAULT 'Student',
    club_role VARCHAR(100) NOT NULL DEFAULT 'Member',
    email VARCHAR(180),
    phone VARCHAR(80),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    joined_at DATE NOT NULL DEFAULT CURRENT_DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT campus_club_members_status_check CHECK (status IN ('active','inactive'))
);

CREATE TABLE IF NOT EXISTS campus_club_documents (
    id BIGSERIAL PRIMARY KEY,
    club_id BIGINT NOT NULL REFERENCES campus_clubs(id) ON DELETE CASCADE,
    uploaded_by BIGINT NOT NULL,
    document_type VARCHAR(100) NOT NULL DEFAULT 'Club Document',
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    file_path TEXT NOT NULL,
    mime_type VARCHAR(160) NOT NULL,
    file_size BIGINT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_campus_clubs_campus_status ON campus_clubs (campus, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_campus_clubs_created_by ON campus_clubs (created_by, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_campus_clubs_category ON campus_clubs (category);
CREATE INDEX IF NOT EXISTS idx_campus_club_members_club ON campus_club_members (club_id, status, full_name);
CREATE INDEX IF NOT EXISTS idx_campus_club_documents_club ON campus_club_documents (club_id, created_at DESC);
