BEGIN TRANSACTION;
CREATE TABLE user (
	user_id INTEGER PRIMARY KEY AUTOINCREMENT,
	user_name TEXT NOT NULL,
	user_email TEXT NOT NULL UNIQUE,
    user_password TEXT NOT NULL,
    user_flag TEXT NOT NULL DEFAULT 'default'
);


CREATE TABLE collaborator (
    collaborator_id INTEGER PRIMARY KEY AUTOINCREMENT,
    collaborator_name TEXT NOT NULL
);

CREATE TABLE events (
    event_id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_name TEXT NOT NULL,
    event_date TEXT NOT NULL,
    event_passage_value TEXT NOT NULL,
    event_hotel_value TEXT NOT NULL,
    event_gift_value TEXT NOT NULL,
    event_team_value TEXT NOT NULL,
    event_sponsor_value TEXT NOT NULL,
    event_total_value TEXT NOT NULL
);

CREATE TABLE event_collaborators (
    event_id INTEGER NOT NULL,
    collaborator_id INTEGER NOT NULL,
    PRIMARY KEY (event_id, collaborator_id),
    FOREIGN KEY (event_id) REFERENCES events(event_id) ON DELETE CASCADE,
    FOREIGN KEY (collaborator_id) REFERENCES collaborator(collaborator_id) ON DELETE CASCADE
);
COMMIT;