---

-- =============================================
-- DATABASE CREATION
-- =============================================

CREATE DATABASE EmploymentBookDB;
GO

USE EmploymentBookDB;
GO


-- =============================================
-- 1. EMPLOYEE
-- =============================================

CREATE TABLE Employee
(
    employee_id INT PRIMARY KEY,
    full_name NVARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL,
    passport_data NVARCHAR(50),
    address NVARCHAR(200),
    phone NVARCHAR(20),
    email NVARCHAR(100)
);
GO


-- =============================================
-- 2. EMPLOYER
-- =============================================

CREATE TABLE Employer
(
    employer_id INT PRIMARY KEY,
    name NVARCHAR(150) NOT NULL,
    tax_number NVARCHAR(20),
    address NVARCHAR(200),
    phone NVARCHAR(20)
);
GO


-- =============================================
-- 3. HR SPECIALIST
-- =============================================

CREATE TABLE HR_Specialist
(
    hr_specialist_id INT PRIMARY KEY,
    full_name NVARCHAR(100) NOT NULL,
    position NVARCHAR(100),
    phone NVARCHAR(20),
    email NVARCHAR(100),

    employer_id INT NOT NULL,

    CONSTRAINT FK_HR_Specialist_Employer
        FOREIGN KEY (employer_id)
        REFERENCES Employer(employer_id)
);
GO


-- =============================================
-- 4. EMPLOYMENT BOOK
-- =============================================

CREATE TABLE Employment_Book
(
    employment_book_id INT PRIMARY KEY,
    book_number NVARCHAR(30) NOT NULL,
    issue_date DATE NOT NULL,
    closing_date DATE NULL,
    status NVARCHAR(30),
    format NVARCHAR(30)
);
GO


-- =============================================
-- 5. EMPLOYEE - EMPLOYMENT BOOK
-- =============================================

CREATE TABLE Employee_Employment_Book
(
    employee_employment_book_id INT PRIMARY KEY,

    employee_id INT NOT NULL,
    employment_book_id INT NOT NULL,

    CONSTRAINT FK_Employee_EmploymentBook_Employee
        FOREIGN KEY (employee_id)
        REFERENCES Employee(employee_id),

    CONSTRAINT FK_Employee_EmploymentBook_Book
        FOREIGN KEY (employment_book_id)
        REFERENCES Employment_Book(employment_book_id)
);
GO


-- =============================================
-- 6. EMPLOYMENT RECORD
-- =============================================

CREATE TABLE Employment_Record
(
    record_id INT PRIMARY KEY,
    record_date DATE NOT NULL,
    record_type NVARCHAR(50),
    position NVARCHAR(100),
    department NVARCHAR(100),
    document_number NVARCHAR(50),

    employment_book_id INT NOT NULL,
    employer_id INT NOT NULL,

    CONSTRAINT FK_EmploymentRecord_EmploymentBook
        FOREIGN KEY (employment_book_id)
        REFERENCES Employment_Book(employment_book_id),

    CONSTRAINT FK_EmploymentRecord_Employer
        FOREIGN KEY (employer_id)
        REFERENCES Employer(employer_id)
);
GO