-- Dateiname create_table.sql
--
-- Beschreibung: Queries zum erstellen der Tabellen der Case Study DB
--
-- Autor: Andreas Zweili
-- Datum: 2017-06-05
-- Server Version: SQL Server 2016


-- create table
use marketdb;
if not exists (select * from sysobjects where name='commercials')
    CREATE TABLE commercials (
    commercial_id int identity not null,
    date_since_last_change date default getdate(),
    member_id int not null,
    constraint commercial_pk primary key (commercial_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='member_status')
    CREATE TABLE member_status (
    member_status_id int identity not null,
    status_name varchar(50) not null,
    constraint member_status_pk primary key (member_status_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='salutations')
    CREATE TABLE salutations (
    salutation_id int identity not null,
    salutation_name varchar(50) not null,
    constraint salutation_pk primary key (salutation_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='subscriptions')
    CREATE TABLE subscriptions (
    subscription_id int identity not null,
    max_rented_locations int not null,
    subscription_price money not null,
    subscription_name varchar(50) not null,
    duration_in_seconds INT,
    constraint subscription_pk primary key (subscription_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='subscption_orders')
    CREATE TABLE subscption_orders (
    subscription_order_id int identity not null,
    running_subscription bit,
    subscription_order_date date default getdate(),
    subscription_id int not null,
    member_id int not null,
    location_id int not null,
    constraint subscription_order_pk primary key (subscription_order_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='members')
    CREATE TABLE members (
    member_id int identity not null,
    email_address varchar(50) not null,
    password varchar(50) not null,
    member_status_id int default 1,
    date_of_registration date default getdate(),
    person_id int,
    signed_agb bit null,
    passed_credit_check bit null,
    constraint member_pk primary key (member_id),
    constraint constraint_email_address unique (email_address)
    );

use marketdb;
if not exists (select * from sysobjects where name='persons')
    CREATE TABLE persons (
    person_id int identity not null,
    salutation_id int not null,
    firstname varchar(50) not null,
    lastname varchar(50) not null,
    date_of_birth date not null,
    streetname varchar(50) not null,
    streetnumber varchar(4) not null,
    city_id int not null,
    country_id int not null,
    constraint person_pk primary key (person_id),
    );

use marketdb;
if not exists (select * from sysobjects where name='trial_periods')
    CREATE TABLE trial_periods (
    trial_period_id int identity not null,
    duration_in_seconds int not null,
    constraint trial_period_pk primary key (trial_period_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='quality_checks')
    CREATE TABLE quality_checks (
    quality_check_id int identity not null,
    check_passed bit,
    due_date date not null,
    checking_member_id int not null,
    checked_member_id int not null,
    constraint quality_check_pk primary key (quality_check_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='cities')
    CREATE TABLE cities (
    city_id int identity not null,
    city_name varchar(50) not null,
    zip_code int not null,
    country_id int not null,
    constraint city_pk primary key (city_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='countries')
    CREATE TABLE countries (
    country_id int identity not null,
    country_name varchar(50) not null,
    constraint country_pk primary key (country_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='rents')
    CREATE TABLE rents (
    rent_id int identity not null,
    rent_date date default getdate(),
    payment_date date null,
    member_id int not null,
    rent_price_id int not null,
	location_id int not null
    constraint rent_pk primary key (rent_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='locations')
    CREATE TABLE locations (
    location_id int identity not null,
    streetname varchar(50) not null,
    location_capacity int not null,
    location_name varchar(50) not null,
    city_id int not null,
    country_id int not null,
	rent_price_id int not null
    constraint location_pk primary key (location_id)
    );

use marketdb;
if not exists (select * from sysobjects where name='rent_prices')
    CREATE TABLE rent_prices (
    rent_price_id int identity not null,
    rent_price money not null,
    constraint rent_price_pk primary key (rent_price_id)
    );


-- Add constraints

use marketdb;
IF OBJECT_ID('dbo.[fk_cit_country_id]', 'F') IS NULL
    ALTER TABLE cities ADD CONSTRAINT fk_cit_country_id
        FOREIGN KEY (country_id) REFERENCES countries (country_id);

use marketdb;
IF OBJECT_ID('dbo.[fk_com_member_id]', 'F') IS NULL
    ALTER TABLE commercials ADD CONSTRAINT fk_com_member_id
        FOREIGN KEY (member_id) REFERENCES members (member_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_mem_status_id]', 'F') IS NULL
    ALTER TABLE members ADD CONSTRAINT fk_mem_status_id
        FOREIGN KEY (member_status_id)
        REFERENCES member_status (member_status_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_per_salutation_id]', 'F') IS NULL
    ALTER TABLE persons ADD CONSTRAINT fk_per_salutation_id
        FOREIGN KEY (salutation_id) REFERENCES salutations (salutation_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_per_city_id]', 'F') IS NULL
    ALTER TABLE persons ADD CONSTRAINT fk_per_city_id
        FOREIGN KEY (city_id) REFERENCES cities (city_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_per_country_id]', 'F') IS NULL
    ALTER TABLE persons ADD CONSTRAINT fk_per_country_id
        FOREIGN KEY (country_id) REFERENCES countries (country_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_sor_subscribtion_id]', 'F') IS NULL
    ALTER TABLE subscption_orders ADD CONSTRAINT fk_sor_subscribtion_id
        FOREIGN KEY (subscription_id)
        REFERENCES subscriptions (subscription_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_sor_member_id]', 'F') IS NULL
    ALTER TABLE subscption_orders ADD CONSTRAINT fk_sor_member_id
        FOREIGN KEY (member_id) REFERENCES members (member_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_sor_location_id]', 'F') IS NULL
    ALTER TABLE subscption_orders ADD CONSTRAINT fk_sor_location_id
        FOREIGN KEY (location_id) REFERENCES locations (location_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_mem_person_id]', 'F') IS NULL
    ALTER TABLE members ADD CONSTRAINT fk_mem_person_id
        FOREIGN KEY (person_id) REFERENCES persons (person_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_qch_checking_member_id]', 'F') IS NULL
    ALTER TABLE quality_checks ADD CONSTRAINT fk_qch_checking_member_id
        FOREIGN KEY (checking_member_id) REFERENCES members (member_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_qch_checked_member_id]', 'F') IS NULL
    ALTER TABLE quality_checks ADD CONSTRAINT fk_qch_checked_member_id
        FOREIGN KEY (checked_member_id) REFERENCES members (member_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_ren_member_id]', 'F') IS NULL
    ALTER TABLE rents ADD CONSTRAINT fk_ren_member_id
        FOREIGN KEY (member_id) REFERENCES members (member_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_ren_rent_price_id]', 'F') IS NULL
    ALTER TABLE rents ADD CONSTRAINT fk_ren_rent_price_id
        FOREIGN KEY (rent_price_id) REFERENCES rent_prices (rent_price_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_ren_location_id]', 'F') IS NULL
    ALTER TABLE rents ADD CONSTRAINT fk_ren_location_id
        FOREIGN KEY (location_id) REFERENCES locations (location_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_location_rpr_id]', 'F') IS NULL
    ALTER TABLE locations ADD CONSTRAINT fk_location_rpr_id
        FOREIGN KEY (rent_price_id) REFERENCES rent_prices (rent_price_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_loc_city_id]', 'F') IS NULL
    ALTER TABLE locations ADD CONSTRAINT fk_loc_city_id
        FOREIGN KEY (city_id) REFERENCES cities (city_id);
go

use marketdb;
IF OBJECT_ID('dbo.[fk_loc_country_id]', 'F') IS NULL
    ALTER TABLE locations ADD CONSTRAINT fk_loc_country_id
        FOREIGN KEY (country_id) REFERENCES countries (country_id);
go