-- Links Directory 1.0.1 schema version 2.
-- No sample, customer, or deployment-specific data is installed.

CREATE TABLE IF NOT EXISTS mm_links_meta (
    meta_key VARCHAR(100) NOT NULL PRIMARY KEY,
    meta_value MEDIUMTEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_links_category (
    category_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    slug VARCHAR(190) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('published','hidden') NOT NULL DEFAULT 'published',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_mm_links_category_parent (parent_id, status, sort_order, name),
    CONSTRAINT fk_mm_links_category_parent FOREIGN KEY (parent_id)
        REFERENCES mm_links_category(category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_links_entry (
    link_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NULL,
    title VARCHAR(255) NOT NULL,
    url VARCHAR(2000) NOT NULL,
    url_hash CHAR(64) NOT NULL,
    dedupe_hash CHAR(64) NULL UNIQUE,
    description TEXT NOT NULL,
    status ENUM('pending','published','rejected') NOT NULL DEFAULT 'pending',
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    contact_name VARCHAR(200) NOT NULL DEFAULT '',
    contact_email VARCHAR(254) NOT NULL DEFAULT '',
    submitter_ip_hash CHAR(64) NOT NULL DEFAULT '',
    legacy_data MEDIUMTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_mm_links_entry_public (status, is_featured, created_at),
    INDEX idx_mm_links_entry_category (category_id, status, is_featured),
    INDEX idx_mm_links_entry_url_hash (url_hash),
    CONSTRAINT fk_mm_links_entry_category FOREIGN KEY (category_id)
        REFERENCES mm_links_category(category_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_links_review (
    review_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    link_id BIGINT UNSIGNED NOT NULL,
    author_name VARCHAR(200) NOT NULL,
    author_email VARCHAR(254) NOT NULL DEFAULT '',
    rating TINYINT UNSIGNED NULL,
    body TEXT NOT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    fingerprint CHAR(64) NOT NULL UNIQUE,
    submitter_ip_hash CHAR(64) NOT NULL DEFAULT '',
    legacy_data MEDIUMTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_mm_links_review_public (link_id, status, created_at),
    CONSTRAINT fk_mm_links_review_link FOREIGN KEY (link_id)
        REFERENCES mm_links_entry(link_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- statement-break

CREATE TABLE IF NOT EXISTS mm_links_rate_limit (
    rate_key CHAR(64) NOT NULL PRIMARY KEY,
    attempts INT UNSIGNED NOT NULL DEFAULT 1,
    expires_at DATETIME NOT NULL,
    INDEX idx_mm_links_rate_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;