CREATE DATABASE IF NOT EXISTS gigantomoda_facebook;
USE gigantomoda_facebook;

CREATE TABLE IF NOT EXISTS fb_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id VARCHAR(50) UNIQUE NOT NULL,
    message TEXT,
    created_time DATETIME,
    permalink_url TEXT,
    author_name VARCHAR(255),
    author_id VARCHAR(50),
    post_type ENUM('text', 'photo', 'video', 'album'),
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS fb_media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id VARCHAR(50) NOT NULL,
    media_id VARCHAR(50),
    media_url TEXT,
    media_type ENUM('photo', 'video'),
    description TEXT,
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES fb_posts(post_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS fb_videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id VARCHAR(50) NOT NULL,
    video_id VARCHAR(50),
    video_url TEXT,
    thumbnail_url TEXT,
    file_path TEXT,
    downloaded BOOLEAN DEFAULT FALSE,
    inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES fb_posts(post_id) ON DELETE CASCADE
);
