-- 1) Create database if it doesn't exist
IF DB_ID(N'company') IS NULL
    CREATE DATABASE [company];
GO

-- 2) Switch into it
USE [company];
GO

-- 3) Create schema if it doesn't exist (per EXEC in eigener Batch)
IF SCHEMA_ID(N'main') IS NULL
    EXEC('CREATE SCHEMA [main]');
GO

-- 4) Create tables
CREATE TABLE [main].[address](
    [id] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
);
GO


CREATE TABLE [main].[contract](
    [id] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
);
GO


CREATE TABLE [main].[customer](
    [id] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
);
GO



-- 5) Add columns (einmalig, alle auf einmal)
ALTER TABLE [main].[address]
    ADD         [city] NVARCHAR(255),
        [country] NVARCHAR(255),
        [house_number] NVARCHAR(255),
        [street] NVARCHAR(255),
        [zip_code] NVARCHAR(255);
GO

ALTER TABLE [main].[contract]
    ADD         [id_customer] BIGINT,
        [duration_months] INT,
        [amount] NUMERIC,
        [contract_number] NVARCHAR(255),
        [contract_date] DATETIME2;
GO

ALTER TABLE [main].[customer]
    ADD         [id_address] BIGINT,
        [email] NVARCHAR(255),
        [phone] NVARCHAR(255),
        [first_name] NVARCHAR(255),
        [last_name] NVARCHAR(255);
GO


-- 6) Alter columns to NOT NULL (jeweils einzeln)
ALTER TABLE [main].[address] ALTER COLUMN [city] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[address] ALTER COLUMN [country] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[address] ALTER COLUMN [house_number] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[address] ALTER COLUMN [street] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[address] ALTER COLUMN [zip_code] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[contract] ALTER COLUMN [id_customer] BIGINT NOT NULL;
GO

ALTER TABLE [main].[contract] ALTER COLUMN [duration_months] INT NOT NULL;
GO

ALTER TABLE [main].[contract] ALTER COLUMN [amount] NUMERIC NOT NULL;
GO

ALTER TABLE [main].[contract] ALTER COLUMN [contract_number] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[contract] ALTER COLUMN [contract_date] DATETIME2 NOT NULL;
GO

ALTER TABLE [main].[customer] ALTER COLUMN [id_address] BIGINT NOT NULL;
GO

ALTER TABLE [main].[customer] ALTER COLUMN [email] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[customer] ALTER COLUMN [phone] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[customer] ALTER COLUMN [first_name] NVARCHAR(255) NOT NULL;
GO

ALTER TABLE [main].[customer] ALTER COLUMN [last_name] NVARCHAR(255) NOT NULL;
GO


-- 7) Foreign-Keys und Indexe
ALTER TABLE [main].[contract]
    ADD CONSTRAINT fk__contract__id_customer
        FOREIGN KEY ([id_customer]) REFERENCES [main].[customer]([id]);
GO
ALTER TABLE [main].[customer]
    ADD CONSTRAINT fk__customer__id_address
        FOREIGN KEY ([id_address]) REFERENCES [main].[address]([id]);
GO

CREATE INDEX fk__contract__id_customer
    ON [main].[contract]([id_customer]);
GO
CREATE INDEX fk__customer__id_address
    ON [main].[customer]([id_address]);
GO