Untitled

CREATE TABLE program_v1 (
    id SERIAL PRIMARY KEY,                       -- Auto-incrementing primary key
    program VARCHAR(255) NOT NULL,              -- Program identifier or code
    program_type_id INT NOT NULL,               -- Foreign key referencing program_type_v1
    name VARCHAR(255) NOT NULL,                 -- Name of the program
    description TEXT,                           -- Description of the program
    start_date DATE,                            -- Start date of the program
    start_time TIME,                            -- Start time of the program
    end_date DATE,                              -- End date of the program
    end_time TIME,                              -- End time of the program
    no_of_session INT,                          -- Number of sessions in the program
    registration_start_date DATE,               -- Registration start date
    registration_start_time TIME,               -- Registration start time
    registration_end_date DATE,                 -- Registration end date
    registration_end_time TIME,                 -- Registration end time
    meta JSON,                                  -- Additional metadata (stored in JSON format)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the record is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Timestamp when the record is updated
);

ALTER TABLE program_v1 
ADD CONSTRAINT fk_program_type 
FOREIGN KEY (program_type_id) REFERENCES program_type_v1 (id);

-- Adding indexes
CREATE INDEX idx_program_type_id ON program_v1 (program_type_id);
CREATE INDEX idx_start_date ON program_v1 (start_date);
CREATE INDEX idx_registration_start_date ON program_v1 (registration_start_date);
CREATE INDEX idx_registration_end_date ON program_v1 (registration_end_date);


CREATE TABLE program_session (
    id SERIAL PRIMARY KEY,                       -- Auto-incrementing primary key (automatically indexed)
    program_id INT NOT NULL,                     -- Foreign key referencing program_v1
    name VARCHAR(255) NOT NULL,                 -- Name of the session
    description TEXT,                            -- Description of the session
    start_date DATE,                             -- Start date of the session
    start_time TIME,                             -- Start time of the session
    end_date DATE,                               -- End date of the session
    end_time TIME,                               -- End time of the session
    registration_start_date DATE,                -- Registration start date for the session
    registration_start_time TIME,                -- Registration start time for the session
    registration_end_date DATE,                  -- Registration end date for the session
    registration_end_time TIME,                  -- Registration end time for the session
    meta JSON,                                   -- Additional metadata (stored in JSON format)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the record is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Timestamp when the record is updated
);

-- Adding foreign key constraint for program_id
ALTER TABLE program_session 
ADD CONSTRAINT fk_program 
FOREIGN KEY (program_id) REFERENCES program_v1 (id);

-- Adding explicit index on id (redundant due to PRIMARY KEY but included for clarity)
CREATE INDEX idx_id ON program_session (id);

CREATE TABLE webinar (
    id SERIAL PRIMARY KEY,                        -- Auto-incrementing primary key (automatically indexed)
    title VARCHAR(255) NOT NULL,                 -- Title of the meeting
    program_session_id INT NOT NULL,             -- Foreign key referencing program_session
    webinar_id_ext VARCHAR(255),                 -- External meeting ID (e.g., Zoom/Teams ID)
    password VARCHAR(255),                       -- Meeting password
    start_date DATE,                              -- Start date of the meeting
    start_url TEXT,                               -- URL to start the meeting
    duration INT,                                 -- Duration of the meeting in minutes
    end_date DATE,                                -- End date of the meeting
    started_at TIMESTAMP,                         -- Timestamp when the meeting started
    started_by VARCHAR(255),                     -- User who started the meeting
    created_by VARCHAR(255),                     -- User who created the meeting
    registration_starts_at TIMESTAMP,            -- Registration start timestamp
    registration_ends_at TIMESTAMP,              -- Registration end timestamp
    meta JSON,                                    -- Additional metadata (stored in JSON format)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the record is created
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Timestamp when the record is updated
);

-- Adding foreign key constraint for program_session_id
ALTER TABLE webinar 
ADD CONSTRAINT fk_program_session 
FOREIGN KEY (program_session_id) REFERENCES program_session (id);

-- Adding explicit index on id (redundant due to PRIMARY KEY but included for clarity)
CREATE INDEX idx_id ON webinar (id);


INSERT INTO program_type_v1 (program_type, name, description)
VALUES ('HDB', 'Hyper Deeper Beyond', 'A personality development program');

INSERT INTO program_v1 (program, program_type_id, name, description, start_date, start_time, end_date, end_time, no_of_session, registration_start_date, registration_start_time, registration_end_date, registration_end_time, meta)
VALUES (
    'P001', 
    1, 
    'HDB Program', 
    'An introductory program for personal development', 
    '2024-12-20', 
    '09:00:00', 
    '2024-12-21', 
    '17:00:00', 
    2, 
    '2024-12-10', 
    '08:00:00', 
    '2024-12-19', 
    '20:00:00', 
    '{"difficulty": "beginner"}'
);

INSERT INTO program_session (program_id, name, description, start_date, start_time, end_date, end_time, registration_start_date, registration_start_time, registration_end_date, registration_end_time, meta)
VALUES (
    1, 
    'Session 1: Introduction', 
    'First session focusing on fundamentals of personal development', 
    '2024-12-20', 
    '09:00:00', 
    '2024-12-20', 
    '12:00:00', 
    '2024-12-10', 
    '08:00:00', 
    '2024-12-19', 
    '20:00:00', 
    '{"focus_area": "self-awareness"}'
);

select * from users

select * from webinar

alter table webinar add column deleted_at date

alter table webinar add column max_video_limit integer 

alter table webinar add column max_non_video_limit integer

alter table webinar rename column started_at to start_at

select * from public.infini_path_meeting_attendee



CREATE TABLE webinar_attendee (
    id SERIAL PRIMARY KEY,
    webinar_id INT NOT NULL,
    user_id INT NOT NULL,
    related_user_id INT,
    mode_of_joining VARCHAR(50),
    face_verification_status BOOLEAN DEFAULT FALSE,
    attendance_time TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Foreign key constraints
    CONSTRAINT fk_webinar_attendee_webinar
        FOREIGN KEY (webinar_id) REFERENCES webinar(id) ON DELETE CASCADE,
    CONSTRAINT fk_webinar_attendee_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_webinar_attendee_related_user
        FOREIGN KEY (related_user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Indexes for improved query performance
CREATE INDEX idx_webinar_attendee_webinar_id ON webinar_attendee (webinar_id);
CREATE INDEX idx_webinar_attendee_user_id ON webinar_attendee (user_id);
CREATE INDEX idx_webinar_attendee_related_user_id ON webinar_attendee (related_user_id);
CREATE INDEX idx_webinar_attendee_mode_of_joining ON webinar_attendee (mode_of_joining);


select * from public.webinar_attendee

select * from public.infini_path_user_meeting



CREATE TABLE webinar_registration (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    webinar_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    join_url VARCHAR(255),
    is_panelist BOOLEAN DEFAULT FALSE,
    registration_id_ext VARCHAR(255),

    -- Foreign key constraints
    CONSTRAINT fk_webinar_registration_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_webinar_registration_webinar
        FOREIGN KEY (webinar_id) REFERENCES webinar(id) ON DELETE CASCADE
);

-- Indexes for optimization
CREATE INDEX idx_webinar_registration_user_id ON webinar_registration (user_id);
CREATE INDEX idx_webinar_registration_webinar_id ON webinar_registration (webinar_id);
CREATE INDEX idx_webinar_registration_registration_id_ext ON webinar_registration (registration_id_ext);


select * from public.webinar_registration