WITH TableData AS (
    SELECT 
        t.TABLE_NAME,
        tc.COMMENTS AS TABLE_COMMENT,
        c.COLUMN_NAME,
        c.DATA_TYPE,
        CASE 
            WHEN c.DATA_TYPE IN ('CHAR', 'VARCHAR') 
                THEN CAST(c.CHAR_LENGTH AS VARCHAR)
            WHEN c.DATA_TYPE IN ('DECIMAL', 'NUMERIC') 
                THEN CONCAT(CAST(c.DATA_PRECISION AS VARCHAR), ',', CAST(c.DATA_SCALE AS VARCHAR))
            ELSE CAST(c.DATA_LENGTH AS VARCHAR)
        END AS LENGTH_PRECISION,
        cc.COMMENTS AS COLUMN_COMMENT,
        c.NULLABLE,
        c.COLUMN_ID,
        -- 判断是否为主键
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM DBA_CONS_COLUMNS pkc
                JOIN DBA_CONSTRAINTS pk ON pkc.OWNER = pk.OWNER 
                    AND pkc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
                WHERE pk.CONSTRAINT_TYPE = 'P'
                    AND pkc.OWNER = c.OWNER
                    AND pkc.TABLE_NAME = c.TABLE_NAME
                    AND pkc.COLUMN_NAME = c.COLUMN_NAME
            ) THEN '✅'
            ELSE ''
        END AS IS_PRIMARY_KEY
    FROM 
        DBA_TABLES t
        LEFT JOIN DBA_TAB_COMMENTS tc ON t.OWNER = tc.OWNER AND t.TABLE_NAME = tc.TABLE_NAME
        LEFT JOIN DBA_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
        LEFT JOIN DBA_COL_COMMENTS cc ON c.OWNER = cc.OWNER AND c.TABLE_NAME = cc.TABLE_NAME 
            AND c.COLUMN_NAME = cc.COLUMN_NAME
    WHERE 
        t.OWNER = 'RISK_CLOUD'
)
SELECT 
    CASE 
        WHEN ROW_TYPE = 'TABLE_HEADER' THEN '📋 表名: ' || TABLE_NAME || 
             CASE WHEN TABLE_COMMENT IS NOT NULL AND TABLE_COMMENT != '' 
                  THEN '  -  ' || TABLE_COMMENT 
                  ELSE '' 
             END
        WHEN ROW_TYPE = 'COLUMN_HEADER' THEN '   ├─ 字段结构'
        WHEN ROW_TYPE = 'COLUMN_TITLE' THEN ''
        WHEN ROW_TYPE = 'COLUMN_DATA' THEN ''
        ELSE ''
    END AS 表结构信息,
    
    CASE 
        WHEN ROW_TYPE = 'COLUMN_TITLE' THEN '字段名称'
        WHEN ROW_TYPE = 'COLUMN_DATA' THEN COLUMN_NAME
        ELSE ''
    END AS 字段名称,
    
    CASE 
        WHEN ROW_TYPE = 'COLUMN_TITLE' THEN '数据类型'
        WHEN ROW_TYPE = 'COLUMN_DATA' THEN DATA_TYPE
        ELSE ''
    END AS 数据类型,
    
    CASE 
        WHEN ROW_TYPE = 'COLUMN_TITLE' THEN '长度/精度'
        WHEN ROW_TYPE = 'COLUMN_DATA' THEN LENGTH_PRECISION
        ELSE ''
    END AS 长度精度,
    
    CASE 
        WHEN ROW_TYPE = 'COLUMN_TITLE' THEN '字段注解'
        WHEN ROW_TYPE = 'COLUMN_DATA' THEN COALESCE(COLUMN_COMMENT, '')
        ELSE ''
    END AS 字段注解,
    
    CASE 
        WHEN ROW_TYPE = 'COLUMN_TITLE' THEN '是否为空'
        WHEN ROW_TYPE = 'COLUMN_DATA' THEN CASE NULLABLE WHEN 'Y' THEN '是' ELSE '否' END
        ELSE ''
    END AS 是否为空,
    
    CASE 
        WHEN ROW_TYPE = 'COLUMN_TITLE' THEN '主键'
        WHEN ROW_TYPE = 'COLUMN_DATA' THEN IS_PRIMARY_KEY
        ELSE ''
    END AS 是否主键
    
FROM (
    -- 表头行
    SELECT 'TABLE_HEADER' AS ROW_TYPE, TABLE_NAME, TABLE_COMMENT,
           NULL AS COLUMN_NAME, NULL AS DATA_TYPE, NULL AS LENGTH_PRECISION,
           NULL AS COLUMN_COMMENT, NULL AS NULLABLE, NULL AS IS_PRIMARY_KEY, 
           0 AS SORT_ORDER, 0 AS COLUMN_ID
    FROM (SELECT DISTINCT TABLE_NAME, TABLE_COMMENT FROM TableData)
    
    UNION ALL
    
    -- 字段结构标题
    SELECT 'COLUMN_HEADER' AS ROW_TYPE, TABLE_NAME, TABLE_COMMENT,
           NULL AS COLUMN_NAME, NULL AS DATA_TYPE, NULL AS LENGTH_PRECISION,
           NULL AS COLUMN_COMMENT, NULL AS NULLABLE, NULL AS IS_PRIMARY_KEY, 
           1 AS SORT_ORDER, 0 AS COLUMN_ID
    FROM (SELECT DISTINCT TABLE_NAME, TABLE_COMMENT FROM TableData)
    
    UNION ALL
    
    -- 字段标题行
    SELECT 'COLUMN_TITLE' AS ROW_TYPE, TABLE_NAME, TABLE_COMMENT,
           NULL AS COLUMN_NAME, NULL AS DATA_TYPE, NULL AS LENGTH_PRECISION,
           NULL AS COLUMN_COMMENT, NULL AS NULLABLE, NULL AS IS_PRIMARY_KEY, 
           2 AS SORT_ORDER, 0 AS COLUMN_ID
    FROM (SELECT DISTINCT TABLE_NAME, TABLE_COMMENT FROM TableData)
    
    UNION ALL
    
    -- 字段数据行
    SELECT 'COLUMN_DATA' AS ROW_TYPE, TABLE_NAME, TABLE_COMMENT,
           COLUMN_NAME, DATA_TYPE, LENGTH_PRECISION,
           COLUMN_COMMENT, NULLABLE, IS_PRIMARY_KEY, 
           3 AS SORT_ORDER, COLUMN_ID
    FROM TableData
    
    UNION ALL
    
    -- 空行分隔
    SELECT 'SPACER' AS ROW_TYPE, TABLE_NAME, TABLE_COMMENT,
           NULL AS COLUMN_NAME, NULL AS DATA_TYPE, NULL AS LENGTH_PRECISION,
           NULL AS COLUMN_COMMENT, NULL AS NULLABLE, NULL AS IS_PRIMARY_KEY, 
           4 AS SORT_ORDER, 999 AS COLUMN_ID
    FROM (SELECT DISTINCT TABLE_NAME, TABLE_COMMENT FROM TableData)
)
ORDER BY 
    TABLE_NAME,
    SORT_ORDER,
    COLUMN_ID;