-- progDB.sql
-- Draft schema for Arisia programming database project.
-- This all kinds of pre-alpha code, kids.
-- It *will not* slide through an SQL parser, don't even try.
--
-- Schema Authors: Jack Dietz, Peter Olszowka, Tracy Gangwer(?).
-- First SQL rendering: Hacksaw (aka HS) 6/4/2005
-- 
-- Search on XXX to find marked bugs or questions
--

-- #################################################################
CREATE TABLE sessions {
    sessionid serial,
    name text,
    divisionid integer, -- divisions.divisionid
    trackid integer, -- tracks.trackid
    pubsid text, -- pubsid is text in case they want an alphanumeric
                 -- identifier (possibly combining track name and session number)

    shortdescription text, -- Short description for Pocket Program
    longdescription text, -- Long description for the participant mailings and for
                          -- participant reference.  Can include references, URLs, etc.
    duration interval,
    attendees integer, -- Estimated number of attendees -- for room sizing purposes.
    proposer text, -- contact info for proposer, for clarification and recruitment.
    state integer, -- sessionstates.sessionstateid
                   -- At what step in the brainstorming process are we?
    roomset integer, -- roomsetnames.roomsetnamesid
                     -- How is the room to be set up
    signup boolean, -- 'Yes' if signup beforehand is required.
    kidstatusid integer, -- kidstatus.kidstatusid
    notes text, -- Any other notes on this session.
    warningsp boolean, -- XXX What is this for? --HS
    warnings text, -- Probably list of integers.
                   -- Perhaps this could be another table, mapping
                   -- sessions to warning IDs?
                   -- XXX an array? --HS
};

-- #################################################################
-- sessionhasproperty: Generalized property feature for sessions; can
-- contain tech details (equipment requirements), resume details
-- (food), film details (run time, language, rating), etc.
CREATE TABLE sessionhasproperty {
    sessionid integer,
    sessionpropertyid integer, 
    value text
};

-- #################################################################
CREATE TABLE sessionproperties {
    sessionpropertyid serial,
    legend text
};

-- #################################################################
-- divisions: Programming, Events, Films to start
CREATE TABLE divisions {
    divisionid serial,
    name text
};

-- #################################################################
-- List of all tracks.  Possible special track 'dummy' for putting all
-- fake sessions used to enforce times rooms are unavailable (set
-- changes, etc).
CREATE TABLE tracks {
    trackid serial,
    legend text
};

-- #################################################################
-- sessionstates: brainstorm, winnowed, selected by participants, in schedule, dead
CREATE TABLE sessionstates {
    sessionstateid serial,
    legend text
};

-- #################################################################
-- roomsetnames: panel, theater, roundtable, etc
CREATE TABLE roomsetnames {
    roomsetnameid serial,
    legend text
    -- XXX maybe also 'description text', so we have a canonical place
    -- to find what we mean when we say roundtable, etc.
};

-- #################################################################
-- kidstatus: an enumeration of relationship between the session and kids.
-- XXX What constitutes a kid? Should this table be directly
-- populated by the schema? --HS

-- Kids targeted
-- Kids welcome
-- Kids ok only with parent
-- No Kids at all
CREATE TABLE kidstatus {
    kidstatusid serial,
    legend text
};

-- #################################################################
-- Warnings set on sessions, participants and rooms are given serial numbers here.
-- What are these for? --HS
CREATE TABLE warnings {
    warningid serial,
    legend text
};

-- #################################################################
CREATE TABLE participants {
    partid integer, -- same as fanid
    moniker text, -- This field is initialized with the Badge Name from CONGO.
                  -- We can edit it if it is ambiguous.  We do not plan to ever
                  -- update these values from CONGO nor will we push updated
                  -- values back into CONGO.
    bio text,
    interest integer, -- interested.interestid
                      -- "confirmed" "unavailable" "interested" "contacted" "tentative"
    updated timestamptz, 
    responsestate integer, -- responses.responseid
    conflicts text, -- Any specific time conflicts this person has.
    familymembers text, -- Details about family members (spice, kids, etc)
    specialneeds text, -- Any special needs for this participant:
                       -- extra time between panels, food requirements, etc.
    notes text, -- Other notes relevant to the participant.
    warningsp boolean, -- XXX What is this for?
    warnings text, -- XXX What is this for?
};

-- #################################################################
-- questionnaire: This table captures the brainstorming ideas from the
-- first participant mailing,  all free-form text boxes for hints
-- about how to schedule this specific person.
CREATE TABLE questionnaire { 
    partid integer,
    sessiontopicideas text,
    sessionideas text,
    sessioninterests text,
    sessiondisinterests text,
    participantinterests text,
    participantdisinterests text,
    roleinterests text
};

-- #################################################################
-- dailysessions: Maximum sessions a participant is interested in per
-- day.  Making this a separate table allows convention lengths to
-- change.
CREATE TABLE dailysessions {
    partid integer, -- participants.partid
    day timestamptz OR days.dayid, -- XXX If we make a table of 'days' for other
                                  -- purposes (say '1' is 'Friday', '2' is 'Saturday', '3'                                  -- is 'Sunday') use that instead.
    sessions integer
}

-- #################################################################
-- interested: General willingness to participate in the convention.
-- "unknown" "not interested" "interested" "contacted" "will participate"
CREATE TABLE interested  {
    interestid serial,
    legend text
}

-- #################################################################
-- Responses to our participant mailings.
-- "none" "first-email" "first-web" "second-email" "second-web"
CREATE TABLE responses {
    responseid serial,
    legend text
}

-- #################################################################
CREATE TABLE rooms {
    roomid serial,
    name text,
    locationid integer, -- locations.locationid
    area integer,
    notes text,
    warningsp boolean, -- XXX What is this?
    warnings text
}

-- #################################################################
-- Terrace, Lobby, Mezzanine, 3rd, 4th, etc.
CREATE TABLE locations {
    locationid serial,
    legend text
}

-- #################################################################
-- This table describes the number of seats in each room in each
-- available room set.  If there is not a row matching a specific room
-- to a specific set, that set is not available in that room.
CREATE TABLE roomhasset {
    roomid integer,
    roomsetnameid integer, -- roomsetnames.roomsetnameid
    seats integer
}

-- #################################################################
-- Used as a general expansion for room properties: tech capacity
-- (built-in a/v, electrical power, windows that open, etc)
CREATE TABLE roomhasproperty {
    roomid integer,
    roompropertyid integer,
    value text
}

-- #################################################################
-- roomproperties: a list of properties a room might have.
CREATE TABLE roomproperties {
    roompropertyid serial,
    legend text
}

-- #################################################################
-- This is the mapping between programs, rooms and times.
CREATE TABLE sessionblocking {
    sessionid integer,
    roomid integer,
    starttime timestamptz
}

-- #################################################################
-- This is the actual assignments of people to panels, plus the "moderator" bit.
CREATE TABLE participantblocking {
    sessionid integer,
    partid integer,
    scheduled boolean,
    moderator boolean,
    interest integer, -- Either a 1-5 scale, or the rank they
                      -- placed that panel in when they signed up.
    notes text
}

-- #################################################################
-- The Contact Log is intended to track each communication with a
-- program participant, with a note (or pasted email) with the content of
-- the communication.
--
-- If you update the Status on the Participant record, you should
-- leave a Contact Log entry.  (Enforce with web interface?)
CREATE TABLE participantcontacts {
    partid integer,
    timestamp timestamptz,
    staffer text OR integer, -- XXX (badge number?)
    -- If we want to cross-reference this or auto-generate email, another
    -- table for Arisia staff (perhaps indexed by badgenumber) would be
    -- good.  Otherwise, just a text field for the name of the staff
    -- member.

    logmessage text
}

-- #################################################################
-- participantavailable: This would be each time window that presenter
-- specifies, at least one per day. Saturday, 11am to 2pm, for
-- instance. This way we can keep arbitrary windows, rather than a
-- fixed number per program participant.
CREATE TABLE participantavailable {
    partid integer,
    starttime timestamptz,
    endtime timestamptz,
    notes text -- Reason for the availability window.  (Preferred for
               -- constraints for just this window to be described
               -- here; general constraints (like diabetic, need
               -- break after every session, etc) to be on the
               -- participant's record.)

    -- XXX Do we even ask for reasons for their availability? Should we?
    -- Would that be rude? --HS
}

-- #################################################################
-- roomavailable: These are the times a given room is available.
CREATE TABLE roomavailable {
    roomid integer,
    starttime timestamptz,
    endtime timestamptz,
    notes text
}