---

-- =========================================================================
-- 1. СОЗДАНИЕ И ПОДКЛЮЧЕНИЕ К БАЗЕ ДАННЫХ
-- =========================================================================
CREATE DATABASE [King Corporation];
GO

USE [King Corporation];
GO

-- =========================================================================
-- 2. СОЗДАНИЕ ТАБЛИЦ (DDL)
-- =========================================================================

-- Таблица: Должности в фирме
CREATE TABLE job (
    job_id SMALLINT PRIMARY KEY,
    [function] VARCHAR(30) NOT NULL
);
GO

-- Таблица: Места размещения отделов
CREATE TABLE location (
    location_id SMALLINT PRIMARY KEY,
    regional_group VARCHAR(20) NOT NULL
);
GO

-- Таблица: Отделы фирмы
CREATE TABLE department (
    department_id SMALLINT PRIMARY KEY,
    name VARCHAR(14) NOT NULL,
    location_id SMALLINT NOT NULL
);
GO

-- Таблица: Сотрудники фирмы
CREATE TABLE employee (
    employee_id SMALLINT PRIMARY KEY,
    last_name VARCHAR(15) NOT NULL,
    first_name VARCHAR(15) NOT NULL,
    middle_initial VARCHAR(1),
    manager_id SMALLINT NULL,
    job_id SMALLINT NOT NULL,
    hire_date DATE NOT NULL,
    salary NUMERIC(7,2) NOT NULL,
    commission NUMERIC(7,2) NULL,
    department_id SMALLINT NOT NULL
);
GO

-- Таблица: Фирмы-покупатели
CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(45) NOT NULL,
    address VARCHAR(40) NULL,
    city VARCHAR(30) NULL,
    state VARCHAR(2) NULL,
    zip_code VARCHAR(9) NULL,
    area_code SMALLINT NULL,
    phone_number INTEGER NULL,
    salesperson_id SMALLINT NULL,
    credit_limit NUMERIC(9,2) NULL,
    comments VARCHAR(500) NULL
);
GO

-- Таблица: Договоры о продаже
CREATE TABLE sales_order (
    order_id SMALLINT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INTEGER NOT NULL,
    ship_date DATE NULL,
    total NUMERIC(8,2) NOT NULL
);
GO

-- Таблица: Товары
CREATE TABLE product (
    product_id INTEGER PRIMARY KEY,
    description VARCHAR(30) NOT NULL
);
GO

-- Таблица: Акты продаж (позиции заказов)
CREATE TABLE item (
    order_id SMALLINT NOT NULL,
    item_id SMALLINT NOT NULL,
    product_id INTEGER NOT NULL,
    actual_price NUMERIC(8,2) NOT NULL,
    quantity INTEGER NOT NULL,
    total NUMERIC(8,2) NOT NULL,
    PRIMARY KEY (order_id, item_id)
);
GO

-- Таблица: Цены
CREATE TABLE price (
    product_id INTEGER NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    list_price NUMERIC(8,2) NOT NULL,
    min_price NUMERIC(8,2) NOT NULL,
    PRIMARY KEY (product_id, start_date)
);
GO

-- =========================================================================
-- 3. ЗАПОЛНЕНИЕ ТАБЛИЦ ДАННЫМИ (DML)
-- =========================================================================

-- Должности
INSERT INTO job (job_id, [function]) VALUES 
(667, 'CLERK'),
(670, 'SALESPERSON'),
(671, 'MANAGER'),
(672, 'ANALYST'),
(673, 'PRESIDENT');
GO

-- Места размещения
INSERT INTO location (location_id, regional_group) VALUES 
(122, 'NEW YORK'),
(123, 'DALLAS'),
(124, 'CHICAGO'),
(167, 'BOSTON');
GO

-- Отделы
INSERT INTO department (department_id, name, location_id) VALUES 
(10, 'ACCOUNTING', 122),
(13, 'SALES', 122),
(20, 'RESEARCH', 123),
(30, 'OPERATIONS', 124);
GO

-- Сотрудники (включая KING, SMITH, ALLEN, TURNER, ADAMS и др.)
INSERT INTO employee (employee_id, last_name, first_name, middle_initial, manager_id, job_id, hire_date, salary, commission, department_id) VALUES 
(7839, 'KING', 'ANDREW', 'P', NULL, 673, '1981-11-17', 5000.00, NULL, 10),
(7698, 'BLAKE', 'ROBERT', 'E', 7839, 671, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'JAMES', 'F', 7839, 671, '1981-06-09', 2450.00, NULL, 10),
(7566, 'JONES', 'CHARLES', 'H', 7839, 671, '1981-04-02', 2975.00, NULL, 20),
(7654, 'MARTIN', 'KAREN', 'J', 7698, 670, '1981-09-28', 1250.00, 1400.00, 30),
(7499, 'ALLEN', 'JONATHAN', 'W', 7698, 670, '1981-02-20', 1600.00, 300.00, 30),
(7844, 'TURNER', 'MARY', 'A', 7698, 670, '1981-09-08', 1500.00, 0.00, 30),
(7900, 'JAMES', 'WILLIE', 'R', 7698, 667, '1981-12-03', 950.00, NULL, 30),
(7521, 'WARD', 'CYNTHIA', 'D', 7698, 670, '1981-02-22', 1250.00, 500.00, 30),
(7902, 'FORD', 'MICHAEL', 'G', 7566, 672, '1981-12-03', 3000.00, NULL, 20),
(7369, 'SMITH', 'JOHN', 'Q', 7902, 667, '1980-12-17', 800.00, NULL, 20),
(7876, 'ADAMS', 'PETER', 'S', 7788, 667, '1987-05-23', 1100.00, NULL, 20),
(7788, 'SCOTT', 'ALEXANDER', 'V', 7566, 672, '1987-04-19', 3000.00, NULL, 20),
(7934, 'MILLER', 'STEPHEN', 'B', 7782, 667, '1982-01-23', 1300.00, NULL, 10);
GO

-- Покупатели
INSERT INTO customer (customer_id, name, address, city, state, zip_code, area_code, phone_number, salesperson_id, credit_limit, comments) VALUES 
(101, 'STADIUM SPORTS', '123 Main St', 'Dallas', 'TX', '75001', 214, 5551234, 7499, 10000.00, 'Good client'),
(102, 'TENNIS WORLD', '456 Oak Rd', 'Chicago', 'IL', '60007', 312, 5555678, 7654, 15000.00, NULL),
(103, 'ACTION SPORTS', '789 Pine Ave', 'New York', 'NY', '10001', 212, 5559012, 7499, 20000.00, 'VIP'),
(104, 'ACTIVE LIFE', '321 Elm St', 'Boston', 'MA', '02108', 617, 5553456, 7844, 12000.00, NULL);
GO

-- Товары
INSERT INTO product (product_id, description) VALUES 
(1001, 'DUNK BASKETBALL INDOOR'),
(1002, 'TENNIS RACKET PRO'),
(1003, 'TENNIS BALLS PACK'),
(1004, 'FOOTBALL LEATHER');
GO

-- Цены
INSERT INTO price (product_id, start_date, end_date, list_price, min_price) VALUES 
(1001, '1989-01-01', '1990-06-30', 25.00, 20.00),
(1001, '1990-07-01', NULL, 30.00, 24.00),
(1002, '1989-05-15', NULL, 120.00, 95.00),
(1003, '1989-01-01', NULL, 8.00, 6.00),
(1004, '1989-01-01', NULL, 45.00, 38.00);
GO

-- Заказы (Sales Orders)
INSERT INTO sales_order (order_id, order_date, customer_id, ship_date, total) VALUES 
(1010, '1989-06-10', 101, '1989-06-15', 500.00),
(1011, '1990-07-12', 104, '1990-07-15', 1200.00),
(1012, '1990-08-05', 102, '1990-08-10', 350.00),
(1013, '1989-11-20', 101, '1989-11-25', 800.00);
GO

-- Позиции заказов (Items)
INSERT INTO item (order_id, item_id, product_id, actual_price, quantity, total) VALUES 
(1010, 1, 1001, 22.00, 10, 220.00),
(1010, 2, 1003, 7.50, 40, 300.00),
(1011, 1, 1002, 90.00, 5, 450.00),
(1012, 1, 1002, 95.00, 3, 285.00),
(1013, 1, 1001, 24.00, 25, 600.00);
GO

-- =========================================================================
-- 4. СОЗДАНИЕ СВЯЗЕЙ (FOREIGN KEYS) СОГЛАСНО СХЕМЕ БАЗЫ ДАННЫХ
-- =========================================================================

-- 1. Связь Department с Location (Отдел -> Место размещения)
ALTER TABLE department
ADD CONSTRAINT FK_department_location 
FOREIGN KEY (location_id) REFERENCES location(location_id);
GO

-- 2. Связь Employee с Job (Сотрудник -> Должность)
ALTER TABLE employee
ADD CONSTRAINT FK_employee_job 
FOREIGN KEY (job_id) REFERENCES job(job_id);
GO

-- 3. Связь Employee с Department (Сотрудник -> Отдел)
ALTER TABLE employee
ADD CONSTRAINT FK_employee_department 
FOREIGN KEY (department_id) REFERENCES department(department_id);
GO

-- 4. Саморазводящаяся связь Employee с Employee (Сотрудник -> Начальник / Manager)
ALTER TABLE employee
ADD CONSTRAINT FK_employee_manager 
FOREIGN KEY (manager_id) REFERENCES employee(employee_id);
GO

-- 5. Связь Customer с Employee (Покупатель -> Продавец / Salesperson)
ALTER TABLE customer
ADD CONSTRAINT FK_customer_employee 
FOREIGN KEY (salesperson_id) REFERENCES employee(employee_id);
GO

-- 6. Связь Sales_order с Customer (Заказ -> Покупатель)
ALTER TABLE sales_order
ADD CONSTRAINT FK_sales_order_customer 
FOREIGN KEY (customer_id) REFERENCES customer(customer_id);
GO

-- 7. Связь Item с Sales_order (Позиция акта -> Заказ)
ALTER TABLE item
ADD CONSTRAINT FK_item_sales_order 
FOREIGN KEY (order_id) REFERENCES sales_order(order_id);
GO

-- 8. Связь Item с Product (Позиция акта -> Товар)
ALTER TABLE item
ADD CONSTRAINT FK_item_product 
FOREIGN KEY (product_id) REFERENCES product(product_id);
GO

-- 9. Связь Price с Product (Цена -> Товар)
ALTER TABLE price
ADD CONSTRAINT FK_price_product 
FOREIGN KEY (product_id) REFERENCES product(product_id);
GO