sources.xrm

source-group wikibus {
	type rdb

	logical-source sources {
		source query "select s.Id,
			FileCabinet,
			FileOffset,
			fc.Description as FileCabinetName
		from Sources.Source s
        join Priv.FileCabinet fc on fc.Id = s.FileCabinet"

		referenceables
			Id
			FileCabinet
			FileOffset
			FileCabinetName
	}

	logical-source brochures {
		source query "select
			s.Id,
			'brochure' as SourceType,
			Pages,
			IIF(rtrim(Notes) = '', NULL, Notes) as Notes,
			FileCabinet,
			FileOffset,
			FolderCode,
			FolderName,
			rtrim([User]) as [User],
			DateCreated,
			DateUpdated,
			IIF(rtrim(value) = '', NULL, value) as Language,
			IIF(Image is NULL, NULL, s.Id) as LegacyImage,
			IIF(Day is null and Month is null, Year, NULL) as Year,
			IIF(
				Day is not null and Month is not null and Year is not null,
				CONCAT(format(Year, '0000'), '-', format(month, '00'), '-', format(Day, '00')),
				NULL
			) as Date,
			IIF(
				Day is null and Month is not null and Year is not null,
				CONCAT(format(Year, '0000'), '-', format(month, '00')),
				NULL
			) as YearMonth,
			i.ExternalId as Image,
			IIF(ContentUrl is NULL, NULL, s.Id) as AssociatedMedia			
		from Sources.Source s
		left join Sources.Images i on i.SourceId = s.Id
		CROSS APPLY STRING_SPLIT(Languages, ';')
		where SourceType = 'folder'"

		referenceables
			Id
			Pages
			Year
			YearMonth
			Date
			Notes
			FileCabinet
			FileOffset
			FolderCode
			FolderName
			User
			DateCreated
			DateUpdated
			Language
			LegacyImage
			SourceType
			Image
			AssociatedMedia
	}

	logical-source brochures_files {
		source query "select
			Id,
			ContentSize as ByteSize,
			concat(cast(round(cast(ContentSize as decimal) / 1024 /1024, 2) as decimal(5,2)), ' MB') as ContentSize,
			dbo.UrlDecode(right(ContentUrl, charindex('/', reverse(ContentUrl) + '/') - 1)) as ContentName
		from Sources.Source
		where SourceType = 'folder' and ContentUrl IS NOT NULL"

		referenceables
			Id
			ContentSize
			ByteSize
			ContentName
	}

	logical-source legacy_images {
		source query "
			select
				Id,
				IIF(SourceType = 'folder', 'brochure', SourceType) as SourceType
			from [Sources].Source
			where Image is NOT NULL
		"

		referenceables
			Id
			SourceType
	}

	logical-source magazine_legacy_images {
		source query "
			select
				s.Id,
				s.MagIssueNumber,
				m.Name as MagName,
    			lower(replace(
					replace(
						cast(m.Name as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS,
						' ', '-'),
					'&', '-')) as MagazineSlug
			from [Sources].Source s
			join Sources.Magazine m on m.Id = s.MagIssueMagazine
			where s.Image is NOT NULL
		"

		referenceables
			Id
			MagName
			MagazineSlug
			MagIssueNumber
	}

	logical-source books {
		source query "select
    Id,
	SourceType,
    Pages,
    Year,
    IIF(rtrim(Notes) = '', NULL, Notes) as Notes,
    BookTitle,
    IIF(trim(BookISBN) = '', NULL, trim(BookISBN)) as BookISBN,
    BookAuthor,
    rtrim([User]) as [User],
	DateCreated,
    DateUpdated,
    IIF(rtrim(value) = '', NULL, value) as Language,
    IIF(Image is NULL, NULL, Id) as LegacyImage
from Sources.Source
         CROSS APPLY STRING_SPLIT(Languages, ';')
where SourceType = 'book'"

		referenceables
			Id
			Pages
			Year
			Notes
			BookTitle
			BookISBN
			BookAuthor
			FolderName
			User
			Language
			LegacyImage
			DateCreated
			DateUpdated
			SourceType
	}

	logical-source bookAuthors {
		source query "
			SELECT 
				Id,
				concat(Id, '_', ROW_NUMBER() over (PARTITION BY Id order by Id)) as AuthorId,
				trim(value) as Author
			FROM Sources.Source
			CROSS APPLY STRING_SPLIT(BookAuthor, ',')
			WHERE SourceType = 'book' and BookAuthor <> ''
		"

		referenceables
			Id
			AuthorId
			Author
	}

	logical-source primaryImages {
		source query "
    SELECT i.SourceId,
           i.ExternalId
    FROM Sources.Images i
	join Sources.Source b on i.SourceId = b.Id
	WHERE i.OrderIndex = 1 and b.SourceType = 'folder'"

		referenceables
			SourceId
			ExternalId
	}

	logical-source images {
		source query "
			select 
				i.*, 
				IIF(s.SourceType = 'folder', 'brochure', s.SourceType) as SourceType
			from [Sources].[Images] i 
			join [Sources].[Source] s on i.SourceId = s.Id"

		referenceables
			SourceId
			Id
			ExternalId
			OrderIndex
			SourceType
			OriginalUrl
			ThumbnailUrl
	}

	logical-source users {
		source query "select rtrim([UserId]) as [UserId], DriveImportFolder from [Sources].[Users]"

		referenceables
			UserId
			DriveImportFolder
	}

	logical-source magazines {
		source query "select *,
			lower(replace(
				replace(
					cast(Name as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS,
				' ', '-'),
			'&', '-')) as Slug
		from Sources.Magazine"

		referenceables
			Id
			Name
			SubName
			Slug
	}

	logical-source magazineIssues {
		source query "select
			s.Id,
			s.Pages,
			IIF(rtrim(Notes) = '', NULL, s.Notes) as Notes,
			s.FileCabinet,
			s.FileOffset,
			s.MagIssueNumber,
			rtrim(s.[User]) as [User],
			DateCreated,
			DateUpdated,
			IIF(rtrim(value) = '', NULL, value) as Language,
			IIF(s.Image is NULL, NULL, s.Id) as LegacyImage,
			IIF(
				Day is not null and Month is not null and Year is not null,
				CONCAT(format(Year, '0000'), '-', format(month, '00'), '-', format(Day, '00')),
				NULL
			) as Date,
			IIF(
				Day is null and Month is not null and Year is not null,
				CONCAT(format(Year, '0000'), '-', format(month, '00')),
				NULL
			) as YearMonth,
			lower(replace(
					replace(
							cast(m.Name as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS,
							' ', '-'),
					'&', '-')) as MagazineSlug
		from Sources.Source s
				join Sources.Magazine m on m.Id = s.MagIssueMagazine
				CROSS APPLY STRING_SPLIT(s.Languages, ';')
		where s.SourceType = 'magissue'"

		referenceables
			Id
			Pages
			Date
			YearMonth
			Notes
			FileCabinet
			FileOffset
			MagazineSlug
			MagIssueNumber
			User
			DateCreated
			DateUpdated
			Language
			LegacyImage
	}

	logical-source wishlist {
		source query "select 
			Id,
			rtrim([User]) as [User],
			SourceId,
			IIF(Done = '1', 'Complete', 'Incomplete') as Status 
		from Sources.Wishlist"

		referenceables
			Id
			User
			SourceId
			Status
	}

	logical-source cabinets {
		source "[Priv].[FileCabinet]"

		referenceables
			Id
			Description
			CompartmentsCount
	}
}