---

CREATE DATABASE IF NOT EXISTS optovy_sklad
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE optovy_sklad;

CREATE TABLE Postavshchik (
    id_postavshchika INT PRIMARY KEY AUTO_INCREMENT,
    naimenovanie VARCHAR(120) NOT NULL,
    inn VARCHAR(12) NOT NULL UNIQUE,
    telefon VARCHAR(20)
);

CREATE TABLE Pokupatel (
    id_pokupatelya INT PRIMARY KEY AUTO_INCREMENT,
    naimenovanie VARCHAR(120) NOT NULL,
    inn VARCHAR(12) NOT NULL UNIQUE,
    adres VARCHAR(200),
    telefon VARCHAR(20)
);

CREATE TABLE Tovar (
    id_tovara INT PRIMARY KEY AUTO_INCREMENT,
    naimenovanie VARCHAR(120) NOT NULL,
    edinitsa_izmereniya VARCHAR(20) NOT NULL,
    tsena DECIMAL(12,2) NOT NULL,
    stavka_nds DECIMAL(4,2) NOT NULL DEFAULT 20.00
);

CREATE TABLE PrikhodnayaNakladnaya (
    id_prikhoda INT PRIMARY KEY AUTO_INCREMENT,
    nomer VARCHAR(30) NOT NULL UNIQUE,
    data_prikhoda DATE NOT NULL,
    id_postavshchika INT NOT NULL,
    summa DECIMAL(14,2) NOT NULL DEFAULT 0,
    FOREIGN KEY (id_postavshchika)
        REFERENCES Postavshchik(id_postavshchika)
);

CREATE TABLE StrokaPrikhoda (
    id_stroki INT PRIMARY KEY AUTO_INCREMENT,
    id_prikhoda INT NOT NULL,
    id_tovara INT NOT NULL,
    kolichestvo DECIMAL(12,3) NOT NULL,
    tsena DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (id_prikhoda)
        REFERENCES PrikhodnayaNakladnaya(id_prikhoda),
    FOREIGN KEY (id_tovara)
        REFERENCES Tovar(id_tovara)
);

CREATE TABLE Ostatok (
    id_tovara INT PRIMARY KEY,
    kolichestvo_na_sklade DECIMAL(12,3) NOT NULL DEFAULT 0,
    data_obnovleniya DATE NOT NULL,
    FOREIGN KEY (id_tovara)
        REFERENCES Tovar(id_tovara)
);

CREATE TABLE TTN (
    id_ttn INT PRIMARY KEY AUTO_INCREMENT,
    nomer VARCHAR(30) NOT NULL UNIQUE,
    data_otgruzki DATE NOT NULL,
    id_pokupatelya INT NOT NULL,
    summa DECIMAL(14,2) NOT NULL DEFAULT 0,
    FOREIGN KEY (id_pokupatelya)
        REFERENCES Pokupatel(id_pokupatelya)
);

CREATE TABLE StrokaTTN (
    id_stroki INT PRIMARY KEY AUTO_INCREMENT,
    id_ttn INT NOT NULL,
    id_tovara INT NOT NULL,
    kolichestvo DECIMAL(12,3) NOT NULL,
    tsena DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (id_ttn)
        REFERENCES TTN(id_ttn),
    FOREIGN KEY (id_tovara)
        REFERENCES Tovar(id_tovara)
);

CREATE TABLE PlatezhnoePoruchenie (
    id_platezha INT PRIMARY KEY AUTO_INCREMENT,
    nomer VARCHAR(30) NOT NULL UNIQUE,
    data_oplaty DATE NOT NULL,
    summa DECIMAL(14,2) NOT NULL,
    id_ttn INT NOT NULL UNIQUE,
    FOREIGN KEY (id_ttn)
        REFERENCES TTN(id_ttn)
);

CREATE TABLE SchetFaktura (
    id_scheta INT PRIMARY KEY AUTO_INCREMENT,
    nomer VARCHAR(30) NOT NULL UNIQUE,
    data_scheta DATE NOT NULL,
    summa_bez_nds DECIMAL(14,2) NOT NULL,
    summa_nds DECIMAL(14,2) NOT NULL,
    id_ttn INT NOT NULL UNIQUE,
    FOREIGN KEY (id_ttn)
        REFERENCES TTN(id_ttn)
);