BEGIN;

ALTER TABLE pages ADD COLUMN parent_id uuid;
ALTER TABLE pages ADD COLUMN menu text NOT NULL; 
ALTER TABLE pages ADD COLUMN position integer;

ALTER TABLE pages ADD FOREIGN KEY (parent_id) REFERENCES pages;
ALTER TABLE pages ADD CHECK (position >= 0);

CREATE INDEX ON pages(slug);
CREATE INDEX ON pages(position);

INSERT INTO meta_data (patch) VALUES ('0009_page_relations.sql');

COMMIT;

import click
import os
import subprocess

from natsort import natsorted


GET_MIGRATIONS = [
    'psql', '{db}',
    '-c', 'SELECT patch FROM meta_data',
    '-tA'
]
RUN_MIGRATION = ['psql', '{db}', '-f', '{filename}']


def substitute(args, values={}):
    new_args = []
    for t in args:
        new_args.append(t.format(**values))

    return new_args


@click.command()
@click.argument('db')
@click.option('--user')
def main(db, user=None):
    if user:
        user = ['-U', user]
        GET_MIGRATIONS.extend(user)
        RUN_MIGRATION.extend(user)

    cmd = subprocess.run(substitute(GET_MIGRATIONS, {'db': db}),
                         stdout=subprocess.PIPE)

    loaded = {x.decode() for x in cmd.stdout.splitlines()}
    available = set(os.listdir()) - {__file__}

    to_run = natsorted(available - loaded)

    for filename in to_run:
        click.echo(
            subprocess.run(
                substitute(RUN_MIGRATION, {'db': db, 'filename': filename}),
                stdout=subprocess.PIPE).stdout)


main()