CREATE database sales;
USE sales;
#Карагишиев Абдулкадыр
CREATE TABLE k_staff (
  staff_num INT NOT NULL AUTO_INCREMENT,
  staff_name VARCHAR(45) NOT NULL,
  staff_post VARCHAR(45) NULL,
  staff_hiredate DATE NULL,
  staff_termdate DATE NULL,
  k_dept_dept_num INT NOT NULL,
  PRIMARY KEY (staff_num));

CREATE TABLE k_dept (
  dept_num INT NOT NULL AUTO_INCREMENT,
  dept_full_name VARCHAR(45),
  dept_short_name VARCHAR(10),
  k_staff_staff_num INT NULL DEFAULT NULL,
  PRIMARY KEY (dept_num));

alter table k_staff add CONSTRAINT fk_k_staff_k_dept1 FOREIGN KEY (k_dept_dept_num) REFERENCES k_dept (dept_num) ON DELETE NO ACTION ON UPDATE NO ACTION;

alter table k_dept add CONSTRAINT fk_k_dept_k_staff1 FOREIGN KEY (k_staff_staff_num) REFERENCES k_staff (staff_num) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE TABLE k_firm (
  firm_num INT NOT NULL AUTO_INCREMENT,
  firm_name VARCHAR(45) NOT NULL,
  firm_addr VARCHAR(45) NULL,
  firm_phone VARCHAR(20) NULL,
  PRIMARY KEY (firm_num));

CREATE TABLE k_contract (
  contract_num INT NOT NULL AUTO_INCREMENT,
  contract_date DATE NOT NULL,
  contract_type ENUM('A','B','C') NOT NULL,
  k_firm_firm_num INT NOT NULL,
  k_staff_staff_num INT NOT NULL,
  PRIMARY KEY (contract_num),
  CONSTRAINT fk_k_kontrakt_k_firm1
    FOREIGN KEY (k_firm_firm_num) REFERENCES k_firm (firm_num)
    ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_k_kontrakt_k_staff1
    FOREIGN KEY (k_staff_staff_num) REFERENCES k_staff (staff_num)
    ON DELETE NO ACTION ON UPDATE NO ACTION);

CREATE TABLE k_bill (
  bill_num INT NOT NULL AUTO_INCREMENT,
  bill_date DATE NOT NULL,
  bill_sum DECIMAL(9,2) NOT NULL,
  bill_term DATE NOT NULL,
  bill_peni DECIMAL(6,2) NULL,
  k_contract_contract_num INT NOT NULL,
  PRIMARY KEY (bill_num),
  CONSTRAINT fk_k_bill_k_contract1
    FOREIGN KEY (k_contract_contract_num) REFERENCES k_contract (contract_num)
    ON DELETE NO ACTION ON UPDATE NO ACTION);

CREATE TABLE k_payment (
  payment_num INT NOT NULL,
  payment_date DATE NOT NULL,
  payment_sum DECIMAL(9,2) NOT NULL,
  k_bill_bill_num INT NOT NULL,
  PRIMARY KEY (payment_num, k_bill_bill_num),
 CONSTRAINT fk_k_payment_k_bill1
    FOREIGN KEY (k_bill_bill_num) REFERENCES k_bill (bill_num)
    ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE k_staff_archive (
  staff_num INT NOT NULL AUTO_INCREMENT,
  staff_name VARCHAR(45) NOT NULL,
  staff_post VARCHAR(45) NULL DEFAULT NULL,
  staff_hiredate DATE NULL DEFAULT NULL,
  staff_termdate DATE NULL DEFAULT NULL,
  k_dept_dept_num INT NULL DEFAULT NULL,
  PRIMARY KEY (staff_num));



CREATE TABLE k_price (
  price_num INT NOT NULL AUTO_INCREMENT,
  price_name VARCHAR(45) NOT NULL,
  price_sum DECIMAL(9,2) NULL,
  price_type VARCHAR(1) NULL,
  PRIMARY KEY (price_num));

CREATE TABLE k_protokol (
  k_bill_bill_num INT NOT NULL,
  k_price_price_num INT NOT NULL,
  kolvo INT NOT NULL,
  price_sum DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (K_bill_bill_num, k_price_price_num),
  CONSTRAINT fk_k_bill_has_k_price_k_bill1
    FOREIGN KEY (k_bill_bill_num) REFERENCES k_bill (bill_num)
    ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_k_bill_has_k_price_K_price1
    FOREIGN KEY (k_price_price_num) REFERENCES k_price (price_num)
    ON DELETE NO ACTION ON UPDATE NO ACTION);

# Заполнение

INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Альфа', 'Москва');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Бета', 'Казань');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Гамма', 'Париж');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Дельта', 'Лондон');
INSERT INTO k_firm (firm_name, firm_addr)
VALUES('Омега', 'Токио');
#Карагишиев
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Sales', 'Отдел продаж');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Mart', 'Отдел маркетинга');
INSERT INTO k_dept (dept_short_name, dept_full_name)
VALUES('Cust', 'Отдел гарантийного обслуживания');
#Карагишиев
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Иванов', 1, '1999-01-01', 'Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Петров', 2, '2010-10-13','Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Сидоров', 3, '2005-12-01','Менеджер');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Семенов', 1, '1990-01-01','Директор');
INSERT INTO k_staff (staff_name, k_dept_dept_num, staff_hiredate, staff_post)
VALUES('Григорьев', 3, '2008-12-19','Программист');
#Карагишиев
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 1, 1,'2011-11-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 1, 2,'2011-10-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 1, 1,'2011-09-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 2, 2,'2011-11-15');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 2, 2,'2011-08-01');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 3, 1,'2011-07-15');
INSERT INTO k_contract
(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 4, 1,'2011-11-12');

INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-11-12', '2011-12-12', 1000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2011-12-12', '2012-01-12', 2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(1, '2012-01-12', '2012-02-12',2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2011-12-12', '2012-01-12', 6000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(2, '2012-01-12', '2012-02-12', 2000);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(3, '2012-01-12', '2012-02-12', 2500);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(4, '2011-12-12', '2012-01-12', 1500);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2011-12-12', '2012-01-12', 1200);
INSERT INTO k_bill
(k_contract_contract_num, bill_date, bill_term, bill_sum)
VALUES(5, '2012-01-12', '2012-02-12', 10000);

INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (1, 'Материализация духов', 1000.00, 'У');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (2, 'Раздача слонов', 100.00, 'У');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (3, 'Слоновый бивень', 3000.00, 'Т');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (4, 'Моржовый клык', 1500.00, 'Т');
INSERT INTO k_price (price_num, price_name, price_sum, price_type)
VALUES (5, 'Копыто пегаса', 5000.00, 'Т');

# Отключение режима safe mode (запрет изменений без указания первичного ключа)
# при помощи команды SQL
# Если режим safe mode включен, то MySQL-сервер выдает ошибку 1175

SET SQL_SAFE_UPDATES = 0;

UPDATE k_dept SET k_staff_staff_num=2
WHERE dept_short_name='Mart';
UPDATE k_dept SET k_staff_staff_num=3
WHERE dept_short_name='Cust';
UPDATE k_dept SET k_staff_staff_num=1
WHERE dept_short_name='Sales';

INSERT INTO k_protokol (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (1, 1, 1, 1000.00);
INSERT INTO k_protokol (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (2, 1, 2, 1000.00);
INSERT INTO k_protokol (K_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (3, 2, 20, 100.00);
INSERT INTO k_protokol (K_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (4, 3, 2, 3000.00);
INSERT INTO k_protokol  (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (5, 1, 1, 1000.00);
INSERT INTO k_protokol  (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (5, 2, 10, 100.00);
INSERT INTO k_protokol  (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (6, 1, 2, 1000.00);
INSERT INTO k_protokol  (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (6, 2, 5, 100.00);
INSERT INTO k_protokol  (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (7, 4, 1, 1500.00);
INSERT INTO k_protokol  (k_bill_bill_num, K_price_price_num, kolvo, price_sum)
VALUES (8, 1, 1, 1000.00);
INSERT INTO k_protokol  (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (8, 2, 2, 100.00);
INSERT INTO k_protokol  (k_bill_bill_num, k_price_price_num, kolvo, price_sum)
VALUES (9, 5, 2, 5000.00);

INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2011-12-15', 1000.00, 2);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-13', 1500.00, 3);
INSERT INTO k_payment (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-12', 1000.00, 4);
INSERT INTO k_payment  (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2012-01-05', 100.00, 7);
INSERT INTO k_payment  (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (1, '2011-12-25', 1000.00, 8);
INSERT INTO k_payment  (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (2, '2012-01-15', 500.00, 3);
INSERT INTO k_payment  (payment_num, payment_date, payment_sum, k_bill_bill_num)
VALUES (2, '2012-01-12', 900.00, 7);


#Карагишиев
call show_contracts("Петров");

#Карагишиев
call clearance(10);

#Карагишиев
call show_fname("Менеджер");

#Карагишиев
call show_bill_info(3);