-- -----------------------------------------------------
-- Table`Shop`
-- -----------------------------------------------------
CREATE TABLE Shop (
  shop_id NUMBER(10) NOT NULL,
  shop_name VARCHAR2(45) NOT NULL,
  shop_address VARCHAR2(45) NOT NULL,
  PRIMARY KEY (shop_id))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE Shop_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER Shop_seq_tr
 BEFORE INSERT ON Shop FOR EACH ROW
 WHEN (NEW.shop_id IS NULL)
BEGIN
 SELECT Shop_seq.NEXTVAL INTO :NEW.shop_id FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `employees`
-- -----------------------------------------------------
CREATE TABLE employees (
  emp_id NUMBER(10) NOT NULL,
  first_name VARCHAR2(45) NULL,
  last_name VARCHAR2(45) NULL,
  hire_date DATE NULL,
  Shop_shop_id NUMBER(10) NOT NULL,
  PRIMARY KEY (emp_id, Shop_shop_id)
 ,
  CONSTRAINT fk_employees_Shop1
    FOREIGN KEY (Shop_shop_id)
    REFERENCES Shop (shop_id)
   )
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE employees_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER employees_seq_tr
 BEFORE INSERT ON employees FOR EACH ROW
 WHEN (NEW.emp_id IS NULL)
BEGIN
 SELECT employees_seq.NEXTVAL INTO :NEW.emp_id FROM DUAL;
END;
/

CREATE INDEX fk_employees_Shop1_idx ON employees (Shop_shop_id ASC);


-- -----------------------------------------------------
-- Table `cd`
-- -----------------------------------------------------
CREATE TABLE cd (
  cd_id NUMBER(10) NOT NULL,
  artist_name VARCHAR2(45) NOT NULL,
  album_title VARCHAR2(45) NOT NULL,
  price NUMBER(2,2) NOT NULL,
  genre VARCHAR2(45) NOT NULL,
  PRIMARY KEY (cd_id))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE cd_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER cd_seq_tr
 BEFORE INSERT ON cd FOR EACH ROW
 WHEN (NEW.cd_id IS NULL)
BEGIN
 SELECT cd_seq.NEXTVAL INTO :NEW.cd_id FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `client`
-- -----------------------------------------------------
CREATE TABLE client (
  client_id NUMBER(10) NOT NULL,
  client_name VARCHAR2(45) NULL,
  client_last_name VARCHAR2(45) NULL,
  client_phone_number VARCHAR2(45) NULL,
  PRIMARY KEY (client_id))
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE client_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER client_seq_tr
 BEFORE INSERT ON client FOR EACH ROW
 WHEN (NEW.client_id IS NULL)
BEGIN
 SELECT client_seq.NEXTVAL INTO :NEW.client_id FROM DUAL;
END;
/


-- -----------------------------------------------------
-- Table `sale`
-- -----------------------------------------------------
CREATE TABLE sale (
  sale_id NUMBER(10) NOT NULL,
  cd_cd_id NUMBER(10) NOT NULL,
  client_client_id NUMBER(10) NOT NULL,
  employees_emp_id NUMBER(10) NOT NULL,
  PRIMARY KEY (sale_id)
 ,
  CONSTRAINT fk_cd_has_client_cd
    FOREIGN KEY (cd_cd_id)
    REFERENCES cd (cd_id)
   ,
  CONSTRAINT fk_cd_has_client_client1
    FOREIGN KEY (client_client_id)
    REFERENCES client (client_id)
   ,
  CONSTRAINT fk_Pardavimas_employees1
    FOREIGN KEY (employees_emp_id)
    REFERENCES employees (emp_id)
   )
;

-- Generate ID using sequence and trigger
CREATE SEQUENCE sale_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER sale_seq_tr
 BEFORE INSERT ON sale FOR EACH ROW
 WHEN (NEW.sale_id IS NULL)
BEGIN
 SELECT sale_seq.NEXTVAL INTO :NEW.sale_id FROM DUAL;
END;
/

CREATE INDEX fk_cd_has_client_client1_idx ON sale (client_client_id ASC);
CREATE INDEX fk_cd_has_client_cd_idx ON sale (cd_cd_id ASC);
CREATE INDEX fk_Pardavimas_employees1_idx ON sale (employees_emp_id ASC);


/* SET SQL_MODE=@OLD_SQL_MODE; */
/* SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; */
/* SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; */