-- ============================================================================================
-- PRODUCTION MIGRATION — Project Wiki Pages
-- ============================================================================================
-- Multi-page Wiki/Documentation per project — a self-referencing page tree (title + rich-text
-- HTML body), modeled on fact_project_tasks' parent/child self-join. Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p `stacie_Aggie_v1.0` < db/project_wiki_pages.sql
-- ============================================================================================

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS dim_project_wiki_pages (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    project_id    BIGINT NOT NULL,
    parent_id     BIGINT NULL,
    title         VARCHAR(255) NOT NULL,
    body_html     LONGTEXT NULL,
    order_index   INT NOT NULL DEFAULT 0,
    created_at    TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by    INT NULL,
    updated_at    TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    updated_by    INT NULL,
    is_deleted    TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT fk_wiki_page_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT fk_wiki_page_project FOREIGN KEY (project_id) REFERENCES dim_projects(id),
    CONSTRAINT fk_wiki_page_parent FOREIGN KEY (parent_id) REFERENCES dim_project_wiki_pages(id),
    INDEX idx_wiki_page_project (tenant_id, project_id, is_deleted),
    INDEX idx_wiki_page_parent (parent_id)
);