-- PostgreSQL Schema for ZIPA Summit 2026
-- Generated from Laravel Migrations
-- No Foreign Keys / Relations

-- 1. Users
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    companyid VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    username VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP,
    password VARCHAR(255) UNIQUE,
    role VARCHAR(255) NOT NULL,
    status VARCHAR(255) DEFAULT 'active' NOT NULL,
    address VARCHAR(255),
    remember_token VARCHAR(100),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 2. Password Reset Tokens
CREATE TABLE password_reset_tokens (
    email VARCHAR(255) PRIMARY KEY,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP
);

-- 3. Failed Jobs
CREATE TABLE failed_jobs (
    id BIGSERIAL PRIMARY KEY,
    uuid VARCHAR(255) NOT NULL UNIQUE,
    connection TEXT NOT NULL,
    queue TEXT NOT NULL,
    payload TEXT NOT NULL,
    exception TEXT NOT NULL,
    failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- 4. Personal Access Tokens
CREATE TABLE personal_access_tokens (
    id BIGSERIAL PRIMARY KEY,
    tokenable_type VARCHAR(255) NOT NULL,
    tokenable_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    abilities TEXT,
    last_used_at TIMESTAMP,
    expires_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 5. Roles
CREATE TABLE roles (
    id BIGSERIAL PRIMARY KEY,
    role_name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 6. User Roles
CREATE TABLE user_roles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL
);

-- 7. Sessions
CREATE TABLE sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id BIGINT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    payload TEXT NOT NULL,
    last_activity INTEGER NOT NULL
);

-- 8. Permissions
CREATE TABLE permissions (
    id BIGSERIAL PRIMARY KEY,
    permission_name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 9. Permission Roles
CREATE TABLE permission_roles (
    id BIGSERIAL PRIMARY KEY,
    permission_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL
);

-- 10. Notifications
CREATE TABLE notifications (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 11. Venues
CREATE TABLE venues (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location TEXT NOT NULL,
    capacity INTEGER,
    description TEXT,
    photo_url VARCHAR(255),
    createdby BIGINT,
    modifiedby BIGINT,
    status BIGINT,
    company_id BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 12. Events
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    institution_name VARCHAR(255) NOT NULL,
    logo_url VARCHAR(255),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    photo_url VARCHAR(255),
    control_number VARCHAR(255),
    venue_id BIGINT,
    status BIGINT,
    createdby BIGINT,
    modifiedby BIGINT,
    company_id BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 13. Guests
CREATE TABLE guests (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    title VARCHAR(255),
    role VARCHAR(255) NOT NULL,
    bio TEXT,
    photo_url VARCHAR(255),
    venue_id BIGINT,
    createdby BIGINT,
    modifiedby BIGINT,
    company_id BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 14. Event Sessions
CREATE TABLE eventsessions (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    quest_id BIGINT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    start_time TIME,
    end_time TIME,
    createdby BIGINT,
    modifiedby BIGINT,
    company_id BIGINT,
    status BIGINT,
    location VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 15. Facilities
CREATE TABLE facilities (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    venue_id BIGINT NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 16. Event Agendas (Typo `eventagenders` preserved from migration)
CREATE TABLE eventagenders (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    agenda TEXT NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 17. Event Speakers
CREATE TABLE eventspeakers (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(255) NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 18. Event Registrations
CREATE TABLE eventregistrations (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT NOT NULL,
    type VARCHAR(255) NOT NULL,
    paymentoptions VARCHAR(255) NOT NULL,
    type_amount VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 19. User Event Registrations
CREATE TABLE usereventregistrations (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT,
    fullname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    organization VARCHAR(255),
    networktype VARCHAR(255),
    jobtitle VARCHAR(255),
    package_category VARCHAR(255),
    package_price DOUBLE PRECISION,
    extra_commitment DOUBLE PRECISION,
    nature_business VARCHAR(255),
    organization_id BIGINT,
    sector_id BIGINT,
    phone VARCHAR(255),
    type_id BIGINT,
    position VARCHAR(255),
    country VARCHAR(255),
    home_country VARCHAR(255),
    target_company VARCHAR(255),
    number_of_participants VARCHAR(255),
    agender VARCHAR(255),
    participant_type VARCHAR(255),
    passport_photo VARCHAR(255),
    passport_id VARCHAR(255),
    controlnumber VARCHAR(255),
    badge_url VARCHAR(255),
    createdby BIGINT,
    modifiedby BIGINT,
    approvestatus BIGINT,
    status BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 20. Organizations
CREATE TABLE organizations (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    business_model VARCHAR(255) NOT NULL,
    createdby BIGINT,
    modifiedby BIGINT,
    status VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 21. Session Participants
CREATE TABLE sessionparticipants (
    id BIGSERIAL PRIMARY KEY,
    eventsession_id BIGINT NOT NULL,
    participant VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 22. User Participants
CREATE TABLE userparticipants (
    id BIGSERIAL PRIMARY KEY,
    registration_id BIGINT NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    photo_path VARCHAR(255) NOT NULL,
    qr_code_path VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 23. Sectors
CREATE TABLE sectors (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    status BOOLEAN DEFAULT true NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 24. Packages
CREATE TABLE packages (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    features JSON NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 25. Networking Requests
CREATE TABLE networking_requests (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    company VARCHAR(255) NOT NULL,
    job_title VARCHAR(255) NOT NULL,
    nature_of_business VARCHAR(255) NOT NULL,
    participants_count INTEGER,
    sector VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    nationality VARCHAR(255) NOT NULL,
    meeting_type JSON,
    preferred_date DATE NOT NULL,
    target_companies JSON,
    target_govt_entities JSON,
    purpose TEXT NOT NULL,
    status VARCHAR(255) DEFAULT 'pending' NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 26. Agendas
CREATE TABLE agendas (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT,
    day_number INTEGER NOT NULL,
    date VARCHAR(255) NOT NULL,
    start_times TEXT NOT NULL,
    end_times TEXT NOT NULL,
    topics TEXT NOT NULL,
    created_by BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 27. Side Events
CREATE TABLE side_events (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT,
    event_date VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    theme TEXT NOT NULL,
    created_by BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 28. Tournaments
CREATE TABLE tournaments (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    image_url VARCHAR(255),
    created_by BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 29. Media Houses
CREATE TABLE media_houses (
    id BIGSERIAL PRIMARY KEY,
    organization_name VARCHAR(255) NOT NULL,
    website_url VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 30. Media Registrations
CREATE TABLE media_registrations (
    id BIGSERIAL PRIMARY KEY,
    usereventregistration_id BIGINT NOT NULL,
    media_house_id BIGINT NOT NULL,
    press_id_number VARCHAR(255),
    dietary_accessibility TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 31. Media Social Links
CREATE TABLE media_social_links (
    id BIGSERIAL PRIMARY KEY,
    media_registration_id BIGINT NOT NULL,
    portfolio_url TEXT NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 32. Media Coverage
CREATE TABLE media_coverage (
    id BIGSERIAL PRIMARY KEY,
    media_registration_id BIGINT NOT NULL,
    media_type VARCHAR(255) NOT NULL,
    news_segment BOOLEAN DEFAULT false,
    interview_request BOOLEAN DEFAULT false,
    live_stream BOOLEAN DEFAULT false,
    social_media BOOLEAN DEFAULT false,
    assignment_letter VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 33. Media Attendance Days
CREATE TABLE media_attendance_days (
    id BIGSERIAL PRIMARY KEY,
    media_registration_id BIGINT NOT NULL,
    day VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- 34. Media Terms Acceptance
CREATE TABLE media_terms_acceptance (
    id BIGSERIAL PRIMARY KEY,
    media_registration_id BIGINT NOT NULL,
    accepted BOOLEAN DEFAULT false,
    accepted_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);