select
	c.typeid
	, c.id
	, c.value
	, p.value
from cmds c
	left join cmds p on p.id = c.parentid 
where c.typeid in (5,6,11)
	and c.hardcmdid = -1
order by c.typeid

select * from indexedlocations




select count(*) from tokensequences where value regexp '^.*the*.*$'
select * from tokensequences limit 10

-- I ended up not using this table, which has been deployed.

-- **********************************************************************************************
-- Copy DateCached from token cmds to their parents.
-- **********************************************************************************************
with cteTokensWithLatestCacheDate as (
	select c.id, c.parentid, max(c.datecached) TokenLatestDateCached, p.datecached as ParentDateCached
	from cmds c
		inner join cmds p on p.id = c.parentid
	where c.typeid in (8,9)
	group by c.parentid
)
select * from cteTokensWithLatestCacheDate
update cmds
set datecached = ifnull((select max(cte.TokenLatestDateCached, cte.ParentDateCached) from cteTokensWithLatestCacheDate cte where cte.parentid = cmds.id), datecached)
where typeid not in (8,9);


select count(*) from cmds where typeid not in (8,9)

with cteTokensWithLatestCacheDate as (
	select c.id, c.parentid, max(c.datecached) TokenLatestDateCached, p.datecached as ParentDateCached
	from cmds c
		inner join cmds p on p.id = c.parentid
	where c.typeid in (8,9)
	group by c.parentid
)
select *, (select max(cte.TokenLatestDateCached, cte.ParentDateCached) from cteTokensWithLatestCacheDate cte where cte.parentid = id)
from cmds 
where typeid not in (8,9)
order by id

-- **********************************************************************************************
-- DATA SANITIZATION
-- **********************************************************************************************

-- Orphan cmds #1: master cmd missing.
--select *
--delete 
from cmds where masterid not in (select id from cmds);

-- Orphan cmds #2: parent cmd missing.
--select *
--delete 
from cmds where parentid != -1 and parentid not in (select id from cmds);

-- Duplicate token-offset tokens for the same (parent) cmd. Delete the ones with the lowest cmd ID.
with cteParentCmdIDsHavingDuplicateTokenOffsets as (
	select distinct parentid
	from cmds
	where offset != -1
	group by parentid, offset
	having count(distinct id) > 1
)
, cteTokenCmdsOfParentsWithDupes as (
	select id
	from cmds where parentid in (select parentid from cteParentCmdIDsHavingDuplicateTokenOffsets)
)
--select * from cteTokenCmdsOfParentsWithDupes
, cteHighestCmdIDsFromDuplicateTokenOffsets as (
	select parentid, max(id) as HighestCmdID
	from cmds 
	where offset != -1
	group by parentid, offset
	having count(distinct id) > 1
)
--select * from cteHighestCmdIDsFromDuplicateTokenOffsets
--select *
delete
from cmds where id in (select id from cteTokenCmdsOfParentsWithDupes)
	and id not in (select HighestCmdID from cteHighestCmdIDsFromDuplicateTokenOffsets);
	

select *
from cmds where id not in (select cmdid from tokensequences)
order by id

select *
from tokensequences where cmdid not in (select id from cmds)

-- **********************************************************************************************
-- TEMP TABLE: Cmd Types
-- **********************************************************************************************

create temp table tmp_CmdTypes (TypeID int, Name text);
insert into tmp_CmdTypes values (2, 'System');
insert into tmp_CmdTypes values (3, 'Path');
insert into tmp_CmdTypes values (4, 'Special Folder');
insert into tmp_CmdTypes values (5, 'URL');
insert into tmp_CmdTypes values (6, 'Alias');
insert into tmp_CmdTypes values (7, 'Argument');
insert into tmp_CmdTypes values (8, 'Token');
insert into tmp_CmdTypes values (9,'UrlVariant');
insert into tmp_CmdTypes values (10,'Pidl');
insert into tmp_CmdTypes values (11,'QueryUrl');
insert into tmp_CmdTypes values (12,'DriveVolume');
insert into tmp_CmdTypes values (13,'CmdExeSequence');
insert into tmp_CmdTypes values (14,'Imdb');
insert into tmp_CmdTypes values (15,'ControlPanelItem');
insert into tmp_CmdTypes values (16,'WikipediaArgVariant');
insert into tmp_CmdTypes values (17,'ContextMenuItem');
insert into tmp_CmdTypes values (18,'StartMenuItem');
insert into tmp_CmdTypes values (19,'RecycleBinItem');
insert into tmp_CmdTypes values (20,'DictionaryEntry');
insert into tmp_CmdTypes values (21,'InstalledWinProgram');
insert into tmp_CmdTypes values (22,'WindowsAudioDevice');
--select * from tmp_cmdtypes

-- **********************************************************************************************
-- CLEAR TOKENSEQUENCES TABLE
-- **********************************************************************************************

delete from TokenSequences; 

-- **********************************************************************************************
-- CONVERT NON-TOKEN CMD VALUES TO TOKEN SEQUENCES
-- **********************************************************************************************

create temp table tmp_CmdsWithTokenSequenceCandidates as 
	select 
		c.id as CmdID
		, c.typeid as CmdTypeID
		, t.name as CmdTypeName
		, cast(case when c.typeid = 15 then c.displayname else c.value end as text) as TokenSequenceCandidate
	from cmds c
		inner join tmp_CmdTypes t on t.typeid = c.typeid
	where c.typeid in (2,3,4,5,6,7,11,12,15,16,17,18)
	order by c.typeid
;
--select * from tmp_CmdsWithTokenSequenceCandidates order by CmdTypeID;

insert into TokenSequences (Value, TypeID, CmdID, IsCustomUserValue)
	select TokenSequenceCandidate, 1, CmdID from tmp_CmdsWithTokenSequenceCandidates, 0;
--select * from TokenSequences order by value

-- ------------------------------------------------------------
-- VERIFY NO DUPLICATES!

-- Should be zero.
--select *, count(*) from tokensequences group by id, value, typeid, cmdid having count(*) > 1

-- There is currently a one-to-one relationship between cmds and token sequences.
-- Should be zero.
/*
select value, cmdid, count(id)
from TokenSequences
group by value, cmdid
having count(id) > 1;
*/
-- ------------------------------------------------------------

/*
-- **********************************************************************************************
-- NEW TABLE TokenSequenceUses
-- **********************************************************************************************

create table TokenSequenceUses (
	TokenSequenceID int not null
	,CharOffset int not null
	,LastUse datetime not null
	,FreqRankAllTime int default 0 
	,FreqRankCurrent int default 0 
	,unique(TokenSequenceID, CharOffset)
);
create index TokenSequenceUses_Idx_TokenSequenceID_CharOffset on TokenSequenceUses (TokenSequenceID, CharOffset);
--select * from TokenSequenceUses
*/

-- **********************************************************************************************
-- CMD USE OF TOKEN CMDS
-- **********************************************************************************************

create temp table tmp_TokenCmdsUsed as
	select 
		c.id as CmdID
		, case
			-- Note: When a token's parent is missing a 0-offset token cmd, this column will render as null,
			-- so further down, I filter those out. 
			when (select typeid from cmds where id = c.parentid) in (3,18) then 
				length((select value from cmds where id = c.parentid)) 
				- length((select value from cmds where typeid = 8 and offset = 0 and parentid = c.parentid))
				- (case when p.value like '%.lnk' then 4 else 0 end)
				+ c.offset
			else c.offset
		end as CharOffset
		, c.LastUse
		, c.FreqRankAllTime
		, c.FreqRankCurrent
		
		, c.parentid
		, (select typeid from cmds where id = c.parentid) 
		, length((select value from cmds where id = c.parentid))
		, length(c.value)
		, c.offset
	from cmds c
		inner join cmds p on p.id = c.parentid
	where c.typeid in (8,9) 
		and c.parentid in (select cmdid from TokenSequences)
		and c.lastuse != '0001-01-01 00:00:00.000'
		and c.FreqRankCurrent > 0
;
--select * from tmp_TokenCmdsUsed;
--select * from tmp_TokenCmdsUsed where charoffset is null;

-- **********************************************************************************************
-- CMD USE OF NON-TOKEN CMDS WITH ALL-TOKEN VALUES
-- **********************************************************************************************

create temp table tmp_NonTokenAllTokensCmdsUsed as
	select id as CmdID, 0 as CharOffset, LastUse, FreqRankAllTime, FreqRankCurrent
	from cmds 
	where typeid in (2,5,6,7,15,16,17,19,20,21,22) 
		and id in (select cmdid from TokenSequences)
		and lastuse != '0001-01-01 00:00:00.000'
		and FreqRankCurrent > 0
		and (typeid != 15 or displayname != '')
;
--select * from tmp_NonTokenAllTokensCmdsUsed order by freqrankcurrent desc;

-- **********************************************************************************************
-- POPULATE TABLE TokenSequenceUses
-- **********************************************************************************************

delete from TokenSequenceUses;

insert into TokenSequenceUses (TokenSequenceID, CharOffset, LastUse, FreqRankAllTime, FreqRankCurrent)
	--with cte as (
	--select tokensequenceid, charoffset, count(*)
	--from (
		select ts.id as TokenSequenceID, tcu.charoffset, max(tcu.lastuse), max(tcu.freqrankalltime), max(tcu.freqrankcurrent)--, tcu.cmdid as CmdID_TokenCmd, c.parentid
		from tmp_TokenCmdsUsed tcu
			inner join cmds c on c.id = tcu.cmdid
			inner join tokensequences ts on ts.cmdid = c.parentid
		where not charoffset is null -- See above about this.
		group by c.parentid, c.offset -- Prevents erroneous duplicate tokens (with the same charoffset).
		order by tokensequenceid, charoffset
--	)
--	group by tokensequenceid, charoffset
--	having count(distinct CmdID_TokenCmd) > 1
--	)
--	, cte2 as (select cmdid from tokensequences where id in (select distinct tokensequenceid from cte))
--	select * from cmds where id in (select cmdid from cte2)
--	select * from cte
	;
--select * from TokenSequenceUses;
--select * from TokenSequences ts left join TokenSequenceUses tsu on tsu.tokensequenceid = ts.id order by ts.value, tsu.charoffset;
--select * from TokenSequences ts left join TokenSequenceUses tsu on tsu.tokensequenceid = ts.id left join cmds c on c.id = ts.cmdid order by ts.value, tsu.charoffset;
--select id, typeid, parentid, masterid, value, offset from cmds where masterid in (select masterid from cmds where id in (819434,1520087,790232,795346,828598,904011,975665,948126,1055785,1019323)) order by parentid, offset

insert into TokenSequenceUses (TokenSequenceID, CharOffset, LastUse, FreqRankAllTime, FreqRankCurrent)
	--with cte as (
	select ts.id as TokenSequenceID, tcu.charoffset, tcu.lastuse, tcu.freqrankalltime, tcu.freqrankcurrent
	from tmp_NonTokenAllTokensCmdsUsed tcu
		inner join cmds c on c.id = tcu.cmdid
		inner join tokensequences ts on ts.cmdid = c.id
	order by tokensequenceid, charoffset
	--)
	--select * from cte 
	--	inner join TokenSequenceUses tsu on tsu.tokensequenceid = cte.tokensequenceid and tsu.charoffset = cte.charoffset
	;
--select * from TokenSequenceUses order by tokensequenceid, charoffset;

-- **********************************************************************************************
-- UNUSED NEW TABLE: TokenSequenceUseTimestamps
-- **********************************************************************************************

/*
 * Not using this yet. Will I?
 * 
create table TokenSequenceUseTimestamps (
	TokenSequenceID text not null
	,DateUsed text not null
	,unique(TokenSequenceID, DateUsed)
); 
--select * from TokenSequenceUseTimestamps
*/

-- **********************************************************************************************
-- TEST QUERY #1
-- **********************************************************************************************

/*
create temp table tmp_TestQuery1 as 
	select
		c.typeid as CmdTypeID
		, c.id as CmdID
		, ts.value as TokenSequenceValue
		, case
			when c.typeid in (3,18) then 
				substr(c.value, 
					length(c.value) 
					- length((select value from cmds where parentid = c.id and typeid = 8 and offset = 0))
					- (case when c.value like '%.lnk' then 4 else 0 end)
					+ 1)  
			else ts.value 
		end as SuggestionValue
		, substr(ts.value, CharOffset + 1) as SuggestionAtCharOffset
		, ts.id as tokensequenceid
		, tsu.charoffset
		, tsu.lastuse
		, tsu.freqrankalltime
		, tsu.freqrankcurrent
	from tokensequences ts
		inner join tokensequenceuses tsu on tsu.tokensequenceid = ts.id
		inner join cmds c on ts.cmdid = c.id
	where ts.value like '%:81%'
	--where ts.value like '%slattery%' and ts.value like '%frances%' and ts.value like '%computer%' and ts.value like '%info%' and ts.value like '%docx%'
	--where ts.value like '%rci%'
	--order by c.typeid, c.id, tsu.freqrankcurrent desc, tsu.lastuse desc
	order by tsu.freqrankcurrent desc, tsu.lastuse desc
	;
--select * from tmp_TestQuery1
*/

-- **********************************************************************************************
-- MANUAL DATA CORRECTION
-- **********************************************************************************************

/*
select 
	c.id as cmdid
	, ts.id as tokensequenceid
	, ts.value
	, tsu.*
from cmds c
	inner join tokensequences ts on ts.cmdid = c.id
	inner join TokenSequenceUses tsu on tsu.TokenSequenceID = ts.id
where c.id = 481057;
*/

--update TokenSequenceUses set charoffset = 15 where tokensequenceid = 132731 and charoffset = 11;
			




------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------

-- MAIN QUERY BETWEEN CMDS AND TOKEN SEQUENCES
select
	c.id as c_id
	, c.typeid as c_typeid
	, c.pathcmdtypeid as c_pathcmdtypeid
	, ts.id as ts_id
	, ts.value
	, ts.typeid as ts_typeid
	, ts.IsCustomUserValue
	, tsu.charoffset
	, tsu.lastuse
	, tsu.freqrankalltime
	, tsu.freqrankcurrent
from tokensequences ts
	left join tokensequenceuses tsu on tsu.tokensequenceid = ts.id
	left join cmds c on c.id = ts.cmdid
--where ts.value like 'd:\craig\test%' and ts.value not like 'd:\craig\test f%'
--where ts.value like 'h:\temp\test_folder_rename%'
--where lower(ts.value) like '%\blah' and c.typeid = 3
--where cmdid in (481062,1720408)
--where ts.value = 'https://docs.google.com/spreadsheets/d/1PgwczKmCgwHlm3415Iv9B3F6VBpfo-whiruR79cF5FE/edit?usp=sharing'
--where ts.value = 'Craig''s Home Wish List - Google Sheets'
--where tsu.lastuse is not null
where c.typeid = 15 and ts.value = 'computer management'
ORDER BY ts.value

cmdid = 1718093
'https://docs.google.com/spreadsheets/d/1PgwczKmCgwHlm3415Iv9B3F6VBpfo-whiruR79cF5FE/edit?usp=sharing'
'Craig''s Home Wish List - Google Sheets'

------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------

select tsu.*, ts.value, substr(ts.value, tsu.charoffset)
from tokensequenceuses tsu
	inner join tokensequences ts on ts.id = tsu.tokensequenceid
where tsu.tokensequenceid = 6523
--where tsu.lastuse = '0001-01-01 00:00:00.000'
	--OR tsu.CharOffset > length(Value)
--order by tsu.charoffset
order by tsu.lastuse desc

DELETE 
--SELECT count(*)
FROM tokensequenceuses
where lastuse = '0001-01-01 00:00:00.000' OR CharOffset > (SELECT LENGTH(Value) FROM tokensequences WHERE id = tokensequenceuses.tokensequenceid)

select count(*) from tokensequenceuses


select * from indexedlocations

SELECT * FROM sqlite_schema WHERE name = 'TokenSequenceUses';
PRAGMA table_xinfo('TokenSequenceUses');
select * from SchemaVersion

select * from cmds where id = 56
update cmds set datecached = '0001-01-01 00:00:00.000' where id = 467479

select sqlite_version();

SELECT c.ID, c.MasterID, ts.Value, c.Flags, c.DateLastSeen 
							FROM Cmds c
								INNER JOIN TokenSequences ts on ts.CmdID = c.ID
							WHERE c.TypeID IN (3,18)

select c.id 
from cmds c
	inner join tokensequences ts on ts.cmdid = c.id
where ts.typeid = 1
group by c.id
having count(ts.id) > 1

SELECT c.id, c.flags, ts.value
FROM cmds c
	INNER JOIN tokensequences ts ON ts.cmdid = c.id
WHERE ts.value LIKE 'h:\%rename%'
	AND c.typeid = 3

SELECT c.id, c.flags, ts.id as ts_id, ts.value, cp.id as cp_id, cp.typeid as cp_typeid, tsp.id as tsp_id, tsp.value as ParentTS
FROM cmds c
	INNER JOIN tokensequences ts ON ts.cmdid = c.id
	inner join cmds cp on cp.id = c.parentid
	inner join tokensequences tsp on tsp.cmdid = cp.id
WHERE c.typeid = 6
	and c.hardcmdid != -1
order by cp.typeid

select flags from cmds where id = 448

SELECT c.id as c_id, c.flags as c_flags, c.datecached, c.historytimestamp, ts.*, tsu.*
FROM cmds c
	INNER JOIN tokensequences ts ON ts.cmdid = c.id
	LEFT JOIN tokensequenceuses tsu on tsu.tokensequenceid = ts.id
WHERE c.id = 177

UPDATE CMDS SET DateLastSeen = '0001-01-01 00:00:00.000' WHERE DateLastSeen != '0001-01-01 00:00:00.000' and Flags & 131072 = 131072;
INSERT OR IGNORE INTO TokenSequenceTypes (ID, Name) VALUES (1, 'Primary');
INSERT OR IGNORE INTO TokenSequenceTypes (ID, Name) VALUES (2, 'Tag');
INSERT OR IGNORE INTO TokenSequenceTypes (ID, Name) VALUES (3, 'Title'); 
DROP INDEX IF EXISTS Cmds_Idx_Value; 
ALTER TABLE Cmds DROP COLUMN [Value];
ALTER TABLE Cmds DROP DefaultValue;



select * from tokensequences where value like 'magnet:%'
update tokensequences set value = 'magnet:?xt=urn:btih:EC2F548A38A127FD34388285705A8A316FCA7393&dn=Living.With.Yourself.S01.COMPLETE.1080p.WEB.X264-AMRAP%5BTGx%5D&tr=udp%3A%2F%2Ftracker.opentrackr.org%3A1337&tr=udp%3A%2F%2Ftracker.openbittorrent.com%3A6969%2Fannounce&tr=udp%3A%2F%2Fopen.stealth.si%3A80%2Fannounce&tr=udp%3A%2F%2Ftracker.torrent.eu.org%3A451%2Fannounce&tr=udp%3A%2F%2Ftracker.bittor.pw%3A1337%2Fannounce&tr=udp%3A%2F%2Fpublic.popcorn-tracker.org%3A6969%2Fannounce&tr=udp%3A%2F%2Ftracker.dler.org%3A6969%2Fannounce&tr=udp%3A%2F%2Fexodus.desync.com%3A6969&tr=udp%3A%2F%2Fopentracker.i2p.rocks%3A6969%2Fannounce'
where id = 163617

select id, typeid, value, displayname, defaultvalue from cmds 
--where length(displayname) > 0 
where length(defaultvalue) > 0


--======================================================================================================================
-- Remove a token sequence.

select * from tokensequences where value like '%desktop\%1password%';

delete from tokensequences where id = 179;
delete from tokensequenceuses where tokensequenceid not in (select id from tokensequences);
--======================================================================================================================



--======================================================================================================================
-- Check Cmd<->TokenSequences data integrity

select * from cmds where id not in (select cmdid from tokensequences);
select * from tokensequences where cmdid not in (select id from cmds);

delete from cmds where id not in (select cmdid from tokensequences);
delete from tokensequences where cmdid not in (select id from cmds);
delete from tokensequenceuses where tokensequenceid not in (select id from tokensequences);
--======================================================================================================================



CREATE TABLE [TokenSequences2] (
	[ID] integer PRIMARY KEY NOT NULL
	, [Value] text NOT NULL COLLATE NOCASE
	, [TypeID] integer NOT NULL
	, [CmdID] integer NOT NULL
	, CONSTRAINT Value_CmdID_Unique UNIQUE (Value, CmdID));
INSERT INTO TokenSequences2 SELECT * FROM TokenSequences;
DROP TABLE TokenSequences;
ALTER TABLE TokenSequences2 RENAME TO TokenSequences;
CREATE INDEX TokenSequences_Idx_CmdID ON TokenSequences(CmdID);

drop index sqlite_autoindex_TokenSequences_1

create table TokenSequences (
	ID integer primary key not null,
	Value text not null collate nocase,
	CmdID integer not null,
	constraint Value_CmdID_Unique unique (value, cmdid)
);

select * from tokensequences
select * from tokensequences where value like '%BOOKMARKS'
delete from tokensequences


------------------------------------------------------------------------------------------------------------------------
-- Cleaning up Duplicates from Contracted Paths

with cte as (
	select
		c.typeid
		, ts.cmdid
		, ts.id as ts_id
		, ts.value
		, tsu.charoffset
		, tsu.lastuse
		, tsu.freqrankalltime
		, tsu.freqrankcurrent
	from tokensequences ts
		left join tokensequenceuses tsu on tsu.tokensequenceid = ts.id
		left join cmds c on c.id = ts.cmdid
	where (ts.value like 'w:\%' or ts.value like 'n:\%' or ts.value like 'o:\%' or ts.value like 'p:\%' or ts.value like 'r:\%' or ts.value like 't:\%') and c.typeid = 3
	--where ts.value like 'c:\users\craig\desktop\%'
	--order by c.typeid desc
)
delete from cmds where id in (select cmdid from cte);

delete from tokensequences where cmdid not in (select id from cmds);
delete from tokensequenceuses where tokensequenceid not in (select id from tokensequences);

select * from tokensequences where cmdid not in (select id from cmds);

--delete
--select *
from cmds where parentid != -1 and parentid not in (select id from cmds)

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------
-- Cleaning up Audio Devices

delete
--select *
from cmds where id not in (select cmdid from tokensequences);

delete from cmds where masterid in (select id from cmds where typeid = 22);

delete
--select *
from tokensequences where cmdid not in (select id from cmds);

delete from tokensequenceuses where tokensequenceid not in (select id from tokensequences);

delete
--select *
from cmds where parentid != -1 and parentid not in (select id from cmds);



select * from cmds where id not in (select cmdid from tokensequences);
select * from tokensequences where cmdid not in (select id from cmds);

------------------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------------------
-- Cleaning up UNC's

delete
--select *
from cmds where id not in (select cmdid from tokensequences) and typeid = 23

delete from tokensequenceuses where tokensequenceid not in (select id from tokensequences)

select * from tokensequences where cmdid not in (select id from cmds)

create table TokenSequences (
	ID integer PRIMARY KEY NOT NULL,
	Value text not null collate nocase,
	TypeID integer not null,
	CmdID integer not null,
	constraint Value_CmdID_Unique unique (Value, CmdID)
);

drop table tokensequences

select * from tokensequences

insert into tokensequences (value, typeid, cmdid) values ('C:\temp\blah', 1, 1);
insert into tokensequences (value, typeid, cmdid) values ('c:\temp\blah', 1, 1);

select * from tokensequences where value = 'c:\temp\blah'




delete from cmds where id = 481062

-- BIG LEFT JOIN.
SELECT 
c1.ID, c1.HardCmdID, c1.TypeID, c1.Flags, c1.DateCached, c1.DateLastSeen, c1.ParentID, c1.PathCmdTypeID, c1.LastUse, c1.Value, c1.DefaultValue, c1.Args, c1.FamilyIconID, c1.Offset, c1.Pidl, c1.PidlNumBytes, c1.Example, c1.QueryName, c1.WorkingDir, c1.FirefoxBookmarkID, c1.FreqRankAllTime, c1.FreqRankCurrent, c1.HistoryTimestamp, c1.DisplayName, c1.ShowCmd, c1.SingleInstance 
,ts1.Value as [ts_Value] 
,c2.ID [ID2], c2.HardCmdID [HardCmdID2], c2.TypeID [TypeID2], c2.Flags [Flags2], c2.DateCached [DateCached2], c2.DateLastSeen [DateLastSeen2], c2.ParentID [ParentID2], c2.PathCmdTypeID [PathCmdTypeID2], c2.LastUse [LastUse2], c2.Value [Value2], c2.DefaultValue [DefaultValue2], c2.Args [Args2], c2.FamilyIconID [FamilyIconID2], c2.Offset [Offset2], c2.Pidl [Pidl2], c2.PidlNumBytes [PidlNumBytes2], c2.Example [Example2], c2.QueryName [QueryName2], c2.WorkingDir [WorkingDir2], c2.FirefoxBookmarkID [FirefoxBookmarkID2], c2.FreqRankAllTime [FreqRankAllTime2], c2.FreqRankCurrent [FreqRankCurrent2], c2.HistoryTimestamp [HistoryTimestamp2], c2.DisplayName [DisplayName2], c2.ShowCmd [ShowCmd2], c2.SingleInstance [SingleInstance2] 
,ts2.Value as [ts_Value2] 
,c3.ID [ID3], c3.HardCmdID [HardCmdID3], c3.TypeID [TypeID3], c3.Flags [Flags3], c3.DateCached [DateCached3], c3.DateLastSeen [DateLastSeen3], c3.ParentID [ParentID3], c3.PathCmdTypeID [PathCmdTypeID3], c3.LastUse [LastUse3], c3.Value [Value3], c3.DefaultValue [DefaultValue3], c3.Args [Args3], c3.FamilyIconID [FamilyIconID3], c3.Offset [Offset3], c3.Pidl [Pidl3], c3.PidlNumBytes [PidlNumBytes3], c3.Example [Example3], c3.QueryName [QueryName3], c3.WorkingDir [WorkingDir3], c3.FirefoxBookmarkID [FirefoxBookmarkID3], c3.FreqRankAllTime [FreqRankAllTime3], c3.FreqRankCurrent [FreqRankCurrent3], c3.HistoryTimestamp [HistoryTimestamp3], c3.DisplayName [DisplayName3], c3.ShowCmd [ShowCmd3], c3.SingleInstance [SingleInstance3] 
,ts3.Value as [ts_Value3] 
,c4.ID [ID4], c4.HardCmdID [HardCmdID4], c4.TypeID [TypeID4], c4.Flags [Flags4], c4.DateCached [DateCached4], c4.DateLastSeen [DateLastSeen4], c4.ParentID [ParentID4], c4.PathCmdTypeID [PathCmdTypeID4], c4.LastUse [LastUse4], c4.Value [Value4], c4.DefaultValue [DefaultValue4], c4.Args [Args4], c4.FamilyIconID [FamilyIconID4], c4.Offset [Offset4], c4.Pidl [Pidl4], c4.PidlNumBytes [PidlNumBytes4], c4.Example [Example4], c4.QueryName [QueryName4], c4.WorkingDir [WorkingDir4], c4.FirefoxBookmarkID [FirefoxBookmarkID4], c4.FreqRankAllTime [FreqRankAllTime4], c4.FreqRankCurrent [FreqRankCurrent4], c4.HistoryTimestamp [HistoryTimestamp4], c4.DisplayName [DisplayName4], c4.ShowCmd [ShowCmd4], c4.SingleInstance [SingleInstance4] 
,ts4.Value as [ts_Value4] 
,c5.ID [ID5], c5.HardCmdID [HardCmdID5], c5.TypeID [TypeID5], c5.Flags [Flags5], c5.DateCached [DateCached5], c5.DateLastSeen [DateLastSeen5], c5.ParentID [ParentID5], c5.PathCmdTypeID [PathCmdTypeID5], c5.LastUse [LastUse5], c5.Value [Value5], c5.DefaultValue [DefaultValue5], c5.Args [Args5], c5.FamilyIconID [FamilyIconID5], c5.Offset [Offset5], c5.Pidl [Pidl5], c5.PidlNumBytes [PidlNumBytes5], c5.Example [Example5], c5.QueryName [QueryName5], c5.WorkingDir [WorkingDir5], c5.FirefoxBookmarkID [FirefoxBookmarkID5], c5.FreqRankAllTime [FreqRankAllTime5], c5.FreqRankCurrent [FreqRankCurrent5], c5.HistoryTimestamp [HistoryTimestamp5], c5.DisplayName [DisplayName5], c5.ShowCmd [ShowCmd5], c5.SingleInstance [SingleInstance5] 
,ts5.Value as [ts_Value5] 
FROM Cmds c1 
 INNER JOIN TokenSequences ts1 on ts1.CmdID = c1.ID 
 LEFT JOIN Cmds c2 on c1.ParentID != -1 and c1.ParentID = c2.ID 
 LEFT JOIN TokenSequences ts2 on c1.ParentID != -1 AND ts2.CmdID = c2.ID 
 LEFT JOIN Cmds c3 on c2.ParentID != -1 and c2.ParentID = c3.ID 
 LEFT JOIN TokenSequences ts3 on c2.ParentID != -1 AND ts3.CmdID = c3.ID 
 LEFT JOIN Cmds c4 on c3.ParentID != -1 and c3.ParentID = c4.ID 
 LEFT JOIN TokenSequences ts4 on c3.ParentID != -1 AND ts4.CmdID = c4.ID 
 LEFT JOIN Cmds c5 on c4.ParentID != -1 and c4.ParentID = c5.ID 
 LEFT JOIN TokenSequences ts5 on c3.ParentID != -1 AND ts5.CmdID = c5.ID 
 WHERE ts1.value REGEXP '.+\\[^\\]*craig[^\\]*$'
	and ts1.value REGEXP '.+\\[^\\]*silver[^\\]*$'
--group by ts.id, tsu.charoffset
	--having group_concat(ts.value, char(6)) REGEXP '.+\\[^\\]*craig[^\\]*$' and group_concat(ts.value, char(6)) REGEXP '.+\\[^\\]*silver[^\\]*$'  order by ts.cmdid, ts.id, tsu.charoffset;
	--having group_concat(ts.value, char(6)) LIKE '%craig%' and group_concat(ts.value, char(6)) LIKE '%silver%' 
--and not (ts.value REGEXP '^[a-z]:\\.*[craig|silver].*\\[^\\]+$')

---------------------------------------------------------------------------------------------------------------------------------
-- DON's database cleanup

select c.id as c_id, c.value as c_valud, p.id as p_id, p.value as p_value, ts_c.value as ts_c_value, ts_p.value as ts_p_value
from cmds c
	inner join cmds p on p.id = c.parentid
	left join tokensequences ts_c on ts_c.cmdid = c.id
	left join tokensequences ts_p on ts_p.cmdid = p.id
where c.typeid = 6 and p.typeid = 11

delete 
--select *
from cmds where parentid != -1 and parentid not in (select id from cmds);

delete
--select *
from cmds where id not in (select cmdid from tokensequences);

delete
--select *
from tokensequences where cmdid not in (select id from cmds);

delete
--select *
from tokensequenceuses where tokensequenceid not in (select id from tokensequences);


select * from cmds where typeid = 6 order by value

select
distinct
	c1.id as c1_id, c1.parentid as c1_parentid, c1.value as c1_value
	, c2.id as c2_id, c2.parentid as c2_parentid, c2.value as c2_value
from cmds c1
	left join cmds c2 on c1.value = c2.value
where c1.typeid = 6 and c1.hardcmdid != -1 and c2.typeid = 6 and c2.hardcmdid != -1 and c1.id < 1000 and c2.id > 1000

select * from cmds where id = 53

select * from tokensequences where id = 135763

delete
--select * 
from cmds where id in (2878104,2878108,2878105,2878107,2878106,2878110,2878113,2878115,2878117)

update cmds set parentid = 2878109 where id = 389;
delete from cmds where id = 2878110;
delete from tokensequences where cmdid = 2878110;

delete from tokensequenceuses where tokensequenceid not in (select id from tokensequences);

---------------------------------------------------------------------------------------------------------------------------------




--===========================================================================================================
--
-- TOKEN SEQUENCES
--
--===========================================================================================================

select * from tokensequences where value like '%nasa%'
select * from tokensequences order by id desc
select * from tokens where tokensequenceid = 1554
select * from TokenSequenceUses

---------------------
-- Checking for dupes
---------------------

-- TokenSequences
with cte as (
	select value, cmdid, count(id)
	from tokensequences
	group by value, cmdid
	having count(id) > 1
) select count(*) from cte

-- Tokens
with cte as (
	select count(t.id), t.value as t_value, t.charindex as t_charindex, ts.value as ts_value
	from tokens t
		inner join tokensequences ts on ts.id = t.tokensequenceid
	group by t.value, ts.id, ts.value, t.charindex
	having count(t.id) > 1
) select count(*) from cte
--

SELECT ts.id as ts_id, ts.value as ts_value, ts.typeid, ts.isprimary, t.id as t_id, t.value as t_value, t.charoffset
FROM TokenSequences ts INNER JOIN Tokens t on t.TokenSequenceID = ts.ID WHERE ts.CmdID = 2272 ORDER BY ts.ID;

create table [TokenSequenceTypes] (
	[ID] integer PRIMARY KEY NOT NULL
	, Name text NOT NULL UNIQUE
);
insert into TokenSequenceTypes (Name) VALUES ('Tag');
INSERT OR IGNORE INTO TokenSequenceTypes (Name) VALUES ('Tag');
select * from tokensequencetypes
--delete from tokensequencetypes
PRAGMA table_info('tokensequenceuses');
INSERT INTO TokenSequenceTypes (Name) VALUES ('Title');

create table [TokenSequences] (
	[ID] integer PRIMARY KEY NOT NULL
	, Value text NOT NULL
	, TypeID integer NOT NULL
	, CmdID integer NOT NULL
);
with cte as (select id from cmds where typeid = 5 and value = 'https://craigsilver.ca/Host#Access/All%20Machines//bf36d742-6e91-4a26-95ee-97e84597553f')
insert into TokenSequences (Value, TypeID, CmdID) VALUES ('remote-access', 1, (select id from cte));
select * from tokensequences
--drop table tokensequences
PRAGMA table_info('tokensequences');
.schema tokensequences

create table [Tokens] (
	[ID] integer PRIMARY KEY NOT NULL
	, Value text NOT NULL
	, TokenIndex integer NOT NULL
	, CharIndex integer NOT NULL
	, LeftDelims text NOT NULL DEFAULT ''
	, RightDelims text NOT NULL DEFAULT ''
	, TokenSequenceID integer NOT NULL
	, CmdID integer NOT NULL
);
with cte as (select id, cmdid from tokensequences where value = 'remote-access')
insert into tokens (Value, CharOffset, TokenSequenceID, CmdID) VALUES ('remote', 0, (select id from cte), (select cmdid from cte));
with cte as (select id, cmdid from tokensequences where value = 'remote-access')
insert into tokens (Value, CharOffset, TokenSequenceID, CmdID) VALUES ('access', 7, (select id from cte), (select cmdid from cte));
select * from tokens
--drop table tokens
--delete from tokens
PRAGMA table_info('tokens');

select * from cmds where typeid in (2,4,6,7,16,17,20,21) order by typeid
select * from cmds where (typeid = 8 and offset = 0 and masterid not in (select id from cmds where typeid = 12)) or (typeid = 9 and offset in (7,8)) order by typeid


--===========================================================================================================
--===========================================================================================================

select * from cmds where masterid in (select id from cmds where value like '\\localhost%')

select * from IndexedLocationExclusions
select * from cmds where masterid in (select id from cmds where typeid = 3 and value like 'c:\users\craig\desktop\test\%')
select * from cmds where id = 580208

with cte as (select id from cmds where typeid = 3 and value like 'c:\users\craig\desktop\test\%')
, cte2 as (select id from cmds where masterid in (select id from cte))
--select * from cte
select * from cmduselog where cmdid in (select id from cte2)
select id from cte union select id from cmds where typeid = 8 and masterid in (select id from cte)

WITH cte as (SELECT ID FROM Cmds WHERE TypeID = 3 AND Value LIKE 'C:\Users\Craig\Desktop\Test\%') 
SELECT ID from cte UNION SELECT ID FROM Cmds WHERE MasterID IN (SELECT ID FROM cte);


WITH cte as (SELECT ID FROM Cmds WHERE TypeID = 3 AND Value LIKE 'C:\Users\Craig\Desktop\Test\%') 
SELECT ID from cte UNION SELECT ID FROM Cmds WHERE TypeID = 8 AND ParentID IN (SELECT ID FROM cte);

WITH cte as (SELECT ID FROM Cmds WHERE TypeID = 3 AND Value LIKE 'C:\Users\Craig\Desktop\Test\%') SELECT ID from cte ;

--===========================================================================================================
--===========================================================================================================
-- Steps to de-index a folder's contents from cache and db, but not the folder itself.

-- Ask db for ID's of all cmds to remove.
select id from cmds where masterid in (select id from cmds where typeid = 3 and value like 'D:\Craig\projects\UpTime\06\res\%') order by masterid, typeid

-- In code, remove all cmds with above ID's.

delete from cmds where id in ( {above-list} )
delete from cmduselog where cmdid in ( {above-list} )

--===========================================================================================================
--===========================================================================================================

select * from cmds where masterid in (select id from cmds where typeid = 3 and value like 'D:\Craig\projects\UpTime\06\res\%') order by masterid, typeid
--delete from IndexedLocationExclusions where id = 4

select * from cmds where masterid = 398556
select id, typeid, lastuse, datecached, value, freqrankalltime from cmds where masterid = 2150

select * from cmds where value like '%serve%' and typeid in (5,6)

select * from cmds where typeid = 22
--delete from cmds where masterid in (select id from cmds where typeid = 22)
select * from cmds where value like '%8-bit Computer Emulators'

select count(*) from cmds where datecached != '0001-01-01 00:00:00.000'
select id from cmds where datecached != '0001-01-01 00:00:00.000'

--================================================
-- Missing Externals
--================================================
select * from cmds where (flags & 131072) = 131072
select * from cmds where typeid = 3 and (flags & 131072) = 131072 order by value
select * from cmds where id = 456335

delete from cmds where id in (select id from cmds where masterid in (select id from cmds where typeid = 3 and value like 'q:\%'))

delete from cmduselog where cmdid not in (select id from cmds)

vacuum

CREATE TEMP TABLE t AS 
select id, value
from cmds 
where value = 'd:\craig\Adobe\Lightroom'
;
insert into t values (select id, value from cmds where value = 'd:\craig');
--select * from t;


WITH RECURSIVE split(s, last, rest) AS (
  select '', '', value from cmds where typeid = 3 and value = 'd:\craig\Adobe\Lightroom'
  UNION ALL
  SELECT s || substr(rest, 1, 1),
         substr(rest, 1, 1),
         substr(rest, 2)
  FROM split
  WHERE rest <> ''
)
SELECT distinct s
FROM split
WHERE rest = ''
   OR last = '\';

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
SELECT * FROM sqlite_master WHERE type='index' ORDER BY name;

PRAGMA table_info('cmds');

select * from payments

select count(*) from cmds

select count(*) from account_email_settings
select count(*) from accounts
select count(*) from clients
select count(*) from invoices

select c.name, i.invoice_number, ii.*, p.payment_date, p.amount, p.payment_type_id
from invoice_items ii
	inner join invoices i on i.id = ii.invoice_id
	inner join clients c on i.client_id = c.id
	inner join contacts ct on ct.client_id = c.id
	left join payments p on p.invoice_id = i.id
where ct.last_name = 'Hamilton'
	and i.invoice_number = '0354'
	--and lower(notes) like '%garmin%'
order by created_at, invoice_id

select * from contacts

--delete from indexedlocations;
--delete from tasks;

--update indexedlocations set enabled = 0;

select * from indexedlocations
select count(*) from cmds

SELECT * FROM sqlite_master where type = 'table';

with cte as (
						select (c.flags & 14) >> 1 as CmdRank, c.id, c.typeid, c.value, c.lastuse, c.freqrankcurrent, c.datecached, p.value as p_value, p.typeid as p_typeid
						from cmds c
							left join cmds p on c.parentid = p.id
						where c.typeid in (5,11,6,7,11)
					)
					select * from cte
					order by id
										

select * from cmds where value like '%dad''s computer%' and typeid = 3 order by lastuse desc
select * from cmds where value like '%chat%' and typeid = 5 order by lastuse desc
select * from cmds where value like '%private%' and typeid = 6 order by lastuse desc
select * from cmds where value like '%visual studio%' and typeid = 7 order by lastuse desc
select * from cmds where value like '%speaker%' and typeid not in (3,8) order by lastuse desc
select * from cmds where value like '%theyfit%' 
select * from cmds where id = 2686752
select * from cmds where masterid in (select id from cmds where typeid = 17) order by masterid
select count(*) from cmds
update cmds set freqrankcurrent = 0 where id = xxx