-- =================================================================
-- AI-Ready Database Schema Documentation Generator
-- Purpose: Generate comprehensive schema documentation for
-- Generative AI Services integration
-- Author: SQL Server Central Community
-- Date: July 2025
-- =================================================================

-- Create temporary table to store schema information
CREATE TABLE #SchemaDoc (
TableName NVARCHAR(128),
ColumnName NVARCHAR(128),
DataType NVARCHAR(128),
MaxLength INT,
IsNullable BIT,
DefaultValue NVARCHAR(256),
IsPrimaryKey BIT,
IsForeignKey BIT,
ReferencedTable NVARCHAR(128),
ReferencedColumn NVARCHAR(128),
ColumnDescription NVARCHAR(500)
);

-- Populate schema information
INSERT INTO #SchemaDoc
SELECT
t.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE +
CASE
WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
WHEN c.NUMERIC_PRECISION IS NOT NULL
THEN '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(10)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(10)) + ')'
ELSE ''
END AS DataType,
c.CHARACTER_MAXIMUM_LENGTH,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
c.COLUMN_DEFAULT,
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
CASE WHEN fk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
fk.REFERENCED_TABLE_NAME,
fk.REFERENCED_COLUMN_NAME,
COALESCE(ep.value, 'No description available') AS ColumnDescription
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN (
SELECT ku.TABLE_NAME, ku.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT JOIN (
SELECT
ku.TABLE_NAME,
ku.COLUMN_NAME,
ku2.TABLE_NAME AS REFERENCED_TABLE_NAME,
ku2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON rc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku2
ON rc.UNIQUE_CONSTRAINT_NAME = ku2.CONSTRAINT_NAME
) fk ON c.TABLE_NAME = fk.TABLE_NAME AND c.COLUMN_NAME = fk.COLUMN_NAME
LEFT JOIN sys.extended_properties ep ON ep.major_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
AND ep.minor_id = COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'ColumnId')
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;

-- Generate AI-friendly documentation
SELECT
'=== TABLE: ' + TableName + ' ===' AS Documentation
FROM #SchemaDoc
GROUP BY TableName
UNION ALL
SELECT
'• ' + ColumnName + ' (' + DataType + ')' +
CASE WHEN IsPrimaryKey = 1 THEN ' [PRIMARY KEY]' ELSE '' END +
CASE WHEN IsForeignKey = 1 THEN ' [FOREIGN KEY → ' + ReferencedTable + '.' + ReferencedColumn + ']' ELSE '' END +
CASE WHEN IsNullable = 0 THEN ' [NOT NULL]' ELSE '' END +
' - ' + ColumnDescription
FROM #SchemaDoc
ORDER BY TableName, ColumnName;

-- Generate JSON format for API integration
SELECT
TableName,
(
SELECT
ColumnName,
DataType,
IsNullable,
IsPrimaryKey,
IsForeignKey,
ReferencedTable,
ReferencedColumn,
ColumnDescription
FROM #SchemaDoc s2
WHERE s2.TableName = s1.TableName
FOR JSON PATH
) AS Columns
FROM #SchemaDoc s1
GROUP BY TableName
FOR JSON PATH;

-- Cleanup
DROP TABLE #SchemaDoc;