using System.Data;
using Microsoft.Data.Sqlite;

namespace Database
{
    public class Database
    {
        public static void CreateDatabaseQuery(SqliteConnection conn)
        {
			string createTableQuery = @"
				CREATE TABLE [Books] (
				    [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
				    [Name] TEXT NOT NULL,
				    [YearOfRelease] INTEGER,
				    [Total] INTEGER,
				    [Available] INTEGER,
				    [AuthorID] INTEGER,
				    [PublisherID] INTEGER,
				    FOREIGN KEY ([AuthorID]) REFERENCES [Authors]([ID]),
				    FOREIGN KEY ([PublisherID]) REFERENCES [Publisher]([ID])
				);
				CREATE TABLE [Authors] (
				    [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
				    [Name] TEXT,
				    [Surname] TEXT,
				    [DateOfBirth] DATE
				);
				CREATE TABLE [Publisher] (
				    [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
				    [Name] TEXT,
				    [State] TEXT
				);
				CREATE TABLE [Borrows] (
				    [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
				    [DateOfBorrow] DATE,
				    [DateOfReturn] DATE,
				    [ReturnDue] DATE,
				    [UserID] INTEGER,
				    [BookID] INTEGER,
				    FOREIGN KEY ([UserID]) REFERENCES [Users]([ID]),
				    FOREIGN KEY ([BookID]) REFERENCES [Books]([ID])
				);
				CREATE TABLE [Users] (
				    [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
				    [Name] TEXT,
				    [Surname] TEXT
				);";
			SqliteCommand command = conn.CreateCommand();
			command.CommandText = createTableQuery;
			command.ExecuteNonQuery();
        }
    }
}