/* Helia / atk24D / Martti Laiho
*  muokannut ISP420 Tietokantojen toteutus -opintojaksoa varten Jouni Huotari
*  Tietokantahallinta
*
*  Demox Oy:n tilaustenkäsittelyn tietokannan taulujen perustaminen 
*  täydennetty lähteestä: VirtAMK / tk-osaaja
*
*  versio 0.4  jossa PRIMARY KEY, UNIQUE, FOREIGN KEY ja CHECK -määreet.
*              Perusavaimille NOT NULL-määreet, sillä Solid edellyttää tätä.
*  versio 0.5  lisätty asiakastiedot sukup ja asvuosi
*              ja myyntitilaustiedot tilaustapa, myyjä, lisätietoa,
*              netto, toimitustiedot ja kulut               
*
*----
*  Luo halutessasi ensin Demox Oy:n pääkäyttäjä, jonka nimissä luodaan kannan taulut */
-- CREATE USER demox IDENTIFIED BY 'sql';
-- luo sitten tietokanta TILAUS
-- MySQL:ssä käsky on CREATE DATABASE TILAUS;
-- ja sitten otetaan kanta käyttöön käskyllä USE TILAUS;


/*  Mahdollisia uudelleenluonteja varten taulujen vanhat versiot voidaan 
    viite-eheystarkistusten vuoksi poistaa parhaiten seuraavassa järjestyksessä:  
    
DROP TABLE MTilausrivi;
DROP TABLE MTilaus;
DROP TABLE Tuote;
DROP TABLE Tuoteryhma;
DROP TABLE Asiakas;
*/

CREATE TABLE Asiakas (
astunnus     INTEGER NOT NULL,
asnimi       CHAR(30) NOT NULL,
yhteyshlo    CHAR(50),
sukup        SMALLINT,
             -- 1=mies, 2=nainen
tiliasiakas  CHAR(1) 
             DEFAULT 'E',
luottoraja   DECIMAL(9,2) 
             DEFAULT 100,
myyntikiellossa CHAR(1) 
             DEFAULT 'E',
lahios       VARCHAR(128),
postinro     CHAR(5),
postitmp     VARCHAR(50),
puhnro       CHAR(20),
asvuosi      SMALLINT,
lisatietoa   VARCHAR(512),
CONSTRAINT asiakas_pk 
	     PRIMARY KEY (astunnus)
) ;

CREATE TABLE Tuoteryhma (
trnro        CHAR(3)  NOT NULL,
trnimi       CHAR(30) NOT NULL,
CONSTRAINT tuoteryhma_pk 
             PRIMARY KEY (trnro)
) ;

CREATE TABLE Tuote (
tuotenro     INTEGER  NOT NULL, 
tuotenimi    CHAR(30) NOT NULL,
hinta        DECIMAL(7,2) NOT NULL,
kustannus    DECIMAL(7,2),
-- tuoteryhmä#
trnro        CHAR(3),
-- varastotilanne
varastossa   INTEGER    
             DEFAULT 0 
             NOT NULL,
-- josta vapaana tilattavaksi
vapaamaara   INTEGER 
             DEFAULT 0 
             NOT NULL,
-- ostotilaus toimittajalle jos varastossa < tilausraja
tilausraja   INTEGER,    
toimittajanro INTEGER,
CONSTRAINT tuote_pk 
             PRIMARY KEY (tuotenro),
CONSTRAINT tuotenimi_un 
             UNIQUE (tuotenimi),
CONSTRAINT tuote_ryhma_fk 
             FOREIGN KEY (trnro) 
             REFERENCES Tuoteryhma (trnro),
CONSTRAINT vap_varastossa_chk 
             CHECK (varastossa >= vapaamaara)
) ;

--           Myyntitilaus

CREATE TABLE MTilaus (
tilausnro    INTEGER NOT NULL,
astunnus     INTEGER NOT NULL, 
tilauspvm    DATE NOT NULL,
tilaustapa   CHAR(1),             
myyja        VARCHAR(30),
maksutapa    CHAR(1),
netto        DECIMAL(9,2),
toimitustapa CHAR(1), 
toimituskulut DECIMAL(9,2),
toimitusos   VARCHAR(128),
postinro     CHAR(5),
postitmp     VARCHAR(50),
tila         CHAR(1) 
             DEFAULT 'K' 
               NOT NULL,
lisatietoa   VARCHAR(512),
CONSTRAINT tilaus_pk 
             PRIMARY KEY (tilausnro),
CONSTRAINT tilaus_asiakas_fk 
             FOREIGN KEY (astunnus) 
             REFERENCES asiakas (astunnus)
) ; 

--           Myyntitilausrivi

CREATE TABLE MTilausrivi (
tilausnro    INTEGER NOT NULL,
rivinro      SMALLINT NOT NULL,
tuotenro     INTEGER NOT NULL, 
kpl          INTEGER NOT NULL,
CONSTRAINT tilausrivi_pk 
             PRIMARY KEY (tilausnro, rivinro),
CONSTRAINT til_rivi_tilaus_fk 
             FOREIGN KEY (tilausnro) 
             REFERENCES MTilaus (tilausnro),
CONSTRAINT til_rivi_tuote_fk 
             FOREIGN KEY (tuotenro) 
             REFERENCES Tuote (tuotenro)
);



-- anna lopuksi demox-käyttäjälle kaikki oikeudet TILAUS-kannan tauluihin:
-- grant all on tilaus.* to demox;