DROP PROCEDURE IF EXISTS p_check_FK//

CREATE PROCEDURE p_check_FK (v_database_name VARCHAR(255))
BEGIN

	-- #1 For each PK, I’ll check is it used at least once as a FK.
    SELECT
    	"WARNING" AS error_type,
        CONCAT("Attribute '",primary_keys.TABLE_NAME,".",primary_keys.COLUMN_NAME,"' is not referenced anywhere in the data model.") AS description,
    	primary_keys.TABLE_NAME AS "table_name",
        primary_keys.COLUMN_NAME AS "column_name"
    FROM
    (

        SELECT 
            INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY
        FROM INFORMATION_SCHEMA.TABLES 
        INNER JOIN INFORMATION_SCHEMA.COLUMNS 
            ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
            AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
        WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = v_database_name
        AND INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY = "PRI"
        AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = "id"
        ORDER BY INFORMATION_SCHEMA.TABLES.TABLE_NAME
    ) primary_keys

    LEFT JOIN

    (        
        SELECT DISTINCT
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
        FROM
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME IS NOT NULL
        AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA = v_database_name
        ORDER BY
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME
    ) foreign_keys

    ON primary_keys.TABLE_NAME = foreign_keys.REFERENCED_TABLE_NAME AND primary_keys.COLUMN_NAME = foreign_keys.REFERENCED_COLUMN_NAME
    WHERE foreign_keys.REFERENCED_TABLE_NAME IS NULL
    
    UNION
    
    -- #2 For all FK attributes, I’ll check are they named <referenced_table_name>_id.
    SELECT
    	"ERROR - naming FK" AS error_type,
        CONCAT("Attribute '",INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME,".",INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,"' references the table '",INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,"' and attribute '",INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME,"' and should be named '",INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,"_id'.") AS description,
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME AS "table_name",
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS "column_name"
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME IS NOT NULL
    AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA = v_database_name
    AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME <> CONCAT(INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,"_id")
    
    UNION

	-- #3 For all attributes that are named <any_table_from_the_database>_id, I’ll check do they have defined the FK constraint that references the id column in that table.    
    SELECT
    	"ERROR - missing FK" AS error_type,
        CONCAT("Attribute '",FK_candidates.table_name,".",FK_candidates.column_name,"' uses the name of the table +'_id' and should have defined FK that references the ",FK_candidates.referenced_table_name,".", FK_candidates.referenced_column_name,"' attribute.") AS description,
        FK_candidates.table_name AS "table_name",
        FK_candidates.column_name AS "column_name"
    FROM
    
    (
    	SELECT DISTINCT
            all_columns.TABLE_NAME AS "table_name",
            all_columns.COLUMN_NAME as "column_name",
            all_tables.TABLE_NAME AS "referenced_table_name",
            "id" AS "referenced_column_name"
        FROM
        (
            -- all columns
            SELECT 
                INFORMATION_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLES 
            INNER JOIN INFORMATION_SCHEMA.COLUMNS 
                ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
                AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
            WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = v_database_name
        ) all_columns


        INNER JOIN

        (
            -- all tables
            SELECT 
                INFORMATION_SCHEMA.TABLES.TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
        ) all_tables

        ON all_columns.COLUMN_NAME = CONCAT(all_tables.TABLE_NAME,"_id")
	) AS FK_candidates
    
    LEFT JOIN
    
    (
        SELECT
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME IS NOT NULL
        AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA = v_database_name        
    ) defined_FK
    
    ON FK_candidates.table_name = defined_FK.table_name AND FK_candidates.column_name = defined_FK.column_name AND FK_candidates.referenced_table_name = defined_FK.referenced_table_name AND FK_candidates.referenced_column_name = defined_FK.referenced_column_name
    
    WHERE defined_FK.referenced_table_name IS NULL;
END//

-- CALL p_check_FK ("simple_db")