create table if not exists layer3domains(
  id serial not null primary key,
  name varchar(128) not null unique,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null
);

create table if not exists pools(
  id serial not null,
  layer3domain_id integer not null references layer3domains(id),
  name varchar(128) unique,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null,
  primary key(layer3domain_id, id)
);

create table containers(
	layer3domain_id integer not null references layer3domains(id),
 	subnet cidr not null,
 	pool_id integer,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null,
 	unique(layer3domain_id, subnet),
	foreign key (layer3domain_id, pool_id) references pools(layer3domain_id, id)
);

create or replace view containers_tree as
select
	c.layer3domain_id,
  c.subnet,
  array_agg(p.subnet order by p.subnet) filter (where p.subnet is not null) as parents
from containers c
left join containers p
  on c.subnet << p.subnet
  and c.layer3domain_id = p.layer3domain_id
group by c.layer3domain_id, c.subnet
order by c.layer3domain_id, c.subnet;
comment on view containers_tree is 'This returns a tree of all containers so that sub trees can be shown.';

create table if not exists ips(
  layer3domain_id integer not null,
  version smallint not null,
  address inet not null,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null,
  unique(layer3domain_id, version, address)
);

create table if not exists zones(
  id serial not null primary key,
  name varchar not null unique,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null
);

create table if not exists zoneviews(
  id serial not null primary key,
  zone_id integer not null references zones(id),
  name varchar not null,
  serial bigint not null default 0,
  ttl integer not null default 3600,
  primary_name_server varchar not null,
  mail varchar not null,
  refresh integer not null default 10800,
  retry integer not null default 900,
  expire integer not null default 604800,
  minimum bigint not null default 86400,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null
);
create index if not exists zoneviews_reference_key on zoneviews(zone_id, id);

create table if not exists records(
  zoneview_id integer not null references zoneviews(id) on delete cascade,
  name varchar(254) not null,
  type varchar(11) not null,
  ttl integer,
  value text not null,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null,
  unique(zoneview_id, name, type, value)
);

create table if not exists outputgroups(
  id serial not null primary key,
  name varchar(128) not null unique,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null
);

create table if not exists zoneviews_outputgroups(
  outputgroup_id integer not null references outputgroups(id),
  zoneview_id integer not null references zoneviews(id),
  unique(outputgroup_id, zoneview_id)
);

create table if not exists outputs(
  id serial not null primary key,
  name varchar(128) not null unique,
  plugin varchar(20) not null,
  db_uri varchar(250) not null,
  status varchar(250) not null,
  attributes jsonb default '{}'::jsonb constraint attributes_not_null check(attributes is not null and attributes != 'null'::jsonb),
  created_at timestamptz not null default now(),
  created_by varchar(128) not null,
  modified_at timestamptz not null default now(),
  modified_by varchar(128) not null
);

create table if not exists outputgroup_outputs(
  outputgroups_id integer not null references outputgroups(id),
  output_id integer not null references outputs(id),
  unique(outputgroups_id, output_id)
);

-- create history functionality
create table if not exists history(
	transaction text not null,
	created_at timestamptz not null default now(),
	created_by text not null,
	type text not null,
	action text not null,
	old_entity jsonb default '{}',
	new_entity jsonb default '{}'
);
comment on table history is 'History is the log of all changes happening in DIM.';
comment on column history.transaction is 'The transaction ID is the same for all changes originating in the same request.';
comment on column history.created_by is 'The username is not a foreign key to keep the history around';
comment on column history.type is 'Type represents the table that originated the change.';
comment on column history.action is 'The action can be one of insert, update or delete.';

create or replace function record() returns trigger as $$
begin
	if TG_OP = 'UPDATE' or TG_OP = 'INSERT' then
		NEW.modified_at = now();
		NEW.modified_by = current_setting('dim.username');
	end if;
	if TG_OP = 'INSERT' then
		NEW.created_by = current_setting('dim.username');
	end if;
  insert into history(transaction, created_by, type, action, old_entity, new_entity)
  values (
    current_setting('dim.transaction'),
    current_setting('dim.username'),
    TG_TABLE_NAME, TG_OP,
    row_to_json(OLD),
    row_to_json(NEW)
  );
  if TG_OP = 'DELETE' then
    return OLD;
  else
    return NEW;
  end if;
end;$$ language plpgsql;

-- drop existing triggers
drop trigger if exists trg_containers on containers;
drop trigger if exists trg_ips on ips;
drop trigger if exists trg_layer3domains on layer3domains;
drop trigger if exists trg_outputgroup_outputs on outputgroup_outputs;
drop trigger if exists trg_outputgroups on outputgroups;
drop trigger if exists trg_outputs on outputs;
drop trigger if exists trg_pools on pools;
drop trigger if exists trg_records on records;
drop trigger if exists trg_zones on zones;
drop trigger if exists trg_zoneviews on zoneviews;
drop trigger if exists trg_zoneviews_outputgroups on zoneviews_outputgroups;

-- recreate history triggers
create trigger trg_containers before insert or update or delete on containers for each row execute function record();
create trigger trg_ips before insert or update or delete on ips for each row execute function record();
create trigger trg_layer3domains before insert or update or delete on layer3domains for each row execute function record();
create trigger trg_outputgroup_outputs before insert or update or delete on outputgroup_outputs for each row execute function record();
create trigger trg_outputgroups before insert or update or delete on outputgroups for each row execute function record();
create trigger trg_outputs before insert or update or delete on outputs for each row execute function record();
create trigger trg_pools before insert or update or delete on pools for each row execute function record();
create trigger trg_records before insert or update or delete on records for each row execute function record();
create trigger trg_zones before insert or update or delete on zones for each row execute function record();
create trigger trg_zoneviews before insert or update or delete on zoneviews for each row execute function record();
create trigger trg_zoneviews_outputgroups before insert or update or delete on zoneviews_outputgroups for each row execute function record();

create or replace function subnets_between(net_start inet, net_end inet) returns setof cidr as $$
declare
        common cidr;
        mask int;
begin
        net_start := host(net_start)::inet;
        net_end   := host(net_end)::inet;
        -- check if net_start and net_end are in the same subnet
        common := inet_merge(host(net_start)::inet, host(net_end)::inet);
        mask := masklen(common);
        if set_masklen(net_start, mask) = common and broadcast(common) = set_masklen(net_end, mask) then
                return next common;
                return;
        end if;
        -- subnet is too big, so make it smaller
        return query select * from subnets_between(net_start, broadcast(set_masklen(net_start, mask + 1)));
        return query select * from subnets_between(network(set_masklen(net_end, mask + 1)), net_end);
end;
$$ language plpgsql;
comment on function subnets_between(inet, inet) is 'get a list of subnets between two IPs.';

create or replace view containers_free_list as
with subnets as (
        select
                c.layer3domain_id,
                c.subnet,
                ct.subnet child,
                lag(ct.subnet) over (partition by c.subnet order by ct.subnet) has_before,
                lead(ct.subnet) over (partition by c.subnet order by ct.subnet) has_after,
                parents
        from containers c
        join containers_tree ct on c.layer3domain_id = ct.layer3domain_id and c.subnet = ct.parents[array_length(ct.parents, 1)]
                                where host(c.subnet) != host(ct.subnet) and host(broadcast(c.subnet)) != host(broadcast(ct.subnet))
)
select * from (
        select layer3domain_id, parents, 'available' state, ls as subnet
					from subnets, lateral subnets_between(subnet, child - 1) ls
				 where has_before is null
        union all
        select layer3domain_id, parents, 'available', ls
					from subnets, lateral subnets_between(broadcast(child) + 1, broadcast(subnet)) ls
				 where has_after is null
        union all
        select layer3domain_id, parents, 'container', subnet
					from containers_tree
) freelist order by layer3domain_id, subnet;
comment on view containers_free_list is 'Creates a list of free space between other containers.';