-- Generated for Snowflake
-- Review users, roles and physical storage settings before production use.

CREATE DATABASE IF NOT EXISTS "company";
USE DATABASE "company";

CREATE SCHEMA IF NOT EXISTS "main";

CREATE TABLE "main"."address" (
  "ID" NUMBER(38,0) AUTOINCREMENT NOT NULL,
  "CITY" VARCHAR(255) NOT NULL,
  "COUNTRY" VARCHAR(255) NOT NULL,
  "HOUSE_NUMBER" VARCHAR(255) NOT NULL,
  "STREET" VARCHAR(255) NOT NULL,
  "ZIP_CODE" VARCHAR(255) NOT NULL,
  CONSTRAINT "pk_main_address" PRIMARY KEY ("ID")
);

CREATE TABLE "main"."contract" (
  "ID" NUMBER(38,0) AUTOINCREMENT NOT NULL,
  "ID_CUSTOMER" NUMBER(38,0) NOT NULL,
  "DURATION_MONTHS" NUMBER(38,0) NOT NULL,
  "AMOUNT" FLOAT NOT NULL,
  "CONTRACT_NUMBER" VARCHAR(255) NOT NULL,
  "CONTRACT_DATE" TIMESTAMP_TZ NOT NULL,
  CONSTRAINT "pk_main_contract" PRIMARY KEY ("ID")
);

CREATE TABLE "main"."customer" (
  "ID" NUMBER(38,0) AUTOINCREMENT NOT NULL,
  "ID_ADDRESS" NUMBER(38,0) NOT NULL,
  "EMAIL" VARCHAR(255) NOT NULL,
  "PHONE" VARCHAR(255) NOT NULL,
  "FIRST_NAME" VARCHAR(255) NOT NULL,
  "LAST_NAME" VARCHAR(255) NOT NULL,
  CONSTRAINT "pk_main_customer" PRIMARY KEY ("ID")
);

-- Foreign keys
ALTER TABLE "main"."contract" ADD CONSTRAINT "fk_main_contract_ID_CUSTOMER" FOREIGN KEY ("ID_CUSTOMER") REFERENCES "main"."customer" ("ID");
ALTER TABLE "main"."customer" ADD CONSTRAINT "fk_main_customer_ID_ADDRESS" FOREIGN KEY ("ID_ADDRESS") REFERENCES "main"."address" ("ID");