#!/usr/bin/env perl
use strict;
use warnings;
use lib 'lib';
use feature 'say';
use SQL::Engine;
my $sql = SQL::Engine->new(
  grammar => 'sqlite',
  validator => undef,
);
# select operations
# * select star
#
# SELECT * FROM users
$sql->select(
  from => {
    table => 'users'
  },
  columns => [
    {
      column => '*'
    }
  ]
);
say $sql->operations->pull->statement;
# * select columns
#
# SELECT id, name, created FROM users
$sql->select(
  from => {
    table => 'users'
  },
  columns => [
    {
      column => 'id'
    },
    {
      column => 'name'
    }
  ]
);
say $sql->operations->pull->statement;
# * select star, where clause
#
# SELECT * FROM users WHERE id = ?
$sql->select(
  from => {
    table => 'users'
  },
  columns => [
    {
      column => '*'
    }
  ],
  where => [
    {
      eq => [{column => 'id'}, {binding => 'id'}]
    }
  ]
);
say $sql->operations->pull->statement;
# * select, join, aliases
#
# SELECT * FROM users u JOIN tasklists t ON u.id = t.user_id WHERE u.id = ?
$sql->select(
  from => {
    table => 'users',
    alias => 'u'
  },
  columns => [
    {
      column => '*',
      alias => 'u'
    }
  ],
  joins => [
    {
      with => {
        table => 'tasklists',
        alias => 't'
      },
      having => [
        {
          eq => [
            {
              column => 'id',
              alias => 'u'
            },
            {
              column => 'user_id',
              alias => 't'
            }
          ]
        }
      ]
    }
  ],
  where => [
    {
      eq => [
        {
          column => 'id',
          alias => 'u'
        },
        {
          binding => 'id'
        }
      ]
    }
  ]
);
say $sql->operations->pull->statement;
# * select column, aggrate, group-by
#
# SELECT count(user_id) FROM tasklists GROUP BY user_id
$sql->select(
  from => {
    table => 'tasklists'
  },
  columns => [
    {
      function => ['count', { column => 'user_id' }]
    }
  ],
  group_by => [
    {
      column => 'user_id'
    }
  ]
);
say $sql->operations->pull->statement;
# * select column, aggrate, group-by, having
#
# SELECT count(user_id) FROM tasklists GROUP BY user_id HAVING count(user_id) > 1
$sql->select(
  from => {
    table => 'tasklists'
  },
  columns => [
    {
      function => ['count', { column => 'user_id' }]
    }
  ],
  group_by => [
    {
      column => 'user_id'
    }
  ],
  having => [
    {
      gt => [
        {
          function => ['count', { column => 'user_id' }]
        },
        1
      ]
    }
  ]
);
say $sql->operations->pull->statement;
# * select star, order-by
#
# SELECT * FROM tasklists ORDER BY user_id
$sql->select(
  from => {
    table => 'tasklists'
  },
  columns => [
    {
      column => '*'
    }
  ],
  order_by => [
    {
      column => 'user_id'
    }
  ]
);
say $sql->operations->pull->statement;
# * select star, order-by (bi-direction)
#
# SELECT * FROM tasklists ORDER BY user_id, id DESC
$sql->select(
  from => {
    table => 'tasklists'
  },
  columns => [
    {
      column => '*'
    }
  ],
  order_by => [
    {
      column => 'user_id'
    },
    {
      column => 'id',
      sort => 'desc'
    }
  ]
);
say $sql->operations->pull->statement;
# * select star, limit
#
# SELECT * FROM tasks LIMIT 5
$sql->select(
  from => {
    table => 'tasks'
  },
  columns => [
    {
      column => '*'
    }
  ],
  rows => {
    limit => 5
  }
);
say $sql->operations->pull->statement;
# * select star, limit, offset
#
# SELECT * FROM tasks LIMIT 5, 1
$sql->select(
  from => {
    table => 'tasks'
  },
  columns => [
    {
      column => '*'
    }
  ],
  rows => {
    limit => 5,
    offset => 1
  }
);
say $sql->operations->pull->statement;
# * select star, aliases, multiple tables
#
# SELECT t1.*, t2.* FROM tasklists t1, tasks t2 WHERE t2.tasklist_id = t1.id
$sql->select(
  from => [
    {
      table => 'tasklists',
      alias => 't1'
    },
    {
      table => 'tasks',
      alias => 't2'
    }
  ],
  columns => [
    {
      column => '*',
      alias => 't1'
    },
    {
      column => '*',
      alias => 't1'
    }
  ],
  where => [
    {
      eq => [
        {
          column => 'tasklist_id',
          alias => 't2'
        },
        {
          column => 'id',
          alias => 't1'
        }
      ]
    }
  ]
);
say $sql->operations->pull->statement;
# insert operations
# * insert, values only
#
# INSERT INTO users VALUES (NULL, 'Rob Zombie', now(), now(), now())
$sql->insert(
  into => {
    table => 'users'
  },
  values => [
    {
      value => undef
    },
    {
      value => 'Rob Zombie'
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    }
  ]
);
say $sql->operations->pull->statement;
# * insert, columns, values
#
# INSERT INTO users (id, name, created, updated, deleted) VALUES (NULL, 'Rob Zombie', now(), now(), now())
$sql->insert(
  into => {
    table => 'users'
  },
  columns => [
    {
      column => 'id'
    },
    {
      column => 'name'
    },
    {
      column => 'created'
    },
    {
      column => 'updated'
    },
    {
      column => 'deleted'
    }
  ],
  values => [
    {
      value => undef
    },
    {
      value => 'Rob Zombie'
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    },
    {
      value => {
        function => ['now']
      }
    }
  ]
);
say $sql->operations->pull->statement;
# * insert, defaults only
#
# INSERT INTO users DEFAULT VALUES
$sql->insert(
  into => {
    table => 'users'
  },
  default => 1
);
say $sql->operations->pull->statement;
# * insert, select columns
#
# INSERT INTO people (name, user_id) SELECT name, id FROM users
$sql->insert(
  into => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    },
    {
      column => 'user_id'
    }
  ],
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => 'name'
        },
        {
          column => 'id'
        }
      ]
    }
  }
);
say $sql->operations->pull->statement;
# * insert, select columns, where clause
#
# INSERT INTO people (name, user_id) SELECT (name, id) FROM users WHERE deleted IS NOT NULL
$sql->insert(
  into => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    },
    {
      column => 'user_id'
    }
  ],
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => 'name'
        },
        {
          column => 'id'
        }
      ],
      where => [
        {
          'not-null' => {
            column => 'deleted'
          }
        }
      ]
    }
  }
);
say $sql->operations->pull->statement;
# update operations
# * update
#
# UPDATE users SET updated = now()
$sql->update(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'updated',
      value => { function => ['now'] }
    }
  ]
);
say $sql->operations->pull->statement;
# * update, where clause
#
# UPDATE users SET name = cancat('[deleted]', ' ', name) WHERE deleted IS NOT NULL
$sql->update(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name',
      value => { function => ['concat', '[deleted]', ' ', { column => 'name' }] }
    }
  ],
  where => [
    {
      'not-null' => {
        column => 'deleted'
      }
    }
  ]
);
say $sql->operations->pull->statement;
# * update, where subquery
#
# UPDATE users u SET updated = now() WHERE u.id IN (SELECT id FROM users x JOIN tasklists t ON x.id = t.user_id WHERE x.id = ?)
$sql->update(
  for => {
    table => 'users',
    alias => 'u1'
  },
  columns => [
    {
      column => 'updated',
      alias => 'u1',
      value => { function => ['now'] }
    }
  ],
  where => [
    {
      in => [
        {
          column => 'id',
          alias => 'u1'
        },
        {
          subquery => {
            select => {
              from => {
                table => 'users',
                alias => 'u2'
              },
              columns => [
                {
                  column => 'id',
                  alias => 'u2'
                }
              ],
              joins => [
                {
                  with => {
                    table => 'tasklists',
                    alias => 't1'
                  },
                  having => [
                    {
                      eq => [
                        {
                          column => 'id',
                          alias => 'u2'
                        },
                        {
                          column => 'user_id',
                          alias => 't1'
                        }
                      ]
                    }
                  ]
                }
              ],
              where => [
                {
                  eq => [
                    {
                      column => 'id',
                      alias => 'u2'
                    },
                    {
                      binding => 'user_id'
                    }
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  ]
);
say $sql->operations->pull->statement;
# delete operations
# * delete
#
# DELETE FROM tasklists
$sql->delete(
  from => {
    table => 'tasklists'
  }
);
say $sql->operations->pull->statement;
# * delete, where clause
#
# DELETE FROM tasklists WHERE deleted IS NOT NULL
$sql->delete(
  from => {
    table => 'tasklists'
  },
  where => [
    {
      'not-null' => {
        column => 'deleted'
      }
    }
  ]
);
say $sql->operations->pull->statement;
# column-change operations
# * change type, nullable
#
# ALTER TABLE users ALTER COLUMN accessed datetime NULL
$sql->column_change(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    nullable => 1
  }
);
while (my $op = $sql->operations->pull) {
  say $op->statement;
}
# * change type, default
#
# ALTER TABLE users ALTER COLUMN accessed datetime DEFAULT now()
$sql->column_change(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    default => { function => ['now'] }
  }
);
while (my $op = $sql->operations->pull) {
  say $op->statement;
}
# * change type, nullable default
#
# ALTER TABLE users ALTER COLUMN accessed datetime SET NULL DEFAULT now()
$sql->column_change(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    default => { function => ['now'] },
    nullable => 1,
  }
);
while (my $op = $sql->operations->pull) {
  say $op->statement;
}
# column-create operations
# * column type
#
# ALTER TABLE users ADD COLUMN accessed datetime
$sql->column_create(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime'
  }
);
say $sql->operations->pull->statement;
# * column type, nullable
#
# ALTER TABLE users ADD COLUMN accessed datetime NULL
$sql->column_create(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    nullable => 1
  }
);
say $sql->operations->pull->statement;
# * column type, nullable, default
#
# ALTER TABLE users ADD COLUMN accessed datetime NULL DEFAULT now()
$sql->column_create(
  for => {
    table => 'users'
  },
  column => {
    name => 'accessed',
    type => 'datetime',
    nullable => 1,
    default => {
      function => ['now']
    }
  }
);
say $sql->operations->pull->statement;
# * column type, nullable, primary key
#
# ALTER TABLE users ADD COLUMN ref uuid PRIMARY KEY
$sql->column_create(
  for => {
    table => 'users'
  },
  column => {
    name => 'ref',
    type => 'uuid',
    primary => 1
  }
);
say $sql->operations->pull->statement;
# column-drop operations
# * column drop
#
# ALTER TABLE users DROP COLUMN accessed
$sql->column_drop(
  table => 'users',
  column => 'accessed'
);
say $sql->operations->pull->statement;
# column-rename operations
# * column rename
#
# ALTER TABLE users RENAME COLUMN accessed TO accessed_at
$sql->column_rename(
  for => {
    table => 'users'
  },
  name => {
    old => 'accessed',
    new => 'accessed_at'
  }
);
say $sql->operations->pull->statement;
# constraint-create operations
# * constraint
#
# ALTER TABLE users ADD CONSTRAINT foreign_users_profile_id (profile_id) REFERENCES profiles (id)
$sql->constraint_create(
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  }
);
say $sql->operations->pull->statement;
# * named constraint
#
# ALTER TABLE users ADD CONSTRAINT user_profile_id (profile_id) REFERENCES profiles (id)
$sql->constraint_create(
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'
);
say $sql->operations->pull->statement;
# * named constraint, on update, on delete
#
# ALTER TABLE ADD CONSTRAINT user_defined_name (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE
$sql->constraint_create(
  on => {
    update => 'cascade',
    delete => 'cascade'
  },
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'
);
say $sql->operations->pull->statement;
# constraint-drop operations
# * constraint
#
# ALTER TABLE DROP CONSTRAINT generated
$sql->constraint_drop(
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  }
);
say $sql->operations->pull->statement;
# * named constraint
#
# ALTER TABLE DROP CONSTRAINT user_defined_name
$sql->constraint_drop(
  source => {
    table => 'users',
    column => 'profile_id'
  },
  target => {
    table => 'profiles',
    column => 'id'
  },
  name => 'user_profile_id'
);
say $sql->operations->pull->statement;
# database-create operations
# * database create
#
# CREATE DATABASE todoapp
$sql->database_create(
  name => 'todoapp'
);
say $sql->operations->pull->statement;
# database-drop operations
# * database drop
#
# DROP DATABASE todoapp
$sql->database_drop(
  name => 'todoapp'
);
say $sql->operations->pull->statement;
# index-create operations
# * index
#
# CREATE INDEX index_users_email ON users (name)
$sql->index_create(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    }
  ]
);
say $sql->operations->pull->statement;
# * unique index
#
# CREATE UNIQUE INDEX unique_users_email ON users (email)
$sql->index_create(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'email'
    }
  ],
  unique => 1
);
say $sql->operations->pull->statement;
# * named index
#
# CREATE INDEX user_name_index ON users (name)
$sql->index_create(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    }
  ],
  name => 'user_name_index'
);
say $sql->operations->pull->statement;
# * named unique index
#
# CREATE UNIQUE INDEX user_email_unique ON users (email)
$sql->index_create(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'email'
    }
  ],
  name => 'user_email_unique',
  unique => 1
);
say $sql->operations->pull->statement;
# * multi-column index
#
# CREATE INDEX index_users_login_email ON users (login, email)
$sql->index_create(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'login'
    },
    {
      column => 'email'
    }
  ]
);
say $sql->operations->pull->statement;
# index-drop operations
# * index
#
# DROP INDEX generated
$sql->index_drop(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    }
  ]
);
say $sql->operations->pull->statement;
# * unique index
#
# DROP INDEX generated
$sql->index_drop(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'email'
    }
  ],
  unique => 1
);
say $sql->operations->pull->statement;
# * named index
#
# DROP INDEX user_defined_name
$sql->index_drop(
  for => {
    table => 'users'
  },
  columns => [
    {
      column => 'name'
    }
  ],
  name => 'user_email_unique'
);
say $sql->operations->pull->statement;
# schema-create operations
# * schema create
#
# CREATE SCHEMA private
$sql->schema_create(
  name => 'private',
);
say $sql->operations->pull->statement;
# schema-drop operations
# * schema drop
#
# DROP SCHEMA private
$sql->schema_drop(
  name => 'private',
);
say $sql->operations->pull->statement;
# schema-rename operations
# * schema rename
#
# ALTER SCHEMA RENAME private TO restricted
$sql->schema_rename(
  name => {
    old => 'private',
    new => 'restricted'
  }
);
say $sql->operations->pull->statement;
# table-create operations
# * table
#
# CREATE TABLE users (id int PRIMARY KEY)
$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    }
  ]
);
say $sql->operations->pull->statement;
# * table, many columns
#
# CREATE TABLE users (id int PRIMARY KEY, name text, created datetime, updated datetime, deleted datetime)
$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    },
    {
      name => 'name',
      type => 'text',
    },
    {
      name => 'created',
      type => 'datetime',
    },
    {
      name => 'updated',
      type => 'datetime',
    },
    {
      name => 'deleted',
      type => 'datetime',
    },
  ]
);
say $sql->operations->pull->statement;
# * table, temporary
#
# CREATE TEMPORARY TABLE users (id int PRIMARY KEY, name text, created datetime, updated datetime, deleted datetime)
$sql->table_create(
  name => 'users',
  columns => [
    {
      name => 'id',
      type => 'integer',
      primary => 1
    },
    {
      name => 'name',
      type => 'text',
    },
    {
      name => 'created',
      type => 'datetime',
    },
    {
      name => 'updated',
      type => 'datetime',
    },
    {
      name => 'deleted',
      type => 'datetime',
    },
  ],
  temp => 1
);
say $sql->operations->pull->statement;
# * table, subquery
#
# CREATE TABLE people AS SELECT * FROM users
$sql->table_create(
  name => 'people',
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => '*'
        }
      ]
    }
  }
);
say $sql->operations->pull->statement;
# table-drop operations
# * table
#
# DROP TABLE users
$sql->table_drop(
  name => 'people'
);
say $sql->operations->pull->statement;
# * table, condition
#
# DROP TABLE users CASCADE
$sql->table_drop(
  name => 'people',
  condition => 'cascade'
);
say $sql->operations->pull->statement;
# table-rename operations
# * table rename
#
# ALTER TABLE peoples RENAME TO people
$sql->table_rename(
  name => {
    old => 'peoples',
    new => 'people'
  }
);
say $sql->operations->pull->statement;
# transaction operations
# * transaction
#
# BEGIN;
# CREATE TABLE ...;
# END;
$sql->transaction(
  queries => [
    {
      'table-create' => {
        name => 'users',
        columns => [
          {
            name => 'id',
            type => 'integer',
            primary => 1
          }
        ]
      }
    }
  ]
);
while (my $op = $sql->operations->pull) {
  say $op->statement;
}
# * transaction mode
#
# BEGIN DEFERRED;
# CREATE TABLE ...;
# END;
$sql->transaction(
  mode => [
    'exclusive'
  ],
  queries => [
    {
      'table-create' => {
        name => 'users',
        columns => [
          {
            name => 'id',
            type => 'integer',
            primary => 1
          }
        ]
      }
    }
  ]
);
while (my $op = $sql->operations->pull) {
  say $op->statement;
}
# view-create operations
# * view
#
# CREATE VIEW active_users AS (SELECT * FROM users WHERE deleted IS NULL)
$sql->view_create(
  name => 'active_users',
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => '*'
        }
      ],
      where => [
        {
          'not-null' => {
            column => 'deleted'
          }
        }
      ]
    }
  }
);
say $sql->operations->pull->statement;
# * view, temporary
#
# CREATE TEMPORARY VIEW active_users AS (SELECT * FROM users WHERE deleted IS NULL)
$sql->view_create(
  name => 'active_users',
  query => {
    select => {
      from => {
        table => 'users'
      },
      columns => [
        {
          column => '*'
        }
      ],
      where => [
        {
          'not-null' => {
            column => 'deleted'
          }
        }
      ]
    }
  },
  temp => 1
);
say $sql->operations->pull->statement;
# view-drop operations
# * view drop
#
# DROP VIEW active_users
$sql->view_drop(
  name => 'active_users'
);
say $sql->operations->pull->statement;