bleeter/sql/install.sql

CREATE TABLE IF NOT EXISTS bleeter_accounts (
    id INT NOT NULL AUTO_INCREMENT,
    char_id VARCHAR(80) NOT NULL,
    identifier VARCHAR(80) NULL,
    mail_address VARCHAR(120) NOT NULL,
    phone_number VARCHAR(20) NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_char_id (char_id),
    UNIQUE KEY uniq_mail_address (mail_address),
    KEY idx_status (status)
);

CREATE TABLE IF NOT EXISTS bleeter_profiles (
    id INT NOT NULL AUTO_INCREMENT,
    account_id INT NULL,
    profile_type VARCHAR(24) NOT NULL,
    handle VARCHAR(40) NOT NULL,
    display_name VARCHAR(80) NOT NULL,
    avatar_url VARCHAR(255) NULL,
    banner_url VARCHAR(255) NULL,
    bio VARCHAR(500) NULL,
    location VARCHAR(120) NULL,
    email_contact VARCHAR(120) NULL,
    phone_contact VARCHAR(20) NULL,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    is_lifeinvader_staff TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    is_locked TINYINT(1) NOT NULL DEFAULT 0,
    locked_reason VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_handle (handle),
    KEY idx_account_id (account_id),
    KEY idx_profile_type (profile_type),
    KEY idx_active (is_active)
);

CREATE TABLE IF NOT EXISTS bleeter_profile_members (
    id INT NOT NULL AUTO_INCREMENT,
    profile_id INT NOT NULL,
    char_id VARCHAR(80) NOT NULL,
    role VARCHAR(40) NOT NULL DEFAULT 'member',
    can_post TINYINT(1) NOT NULL DEFAULT 0,
    can_edit_profile TINYINT(1) NOT NULL DEFAULT 0,
    can_manage_members TINYINT(1) NOT NULL DEFAULT 0,
    source VARCHAR(40) NOT NULL DEFAULT 'manual',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_profile_member (profile_id, char_id),
    KEY idx_char_id (char_id)
);

CREATE TABLE IF NOT EXISTS bleeter_media (
    id INT NOT NULL AUTO_INCREMENT,
    owner_profile_id INT NOT NULL,
    source_type VARCHAR(24) NOT NULL,
    url VARCHAR(500) NOT NULL,
    original_name VARCHAR(180) NULL,
    mime_type VARCHAR(80) NULL,
    size_bytes INT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_owner (owner_profile_id),
    KEY idx_source_type (source_type)
);

CREATE TABLE IF NOT EXISTS bleeter_posts (
    id INT NOT NULL AUTO_INCREMENT,
    author_profile_id INT NOT NULL,
    feed_type VARCHAR(24) NOT NULL,
    body TEXT NULL,
    media_id INT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    self_deleted_at TIMESTAMP NULL DEFAULT NULL,
    hidden_at TIMESTAMP NULL DEFAULT NULL,
    hidden_by_profile_id INT NULL,
    hidden_reason VARCHAR(255) NULL,
    restore_until TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_feed_created (feed_type, created_at),
    KEY idx_author_created (author_profile_id, created_at),
    KEY idx_hidden (hidden_at),
    KEY idx_deleted (deleted_at)
);

CREATE TABLE IF NOT EXISTS bleeter_comments (
    id INT NOT NULL AUTO_INCREMENT,
    post_id INT NOT NULL,
    author_profile_id INT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    self_deleted_at TIMESTAMP NULL DEFAULT NULL,
    hidden_at TIMESTAMP NULL DEFAULT NULL,
    hidden_by_profile_id INT NULL,
    hidden_reason VARCHAR(255) NULL,
    restore_until TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_post_created (post_id, created_at),
    KEY idx_author_created (author_profile_id, created_at)
);

CREATE TABLE IF NOT EXISTS bleeter_likes (
    id INT NOT NULL AUTO_INCREMENT,
    profile_id INT NOT NULL,
    target_type VARCHAR(24) NOT NULL,
    target_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_like (profile_id, target_type, target_id),
    KEY idx_target (target_type, target_id)
);

CREATE TABLE IF NOT EXISTS bleeter_follows (
    id INT NOT NULL AUTO_INCREMENT,
    follower_profile_id INT NOT NULL,
    followed_profile_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_follow (follower_profile_id, followed_profile_id),
    KEY idx_followed (followed_profile_id)
);

CREATE TABLE IF NOT EXISTS bleeter_blocks (
    id INT NOT NULL AUTO_INCREMENT,
    blocker_profile_id INT NOT NULL,
    blocked_profile_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_block (blocker_profile_id, blocked_profile_id),
    KEY idx_blocked (blocked_profile_id)
);

CREATE TABLE IF NOT EXISTS bleeter_marketplace (
    id INT NOT NULL AUTO_INCREMENT,
    author_profile_id INT NOT NULL,
    category VARCHAR(40) NOT NULL,
    title VARCHAR(120) NOT NULL,
    description TEXT NOT NULL,
    price_label VARCHAR(80) NULL,
    media_id INT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_category_status (category, status),
    KEY idx_author (author_profile_id)
);

CREATE TABLE IF NOT EXISTS bleeter_events (
    id INT NOT NULL AUTO_INCREMENT,
    author_profile_id INT NOT NULL,
    title VARCHAR(140) NOT NULL,
    description TEXT NULL,
    location VARCHAR(140) NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_starts_at (starts_at),
    KEY idx_author (author_profile_id)
);

CREATE TABLE IF NOT EXISTS bleeter_business_status (
    profile_id INT NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'closed',
    source VARCHAR(40) NOT NULL DEFAULT 'manual',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (profile_id),
    KEY idx_status (status)
);

CREATE TABLE IF NOT EXISTS bleeter_lifeinvader_permissions (
    id INT NOT NULL AUTO_INCREMENT,
    char_id VARCHAR(80) NOT NULL,
    permission VARCHAR(80) NOT NULL,
    allowed TINYINT(1) NOT NULL DEFAULT 0,
    source VARCHAR(40) NOT NULL DEFAULT 'manual',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_char_permission (char_id, permission)
);

CREATE TABLE IF NOT EXISTS bleeter_audit_logs (
    id INT NOT NULL AUTO_INCREMENT,
    actor_char_id VARCHAR(80) NULL,
    actor_profile_id INT NULL,
    action VARCHAR(80) NOT NULL,
    target_type VARCHAR(40) NULL,
    target_id INT NULL,
    reason VARCHAR(255) NULL,
    payload LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_actor_char (actor_char_id),
    KEY idx_actor_profile (actor_profile_id),
    KEY idx_target (target_type, target_id),
    KEY idx_action (action)
);

ALTER TABLE bleeter_profiles
    ADD COLUMN IF NOT EXISTS is_lifeinvader_staff TINYINT(1) NOT NULL DEFAULT 0 AFTER is_verified;