using namespace System.Xml
Add-Type -AssemblyName PresentationFramework
[Reflection.Assembly]::LoadFile("C:\Program Files\sqlite-netFx46-binary-x64-2015-1.0.118.0\System.Data.SQLite.dll")

#region SQLITE CONNECT
$databasePath = "C:\Devs\bash\TODOv3.sqlite3"
$dbConnectionString = "Data Source=$databasePath;Version=3;"
$Global:connection = New-Object System.Data.SQLite.SQLiteConnection($dbConnectionString)
#endregion

#region DESIGNER
[xml]$xaml = '
<Window xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="476" Width="800">
<Grid>
    <TabControl Margin="10,10,460,21">
        <TabItem Header="TabItem">
            <Grid x:Name="taskListPanel" Background="#FFE5E5E5">
                
            </Grid>
        </TabItem>
        <TabItem Header="TabItem">

        </TabItem>
    </TabControl>
    <Label x:Name="title1" Content="Label" HorizontalAlignment="Right" Margin="0,33,10,0" Width="432" Background="#FFC1FFED" Height="77" VerticalAlignment="Top" />
    <Label x:Name="content1" Content="Content" HorizontalAlignment="Right" Margin="0,148,10,21" Width="432" Background="#FFE3FFAD"/>

</Grid>
</Window>
'
[xml]$ucxaml = '
<UserControl
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    x:Name="Page1">
        <Grid Height="55" Margin="8,17,8,0" VerticalAlignment="Top" Background="White">
                    <Label x:Name="lb1" Content="Label 1" Height="35" Margin="10,0,10,0" VerticalAlignment="Center"/>
         </Grid>
</UserControl> '

$window = [Windows.Markup.XamlReader]::Load((New-Object XmlNodeReader $xaml))
$taskNode =[Windows.Markup.XamlReader]::Load((New-Object XmlNodeReader $ucxaml))
#endregion

#region INIT
$connection.Open()

$command = $connection.CreateCommand()
$allTaskRead = "SELECT tid,title,uid,mtid FROM task"
$command = New-Object System.Data.SQLite.SQLiteCommand($allTaskRead, $connection)
$result = $command.ExecuteReader()

$Global:gridMain = $window.FindName("taskListPanel")

$taskNodeList = @()

while($result.Read()){
    $t1 = [Windows.Markup.XamlReader]::Load((New-Object XmlNodeReader $ucxaml))
    $t1.Tag = $result["tid"]
    $t1.add_MouseLeftButtonUp({ readDetail($this.Tag) })
    $taskNodeList += $t1
}

foreach($gNode in $taskNodeList) {
    $gridMain.AddChild($gNode)
}

$connection.Close()
#endregion

#region ACTION
function readDetail($tid)
{
    $connection.Open()
    $query = ("SELECT task.tid,sid,stime FROM task LEFT JOIN status ON task.tid=status.tid WHERE task.tid = ",$tid,"ORDER BY stime", -join  ' ')    
    $command = New-Object System.Data.SQLite.SQLiteCommand($query, $connection)
    $reader = $command.ExecuteReader()
    $txtDetail.Content =""
    while($reader.Read()){
        $txtDetail.Content = ( $txtDetail.Content,$reader["sid"],$reader["stime"] -join '

')
    }
    $connection.Close()
}


$txtTitle = $window.FindName("title1")

$txtDetail = $window.FindName("content1")
#endregion

$window.ShowDialog()

CREATE TABLE meta( 
  "k" TEXT NOT NULL
  , "v" TEXT NOT NULL
  , PRIMARY KEY ("k")
);

CREATE TABLE status( 
  "sid" INT NOT NULL
  , "tid" INT NOT NULL
  , "mtid" TEXT NOT NULL
  , "msg" TEXT
  , "stime" TEXT NOT NULL
  , PRIMARY KEY ("sid")
);

CREATE TABLE task( 
  "tid" INT NOT NULL
  , "title" TEXT NOT NULL
  , "uid" INT
  , "mtid" TEXT NOT NULL
  , PRIMARY KEY ("tid")
);

CREATE TABLE user ( 
  "uid" TEXT NOT NULL
  , "name" TEXT NOT NULL
  , PRIMARY KEY ("uid")
);