Andy’s Neural Works

BEGIN;


CREATE TABLE IF NOT EXISTS "AllRoads"."Bible_Header_Stage_T"
(
    "File_Name" character varying(1000) NOT NULL,
    "Group_Name" text,
    "Book_Name" text,
    "Chapter_Name" text,
    "Page_Line_Number" bigint NOT NULL,
    "Header_Line_Number" bigint NOT NULL,
    "Header_Text" text NOT NULL
);

CREATE TABLE IF NOT EXISTS "AllRoads"."Bible_Paragraphs_Stage_T"
(
    "File_Name" character varying(1000) NOT NULL,
    "Group_Name" text,
    "Book_Name" text,
    "Chapter_Name" text,
    "Page_Line_Number" bigint NOT NULL,
    "Paragraph_Line_Number" bigint NOT NULL,
    "Paragraph_Id" bigint NOT NULL,
    "Paragraph_Text" text NOT NULL,
    "References" text
);

CREATE TABLE IF NOT EXISTS "AllRoads"."Bible_References_Stage_T"
(
    "Book_Name" text,
    "Group_Name" text,
    "File_Name" character varying(1000) NOT NULL,
    "Chapter_Name" text,
    "Page_Line_Number" bigint NOT NULL,
    "Reference_Line_Number" bigint NOT NULL,
    "Reference_Id" text NOT NULL,
    "Reference_Text" text NOT NULL
);

CREATE TABLE IF NOT EXISTS "AllRoads"."Bible_Pages_Stage_T"
(
    "File_Name" character varying(1000) NOT NULL,
    "Title" character varying(1000) NOT NULL,
    "URL" character varying(2048) NOT NULL
);
END;

INSERT INTO "AllRoads"."Texts_T"
("Title"
 ,"Description"
 ,"URL"
 ,"Group"
 ,"Type"
 ,"Audit_Create_Date"
 ,"Audit_Create_User"
)
SELECT h."Book_Name" as "Title"
	   ,h."Book_Name" as "Description" --duplicate book name rather than put a null in it
	   ,pg."URL" as "URL"
	   ,h."Group_Name" as "Group"
	   ,'Bible' as "Type"
	   ,CURRENT_DATE as "Audit_Create_Date"
	   ,'Andy' as "Audit_Create_User"
FROM (
		SELECT *
		  FROM (
			SELECT distinct row_number() over (partition by "Group_Name", "Book_Name" order by cast(substr("File_Name",7,100) as numeric)) as rn,
					"File_Name", 
					"Group_Name", 
					"Book_Name"  
			FROM "AllRoads"."Bible_Header_Stage_T" h1
			WHERE "Book_Name" is not null
		) hdr
		where hdr.rn = 1
	 ) as h
     inner join
	 "AllRoads"."Bible_Pages_Stage_T" as pg on (h."File_Name" = 'Bible.' || pg."File_Name")-- and trim(h."Group_Name") = trim(p."Title"))
ORDER BY case h."Group_Name"
		     when 'The Pentateuch' then 1
			 when 'The Historical Books' then 2
			 when 'The Wisdom Books' then 3
			 when 'The Prophetic Books' then 4
			 when 'The Gospels' then 5
			 when 'New Testament Letters' then 6
			 when 'The Catholic Letters' then 7
		 end
		 ,cast(pg."File_Name" as numeric)
		 , pg."Title"
;

INSERT INTO "AllRoads"."Paragraphs_T"
(
	"Paragraph_Sequence_Id"
	,"Paragraph_Text"
	,"URL"
	,"Reference_Sequence_Ids"
	,"Text_Id"
	,"Page_Id"
	,"Audit_Create_Date"
	,"Audit_Create_User"
)
SELECT case 
			when p."Paragraph_Id" = '-1' then null
			else p."Paragraph_Id"
		end as Paragraph_Sequence_Id
	   ,p."Paragraph_Text"
	   ,pg."URL"
	   ,to_json(string_to_array(p."References",',')) as Reference_Sequence_Ids
	   ,pg."Text_Id"
	   ,pg."Page_Id"
	   ,CURRENT_DATE AS Audit_Create_Date
	   ,'Andy' as Audit_Create_User
  FROM "AllRoads"."Bible_Paragraphs_Stage_T" as p
       INNER JOIN
	   "AllRoads"."Pages_T" as pg on (p."File_Name" = pg."File_Name")
ORDER BY pg."Page_Id", p."Paragraph_Line_Number"
;

INSERT INTO "AllRoads"."Pages_T"
(
	"Page_Sequence_Id"
	,"Title"
	,"Description"
	,"URL"
	,"File_Name"
	,"Text_Id"
	,"Chapter_Id"
	,"Audit_Create_Date"
	,"Audit_Create_User"
)
SELECT ROW_NUMBER() OVER(PARTITION BY pg1."Text_Id" ORDER BY CAST(pg1."File_Name" as INTEGER)) AS Page_Sequence_Id
	   ,pg1."Title"
	   ,pg1."Description"
	   ,pg1."URL"
	   ,pg1."Bible_File_Name"
	   ,pg1."Text_Id"
	   ,pg1."Chapter_Id"
	   ,pg1."Audit_Create_Date"
	   ,pg1."Audit_Create_User"
  FROM (

	SELECT COALESCE(h."Book_Name" || ' ' || pg."Title", pg."Title") as "Title"
		   ,COALESCE(c."Description", pg."Title") as "Description"
		   ,pg."URL"
	  	   ,pg."File_Name"
		   ,('Bible.' || pg."File_Name") as "Bible_File_Name"
		   ,COALESCE(t."Text_Id", 78) as "Text_Id" --78 is the default record in the Texts_T table for general Bible data.
		   ,COALESCE(c."Chapter_Id", 2770) as "Chapter_Id" --2770 is the default record in the Chapters_T for general Bible data.
		   ,CURRENT_DATE as "Audit_Create_Date"
		   ,'Andy' as "Audit_Create_User"
	  FROM "AllRoads"."Bible_Pages_Stage_T" as pg 
		   LEFT OUTER JOIN
		   (SELECT "File_Name", "Group_Name", "Book_Name", "Chapter_Name" from "AllRoads"."Bible_Header_Stage_T" WHERE "Chapter_Name" IS NOT NULL) as h on (h."File_Name" = 'Bible.' || pg."File_Name")	   
		   LEFT OUTER JOIN -- take into consideration that the preface doesn't have a chapter Id
		   "AllRoads"."Chapters_T" as c on (c."URL" = pg."URL") 
		   LEFT OUTER JOIN
		   "AllRoads"."Texts_T" as t on (t."Text_Id" = c."Text_Id")
) pg1
;

INSERT INTO "AllRoads"."References_T"
(
	"Reference_Sequence_Id"
	,"Reference_Text"
	,"Page_Id"
	,"Text_Id"
	,"Audit_Create_Date"
	,"Audit_Create_User"
)
SELECT (case when r."Reference_Id"~ '^[0-9]+$' = TRUE THEN r."Reference_Id" ELSE '-1' END) as Reference_Sequence_Id
       ,(case when r."Reference_Id"~ '^[0-9]+$' = FALSE THEN r."Reference_Id" ELSE '' END) || COALESCE(r."Reference_Text", 'No Reference Text') as Reference_Text
	   ,pg."Page_Id"
	   ,pg."Text_Id"
	   ,CURRENT_DATE AS Audit_Create_Date
	   ,'Andy' as Audit_Create_User
  FROM "AllRoads"."Bible_References_Stage_T" as r
       INNER JOIN
	   "AllRoads"."Pages_T" as pg on (r."File_Name" = pg."File_Name")
ORDER BY cast(pg."Text_Id" as numeric)
		 ,cast(pg."Page_Id" as numeric)
         ,cast((case when r."Reference_Id"~ '^[0-9]+$' = TRUE THEN r."Reference_Id" ELSE NULL END) as numeric)

INSERT INTO "AllRoads"."Chapters_T"
("URL"
,"Title"
,"Description"
,"Text_Id"
 ,"Audit_Create_Date"
 ,"Audit_Create_User"
)
SELECT pg."URL"
       ,h."Book_Name" || ' ' || pg."Title" as "Title"
	   ,h."Book_Name" || ' - ' || h."Chapter_Name" as "Description"
	   ,tt."Text_Id"
	   ,CURRENT_DATE as "Audit_Create_Date"
	   ,'Andy' as "Audit_Update_User"
  FROM "AllRoads"."Bible_Pages_Stage_T" as pg
       INNER JOIN
	   (SELECT "File_Name", "Group_Name", "Book_Name", "Chapter_Name" from "AllRoads"."Bible_Header_Stage_T" WHERE "Chapter_Name" IS NOT NULL) as h on (h."File_Name" = 'Bible.' || pg."File_Name")	   
	   INNER JOIN
	   "AllRoads"."Texts_T" as tt on (h."Group_Name" = tt."Group" and h."Book_Name" = tt."Title")
;