<?xml version="1.0" encoding="UTF-8"?>
<schema>
    <!-- Metadata -->
    <meta>
        <!-- Supported driver types -->
        <drivers>
            <driver>postgres</driver>
            <driver>postgresql</driver>
        </drivers>
    </meta>

    <!-- SQL commands to run on installation and update -->
    <sql>
        <!-- Create the #__ak_profiles table if it's missing -->
        <action table="#__ak_profiles" canfail="0">
            <condition type="missing" value="" />
            <query><![CDATA[
CREATE TABLE "#__ak_profiles" (
	"id" serial NOT NULL,
	"description" character varying(255) NOT NULL,
	"configuration" text,
	"filters" text,
	PRIMARY KEY ("id")
);
            ]]></query>
        </action>

        <!-- Insert into #__ak_profiles if id=1 is not there -->
        <action table="#__ak_profiles" canfail="1">
            <condition type="equals" operator="not" value="1"><![CDATA[
SELECT COUNT(*) FROM "#__ak_profiles" WHERE "id" = 1;
            ]]></condition>

            <query><![CDATA[
INSERT INTO "#__ak_profiles"
("id","description", "configuration", "filters") VALUES
(1,'Default Backup Profile','','');
            ]]></query>
        </action>

        <!-- Create #__ak_stats if it's missing -->
        <action table="#__ak_stats" canfail="0">
            <condition type="missing" value="" />
            <query><![CDATA[
CREATE TABLE "#__ak_stats" (
	"id" serial NOT NULL,
	"description" character varying(255) NOT NULL,
	"comment" text,
	"backupstart" timestamp without time zone DEFAULT '1970-01-01 00:00:00' NOT NULL,
	"backupend" timestamp without time zone DEFAULT '1970-01-01 00:00:00' NOT NULL,
	"status" character varying(10) NOT NULL DEFAULT 'run',
	"origin" character varying(30) NOT NULL DEFAULT 'backend',
	"type" character varying(30) NOT NULL DEFAULT 'full',
	"profile_id" bigint NOT NULL DEFAULT '1',
	"archivename" text,
	"absolute_path" text,
	"multipart" int NOT NULL DEFAULT '0',
	"tag" character varying(255) DEFAULT NULL,
	"backupid" character varying(255) DEFAULT NULL,
	"filesexist" smallint NOT NULL DEFAULT '1',
	"remote_filename" character varying(1000) DEFAULT NULL,
	"total_size" bigint NOT NULL DEFAULT '0',
	PRIMARY KEY ("id")
);
            ]]></query>
            <query canfail="1"><![CDATA[
CREATE INDEX "#__ak_stats_idx_fullstatus" ON "#__ak_stats" ("filesexist", "status");
            ]]></query>
            <query canfail="1"><![CDATA[
CREATE INDEX "#__ak_stats_idx_stale" ON "#__ak_stats" ("status", "origin");
            ]]></query>
        </action>

        <!-- Create #__ak_storage if it's missing -->
        <action table="#__ak_storage" canfail="0">
            <condition type="missing" value="" />
            <query><![CDATA[
CREATE TABLE "#__ak_storage" (
	"tag" character varying(255) NOT NULL DEFAULT 'backend',
	"lastupdate" timestamp without time zone DEFAULT '1970-01-01 00:00:00' NOT NULL,
	"data" text,
	PRIMARY KEY ("tag")
);
            ]]></query>
        </action>

        <!-- Add the backupid column to #__ak_stats if it's missing -->
        <action table="#__ak_stats" canfail="1">
            <condition type="missing" value="backupid" />
            <query><![CDATA[
ALTER TABLE "#__ak_stats"
ADD COLUMN "backupid" character varying(255) DEFAULT NULL
            ]]></query>
        </action>
    </sql>
</schema>