DROP TABLE dates_of_contracts
DROP TABLE type_of_contract
DROP TABLE companies

CREATE TABLE companies (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  comp_name VARCHAR(255) NOT NULL
);

CREATE TABLE type_of_contract (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  cont_type VARCHAR(255) CHECK (cont_type IN ('employment', 'civil', 'military'))
);

CREATE TABLE dates_of_contracts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  comp_id BIGINT NOT NULL,
  type_id BIGINT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  CONSTRAINT check_dates CHECK (start_date <= end_date),
  CONSTRAINT fk_dates_of_contracts_comp_id FOREIGN KEY (comp_id) REFERENCES companies(id),
  CONSTRAINT fk_dates_of_contracts_type_id FOREIGN KEY (type_id) REFERENCES type_of_contract(id)
);

INSERT INTO companies (comp_name) VALUES
('TechCorp Solutions'),
('Global Industries Ltd'),
('Military Systems Inc'),
('Construction Pros'),
('IT Services Group');

INSERT INTO type_of_contract (cont_type) VALUES
('employment'),
('civil'),
('military');

INSERT INTO dates_of_contracts (comp_id, type_id, start_date, end_date) VALUES
(1, 1, '2023-01-15', '2024-01-14'),
(1, 2, '2024-02-01', '2024-12-31'),
(2, 1, '2022-03-10', '2025-03-09'),
(2, 2, '2023-06-01', '2023-11-30'),
(3, 3, '2024-01-01', '2026-12-31'),
(3, 1, '2023-09-15', '2024-09-14'),
(4, 2, '2024-03-01', '2024-08-31'),
(4, 1, '2023-11-01', '2024-10-31'),
(5, 1, '2024-01-10', '2025-01-09'),
(5, 2, '2023-07-15', '2023-12-20');