-- MEDORIA AI SEARCH APPLICATION SCHEMA
-- COLLATION: utf8mb3_general_ci

SET NAMES utf8mb3;

-- =========================
-- ADMINS
-- =========================
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


-- =========================
-- SOURCES
-- =========================
CREATE TABLE sources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url TEXT NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


-- =========================
-- FEED SOURCES
-- =========================
CREATE TABLE wm_feed_sources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feed_name VARCHAR(255) NOT NULL,
    feed_url VARCHAR(500) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    crawl_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_run_at DATETIME NULL DEFAULT NULL,
    last_success_at DATETIME NULL DEFAULT NULL,
    last_error_at DATETIME NULL DEFAULT NULL,
    last_error_message TEXT NULL,
    next_run_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uq_wm_feed_sources_feed_url (feed_url)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
-- =========================
-- FEED ITEMS
-- =========================
CREATE TABLE wm_feed_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feed_source_id INT NOT NULL,
    article_url TEXT NOT NULL,
    original_title TEXT NOT NULL,
    translated_title TEXT NULL,
    published_at DATETIME NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


-- =========================
-- ADMIN NOTICES
-- =========================
CREATE TABLE wm_admin_notices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    notice_type VARCHAR(50) NOT NULL,
    notice_context VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    is_dismissed TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dismissed_at DATETIME NULL DEFAULT NULL,
    KEY idx_wm_admin_notices_context (notice_context),
    KEY idx_wm_admin_notices_dismissed (is_dismissed),
    KEY idx_wm_admin_notices_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- =========================
-- ARTICLES
-- =========================
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,

    source_url TEXT NOT NULL,
    source_title TEXT,

    translated_title TEXT,
    final_title TEXT,

    content LONGTEXT,
    alias VARCHAR(255),

    tags TEXT,
    meta_description TEXT,

    status ENUM('rejected','pending','ready','done') NOT NULL DEFAULT 'pending',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


-- =========================
-- SETTINGS
-- =========================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value LONGTEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


-- =========================
-- TRANSFER LOGS
-- =========================
CREATE TABLE transfer_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    response TEXT,
    status VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;