CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(150) UNIQUE,
    password VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150),
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE group_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT,
    user_id INT,
    role ENUM('admin','member') DEFAULT 'member',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(group_id, user_id)
);

CREATE TABLE group_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT,
    user_id INT,
    text TEXT,
    image_url VARCHAR(255),
    video_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE group_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT,
    sender_id INT,
    message TEXT,
    media_url VARCHAR(255),
    media_type ENUM('text','image','video'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE group_posts CHANGE text content TEXT;

ALTER TABLE groups
ADD CONSTRAINT fk_groups_creator
FOREIGN KEY (created_by) REFERENCES users(id)
ON DELETE CASCADE;

ALTER TABLE group_members
ADD CONSTRAINT fk_gm_group
FOREIGN KEY (group_id) REFERENCES groups(id)
ON DELETE CASCADE,
ADD CONSTRAINT fk_gm_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

ALTER TABLE group_posts
ADD CONSTRAINT fk_gp_group
FOREIGN KEY (group_id) REFERENCES groups(id)
ON DELETE CASCADE,
ADD CONSTRAINT fk_gp_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

ALTER TABLE group_messages
ADD CONSTRAINT fk_gm_msg_group
FOREIGN KEY (group_id) REFERENCES groups(id)
ON DELETE CASCADE,
ADD CONSTRAINT fk_gm_msg_sender
FOREIGN KEY (sender_id) REFERENCES users(id)
ON DELETE CASCADE;

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    content TEXT,
    image_url VARCHAR(255),
    video_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE post_likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(post_id,user_id)
);

CREATE TABLE post_votes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(post_id,user_id)
);


CREATE TABLE post_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE post_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(post_id, user_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE group_post_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_post_id INT NOT NULL,
    user_id INT NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(group_post_id, user_id),
    FOREIGN KEY (group_post_id) REFERENCES group_posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE group_message_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    user_id INT NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(message_id, user_id),
    FOREIGN KEY (message_id) REFERENCES group_messages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE comment_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    comment_id INT NOT NULL,
    user_id INT NOT NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(comment_id, user_id),
    FOREIGN KEY (comment_id) REFERENCES post_comments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
ALTER TABLE group_message_views
ADD COLUMN group_id INT NOT NULL AFTER message_id;

ALTER TABLE groups
ADD COLUMN type ENUM('group','private') DEFAULT 'group';

ALTER TABLE group_messages
ADD COLUMN media_url VARCHAR(255) NULL,
ADD COLUMN media_type ENUM('text','image','video','document') DEFAULT 'text';

ALTER TABLE users
ADD reset_token VARCHAR(64) NULL,
ADD reset_expires DATETIME NULL;


CREATE TABLE group_invites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    token VARCHAR(100) NOT NULL UNIQUE,
    type ENUM('private','public') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    used_count INT DEFAULT 0
);

ALTER TABLE users
ADD COLUMN profile_pic VARCHAR(255) NULL,
ADD COLUMN bio TEXT NULL,
ADD COLUMN education VARCHAR(255) NULL,
ADD COLUMN business VARCHAR(255) NULL,
ADD COLUMN website VARCHAR(255) NULL,
ADD COLUMN social_links JSON NULL; -- store {"facebook": "...", "twitter": "...", "linkedin": "..."}

ALTER TABLE post_comments
ADD COLUMN parent_id INT DEFAULT NULL,
ADD CONSTRAINT fk_parent_comment
FOREIGN KEY (parent_id) REFERENCES post_comments(id)
ON DELETE CASCADE;

ALTER DATABASE gstsolut_nishati
CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE post_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE post_likes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE post_votes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE group_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE group_messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;



CREATE TABLE message_reactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT NOT NULL,
    user_id INT NOT NULL,
    reaction VARCHAR(10) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (message_id, user_id)
);

ALTER TABLE message_reactions
MODIFY reaction VARCHAR(191) 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci 
NOT NULL;

ALTER TABLE message_reactions
ADD UNIQUE KEY message_user_unique (message_id, user_id);

CREATE TABLE media_files (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    owner_user_id INT NOT NULL,

    media_type ENUM('image','video','document','audio') NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_name VARCHAR(255) NULL,

    file_data LONGBLOB NOT NULL,
    file_size BIGINT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (owner_user_id) REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE posts
DROP COLUMN image_url,
DROP COLUMN video_url,
ADD COLUMN media_id BIGINT NULL;

ALTER TABLE groups
ADD COLUMN group_pic VARCHAR(255) NULL AFTER description;


ALTER TABLE posts DROP COLUMN media_id;

CREATE TABLE post_media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,

    media_type ENUM('image','video') NOT NULL,
    mime_type VARCHAR(100) NOT NULL,

    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX (post_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);



ALTER TABLE group_messagess
ADD COLUMN reply_to_id INT NOT NULL;

ALTER TABLE group_message_views
ADD UNIQUE KEY uniq_message_view (message_id, group_id, user_id);

CREATE TABLE group_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL,
    started_by INT NOT NULL,
    call_type ENUM('voice','video') NOT NULL,
    channel_name VARCHAR(150) NOT NULL,
    status ENUM('ringing','ongoing','ended','missed') DEFAULT 'ringing',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP NULL,

    FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE,
    FOREIGN KEY (started_by) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE call_participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    call_id INT NOT NULL,
    user_id INT NOT NULL,
    joined_at TIMESTAMP NULL,
    left_at TIMESTAMP NULL,
    UNIQUE(call_id, user_id),

    FOREIGN KEY (call_id) REFERENCES group_calls(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);


