<?xml version="1.0" encoding="UTF-8"?>
<schema>
    <!-- Metadata -->
    <meta>
        <!-- Supported driver types -->
        <drivers>
            <driver>sqlsrv</driver>
            <driver>sqlazure</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] [INT] IDENTITY(1,1) NOT NULL,
	[description] [NVARCHAR](255) NOT NULL,
	[configuration] TEXT NULL,
	[filters] TEXT NULL,
	[quickicon] [TINYINT] NOT NULL DEFAULT ('1'),
	CONSTRAINT [PK_#__ak_profiles] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
            ]]></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>

            <!-- SQL Server does not allow an explicit value for an auto increment field -->
            <query><![CDATA[
INSERT INTO #__ak_profiles (description, configuration, filters, quickicon)
SELECT 'Default Backup profile', '', '', 1
            ]]></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] [BIGINT] IDENTITY(1,1) NOT NULL,
	[description] [NVARCHAR](255) NOT NULL,
	[comment] [NVARCHAR](4000) NULL,
	[backupstart] [DATETIME] NOT NULL DEFAULT ('1900-01-01 00:00:00'),
	[backupend] [DATETIME] NOT NULL DEFAULT ('1900-01-01 00:00:00'),
	[status] [NVARCHAR](8) NOT NULL DEFAULT ('run'),
	[origin] [NVARCHAR](30) NOT NULL DEFAULT ('backend'),
	[type] [NVARCHAR](30) NOT NULL DEFAULT ('full'),
	[profile_id] [BIGINT] NOT NULL DEFAULT ('1'),
	[archivename] [NVARCHAR](4000),
	[absolute_path] [NVARCHAR](4000),
	[multipart] [INT] NOT NULL DEFAULT ('0'),
	[tag] [NVARCHAR](255) NULL,
	[backupid] [NVARCHAR](255) NULL,
	[filesexist] [TINYINT] NOT NULL DEFAULT ('1'),
	[remote_filename] [NVARCHAR](1000) NULL,
	[total_size] [BIGINT] NOT NULL DEFAULT ('0'),
	CONSTRAINT [PK_#__ak_stats] PRIMARY KEY CLUSTERED
	(
		[id] ASC
	) WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
            ]]></query>

            <query canfail="1"><![CDATA[
CREATE NONCLUSTERED INDEX [idx_fullstatus] ON [#__ak_stats]
(
	[filesexist] ASC,
	[status] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

            ]]></query>
            <query canfail="1"><![CDATA[
CREATE NONCLUSTERED INDEX [idx_stale] ON [#__ak_stats]
(
	[status] ASC,
	[origin] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

            ]]></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] [NVARCHAR](255) NOT NULL,
	[lastupdate] [DATETIME] NOT NULL DEFAULT ('1900-01-01 00:00:00'),
	[data] [TEXT]
)
            ]]></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 [backupid] [NVARCHAR](255) DEFAULT NULL
            ]]></query>
        </action>

		<!-- Add the quickicon column to #__ak_profiles if it's missing -->
		<action table="#__ak_profiles" canfail="1">
			<condition type="missing" value="quickicon" />
			<query><![CDATA[
ALTER TABLE [#__ak_profiles]
ADD [quickicon] [TINYINT] NOT NULL DEFAULT ('1')
            ]]></query>
		</action>

	</sql>
</schema>