// The MIT License (MIT)
// 
// Copyright (c) 2017 Jesse Sweetland
// 
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.

using System;
using System.Data;
using System.Data.Common;
using Platibus.SQL.Commands;

namespace Platibus.SQLite.Commands
{
    /// <summary>
    /// A subclass of <see cref="CreateMessageJournalObjectsCommandBuilder"/> that produces
    /// commands for creating message journal objects in a SQLite database using SQLite syntax.
    /// </summary>
    public class SQLiteCreateMessageJournalObjectsCommandBuilder : CreateMessageJournalObjectsCommandBuilder
    {
        /// <inheritdoc />
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public override DbCommand BuildDbCommand(DbConnection connection)
        {
            if (connection == null) throw new ArgumentNullException(nameof(connection));
            var command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = CommandText;
            return command;
        }

        /// <summary>
        /// The default command text (SQLite syntax)
        /// </summary>
        public virtual string CommandText => @"
CREATE TABLE IF NOT EXISTS [PB_MessageJournal]
(
    [Id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [MessageId] TEXT NOT NULL,
    [Timestamp] INTEGER,
    [Category] TEXT NOT NULL,
    [TopicName] TEXT NULL,
    [MessageName] TEXT NULL,
    [Origination] TEXT NULL,
    [Destination] TEXT NULL,
    [ReplyTo] TEXT NULL,
    [RelatedTo] TEXT NULL,
    [Expires] TEXT NULL,
    [ContentType] TEXT NULL,
    [Headers] TEXT,
    [MessageContent] TEXT,
    [Attempts] INT NOT NULL DEFAULT 0,
    [Acknowledged] TEXT NULL,
    [Abandoned] TEXT NULL
);

CREATE INDEX IF NOT EXISTS [PB_MessageJournal_IX_MessageId] 
    ON [PB_MessageJournal]([MessageId]);

CREATE INDEX IF NOT EXISTS [PB_MessageJournal_IX_Timestamp] 
    ON [PB_MessageJournal]([Timestamp]);

CREATE INDEX IF NOT EXISTS [PB_MessageJournal_IX_Category] 
    ON [PB_MessageJournal]([Category]);

CREATE INDEX IF NOT EXISTS [PB_MessageJournal_IX_Origination] 
    ON [PB_MessageJournal]([Origination]);

CREATE INDEX IF NOT EXISTS [PB_MessageJournal_IX_Destination] 
    ON [PB_MessageJournal]([Destination]);

CREATE INDEX IF NOT EXISTS [PB_MessageJournal_IX_RelatedTo] 
    ON [PB_MessageJournal]([RelatedTo]);";
    }
}