Untitled

avatar
unknown
mysql
2 months ago
53 kB
5
Indexable
-- V1__Comprehensive_Schema.sql

-- Comprehensive ERP Schema for Altec Mechanical Group
-- Includes indexes for performance optimization and liberal application-time periods

SET NAMES utf8mb4;

-- Shift Type Table
CREATE TABLE shift_type (
  shift_type_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Initial shift types
INSERT INTO shift_type (name, start_time, end_time) VALUES
  ('Morning', '07:00:00', '15:00:00'),
  ('Regular', '09:00:00', '17:00:00'),
  ('Late',    '11:00:00', '19:00:00');

-- Role Table
CREATE TABLE role (
  role_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Add an index on name for faster lookups
CREATE INDEX idx_role_name ON role (name);

-- Permission Table
CREATE TABLE permission (
  permission_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Add an index on name for faster lookups
CREATE INDEX idx_permission_name ON permission (name);

-- Role Hierarchy Table
CREATE TABLE role_hierarchy (
  parent_role_id INT NOT NULL,
  child_role_id INT NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  PRIMARY KEY (parent_role_id, child_role_id, effective_from),
  FOREIGN KEY (parent_role_id) REFERENCES role(role_id),
  FOREIGN KEY (child_role_id) REFERENCES role(role_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Account Type Table
CREATE TABLE account_type (
  account_type_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Add an index on name for faster lookups
CREATE INDEX idx_account_type_name ON account_type (name);

-- Transaction Type Lookup Table
CREATE TABLE transaction_type (
  transaction_type_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Add an index on name for faster lookups
CREATE INDEX idx_transaction_type_name ON transaction_type (name);

-- Event Type Lookup Table
CREATE TABLE event_type (
  event_type_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Add an index on name for faster lookups
CREATE INDEX idx_event_type_name ON event_type (name);

-- Artifact Type Lookup Table
CREATE TABLE artifact_type (
  artifact_type_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Add an index on name for faster lookups
CREATE INDEX idx_artifact_type_name ON artifact_type (name);

-- Entity Table for Multi-Entity Support
CREATE TABLE entity (
  entity_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Add index on is_active for quick filtering
CREATE INDEX idx_entity_is_active ON entity (is_active);

-- Main Tables

-- User Account Table
CREATE TABLE user_account (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) DEFAULT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  avatar_url VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_anonymized TINYINT(1) NOT NULL DEFAULT 0,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for User Account
CREATE INDEX idx_user_account_username ON user_account (username);
CREATE INDEX idx_user_account_email ON user_account (email);
CREATE INDEX idx_user_account_is_active ON user_account (is_active);

-- User Permission Table
CREATE TABLE user_permission (
  user_id INT NOT NULL,
  permission_id INT NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  PRIMARY KEY (user_id, permission_id, effective_from),
  FOREIGN KEY (user_id) REFERENCES user_account(user_id),
  FOREIGN KEY (permission_id) REFERENCES permission(permission_id),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Worker Table
CREATE TABLE worker (
  worker_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL UNIQUE,
  employee_code VARCHAR(50) UNIQUE,
  hire_date DATE DEFAULT NULL,
  termination_date DATE DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (user_id) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Worker
CREATE INDEX idx_worker_user_id ON worker (user_id);
CREATE INDEX idx_worker_employee_code ON worker (employee_code);

-- User Role Table
CREATE TABLE user_role (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  PRIMARY KEY (user_id, role_id, effective_from),
  FOREIGN KEY (user_id) REFERENCES user_account(user_id),
  FOREIGN KEY (role_id) REFERENCES role(role_id),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Role Permission Table
CREATE TABLE role_permission (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  PRIMARY KEY (role_id, permission_id, effective_from),
  FOREIGN KEY (role_id) REFERENCES role(role_id),
  FOREIGN KEY (permission_id) REFERENCES permission(permission_id),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Crew Table
CREATE TABLE crew (
  crew_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Crew
CREATE INDEX idx_crew_name ON crew (name);
CREATE INDEX idx_crew_is_active ON crew (is_active);

-- Crew Shift Table
CREATE TABLE crew_shift (
  crew_shift_id INT AUTO_INCREMENT PRIMARY KEY,
  crew_id INT NOT NULL,
  shift_type_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (crew_id) REFERENCES crew(crew_id),
  FOREIGN KEY (shift_type_id) REFERENCES shift_type(shift_type_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Crew Shift
CREATE INDEX idx_crew_shift_crew_id ON crew_shift (crew_id);
CREATE INDEX idx_crew_shift_shift_type_id ON crew_shift (shift_type_id);
CREATE INDEX idx_crew_shift_start_date ON crew_shift (start_date);

-- Crew Member Table
CREATE TABLE crew_member (
  crew_member_id INT AUTO_INCREMENT PRIMARY KEY,
  crew_id INT NOT NULL,
  worker_id INT NOT NULL,
  role_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (crew_id) REFERENCES crew(crew_id),
  FOREIGN KEY (worker_id) REFERENCES worker(worker_id),
  FOREIGN KEY (role_id) REFERENCES role(role_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Crew Member
CREATE INDEX idx_crew_member_crew_id ON crew_member (crew_id);
CREATE INDEX idx_crew_member_worker_id ON crew_member (worker_id);
CREATE INDEX idx_crew_member_role_id ON crew_member (role_id);

-- Shift Change Request Table
CREATE TABLE shift_change_request (
  request_id INT AUTO_INCREMENT PRIMARY KEY,
  worker_id INT NOT NULL,
  from_shift_type_id INT NOT NULL,
  to_shift_type_id INT NOT NULL,
  requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_by INT DEFAULT NULL,
  processed_at TIMESTAMP DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  current_state VARCHAR(50) NOT NULL DEFAULT 'INITIATED',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  FOREIGN KEY (worker_id) REFERENCES worker(worker_id),
  FOREIGN KEY (from_shift_type_id) REFERENCES shift_type(shift_type_id),
  FOREIGN KEY (to_shift_type_id) REFERENCES shift_type(shift_type_id),
  FOREIGN KEY (processed_by) REFERENCES user_account(user_id),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Shift Change Request
CREATE INDEX idx_shift_change_request_worker_id ON shift_change_request (worker_id);
CREATE INDEX idx_shift_change_request_current_state ON shift_change_request (current_state);

-- Job Site Table
CREATE TABLE job_site (
  job_site_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  address VARCHAR(255) DEFAULT NULL,
  latitude DECIMAL(10,7) DEFAULT NULL,
  longitude DECIMAL(10,7) DEFAULT NULL,
  created_by INT NOT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (created_by) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Job Site
CREATE INDEX idx_job_site_name ON job_site (name);
CREATE INDEX idx_job_site_created_by ON job_site (created_by);

-- Timeline Event Table
CREATE TABLE timeline_event (
  event_id INT AUTO_INCREMENT PRIMARY KEY,
  job_site_id INT NOT NULL,
  event_type_id INT NOT NULL,
  reference_id INT NOT NULL,
  event_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (job_site_id) REFERENCES job_site(job_site_id),
  FOREIGN KEY (event_type_id) REFERENCES event_type(event_type_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Timeline Event
CREATE INDEX idx_timeline_event_job_site_id ON timeline_event (job_site_id);
CREATE INDEX idx_timeline_event_event_type_id ON timeline_event (event_type_id);
CREATE INDEX idx_timeline_event_reference_id ON timeline_event (reference_id);

-- Post Table
CREATE TABLE post (
  post_id INT AUTO_INCREMENT PRIMARY KEY,
  job_site_id INT NOT NULL,
  author_id INT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_public TINYINT(1) NOT NULL DEFAULT 0,
  public_access_token VARCHAR(64) DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (job_site_id) REFERENCES job_site(job_site_id),
  FOREIGN KEY (author_id) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Post
CREATE INDEX idx_post_job_site_id ON post (job_site_id);
CREATE INDEX idx_post_author_id ON post (author_id);
CREATE INDEX idx_post_created_at ON post (created_at);

-- Daily Report Table
CREATE TABLE daily_report (
  report_id INT AUTO_INCREMENT PRIMARY KEY,
  worker_id INT NOT NULL,
  job_site_id INT NOT NULL,
  report_date DATE NOT NULL,
  summary TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (worker_id) REFERENCES worker(worker_id),
  FOREIGN KEY (job_site_id) REFERENCES job_site(job_site_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Daily Report
CREATE INDEX idx_daily_report_worker_id ON daily_report (worker_id);
CREATE INDEX idx_daily_report_job_site_id ON daily_report (job_site_id);
CREATE INDEX idx_daily_report_report_date ON daily_report (report_date);

-- Artifact Table
CREATE TABLE artifact (
  artifact_id INT AUTO_INCREMENT PRIMARY KEY,
  artifact_type_id INT NOT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  media_url VARCHAR(255) DEFAULT NULL,
  content TEXT DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (artifact_type_id) REFERENCES artifact_type(artifact_type_id),
  FOREIGN KEY (created_by) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Artifact
CREATE INDEX idx_artifact_artifact_type_id ON artifact (artifact_type_id);
CREATE INDEX idx_artifact_created_by ON artifact (created_by);
CREATE INDEX idx_artifact_created_at ON artifact (created_at);

-- GPS Log Entry Table
CREATE TABLE gps_log_entry (
  gps_log_entry_id INT AUTO_INCREMENT PRIMARY KEY,
  worker_id INT NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  log_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  accuracy DECIMAL(5,2) DEFAULT NULL,
  altitude DECIMAL(8,2) DEFAULT NULL,
  speed DECIMAL(5,2) DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  is_clock_in_out TINYINT(1) NOT NULL DEFAULT 0,
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (worker_id) REFERENCES worker(worker_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for GPS Log Entry
CREATE INDEX idx_gps_log_entry_worker_id ON gps_log_entry (worker_id);
CREATE INDEX idx_gps_log_entry_log_timestamp ON gps_log_entry (log_timestamp);
CREATE INDEX idx_gps_log_entry_is_clock_in_out ON gps_log_entry (is_clock_in_out);

-- Post Artifact Mapping
CREATE TABLE post_artifact (
  post_id INT NOT NULL,
  artifact_id INT NOT NULL,
  PRIMARY KEY (post_id, artifact_id),
  FOREIGN KEY (post_id) REFERENCES post(post_id),
  FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Daily Report Artifact Mapping
CREATE TABLE daily_report_artifact (
  report_id INT NOT NULL,
  artifact_id INT NOT NULL,
  PRIMARY KEY (report_id, artifact_id),
  FOREIGN KEY (report_id) REFERENCES daily_report(report_id),
  FOREIGN KEY (artifact_id) REFERENCES artifact(artifact_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Blueprint Table
CREATE TABLE blueprint (
  blueprint_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  description TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Blueprint
CREATE INDEX idx_blueprint_name ON blueprint (name);

-- Blueprint Segment Table
CREATE TABLE blueprint_segment (
  segment_id INT AUTO_INCREMENT PRIMARY KEY,
  blueprint_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (blueprint_id) REFERENCES blueprint(blueprint_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Blueprint Segment
CREATE INDEX idx_blueprint_segment_blueprint_id ON blueprint_segment (blueprint_id);
CREATE INDEX idx_blueprint_segment_name ON blueprint_segment (name);

-- Task Table
CREATE TABLE task (
  task_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  description TEXT DEFAULT NULL,
  estimated_hours DECIMAL(5,2) DEFAULT NULL,
  trade VARCHAR(50) DEFAULT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  blueprint_segment_id INT DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (created_by) REFERENCES user_account(user_id),
  FOREIGN KEY (blueprint_segment_id) REFERENCES blueprint_segment(segment_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Task
CREATE INDEX idx_task_name ON task (name);
CREATE INDEX idx_task_created_by ON task (created_by);
CREATE INDEX idx_task_blueprint_segment_id ON task (blueprint_segment_id);

-- Task Dependency Table
CREATE TABLE task_dependency (
  task_id INT NOT NULL,
  depends_on_task_id INT NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  PRIMARY KEY (task_id, depends_on_task_id, effective_from),
  FOREIGN KEY (task_id) REFERENCES task(task_id),
  FOREIGN KEY (depends_on_task_id) REFERENCES task(task_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Assignment Table
CREATE TABLE assignment (
  assignment_id INT AUTO_INCREMENT PRIMARY KEY,
  crew_id INT NOT NULL,
  scheduled_date DATE NOT NULL,
  shift_type_id INT NOT NULL,
  notes TEXT DEFAULT NULL,
  assigned_by INT NOT NULL,
  assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (crew_id) REFERENCES crew(crew_id),
  FOREIGN KEY (shift_type_id) REFERENCES shift_type(shift_type_id),
  FOREIGN KEY (assigned_by) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Assignment
CREATE INDEX idx_assignment_crew_id ON assignment (crew_id);
CREATE INDEX idx_assignment_scheduled_date ON assignment (scheduled_date);
CREATE INDEX idx_assignment_shift_type_id ON assignment (shift_type_id);

-- Assignment Task Mapping
CREATE TABLE assignment_task (
  assignment_id INT NOT NULL,
  task_id INT NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  PRIMARY KEY (assignment_id, task_id, effective_from),
  FOREIGN KEY (assignment_id) REFERENCES assignment(assignment_id),
  FOREIGN KEY (task_id) REFERENCES task(task_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Weekly Timesheet Table
CREATE TABLE weekly_timesheet (
  timesheet_id INT AUTO_INCREMENT PRIMARY KEY,
  worker_id INT NOT NULL,
  week_start_date DATE NOT NULL,
  submitted_at TIMESTAMP DEFAULT NULL,
  approved_by INT DEFAULT NULL,
  approved_at TIMESTAMP DEFAULT NULL,
  total_hours DECIMAL(5,2) DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'PENDING',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (worker_id) REFERENCES worker(worker_id),
  FOREIGN KEY (approved_by) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Weekly Timesheet
CREATE INDEX idx_weekly_timesheet_worker_id ON weekly_timesheet (worker_id);
CREATE INDEX idx_weekly_timesheet_week_start_date ON weekly_timesheet (week_start_date);
CREATE INDEX idx_weekly_timesheet_current_state ON weekly_timesheet (current_state);

-- Time Entry Table
CREATE TABLE time_entry (
  time_entry_id INT AUTO_INCREMENT PRIMARY KEY,
  timesheet_id INT NOT NULL,
  date DATE NOT NULL,
  clock_in_time TIMESTAMP DEFAULT NULL,
  clock_out_time TIMESTAMP DEFAULT NULL,
  clock_in_gps_log_entry_id INT DEFAULT NULL,
  clock_out_gps_log_entry_id INT DEFAULT NULL,
  shift_type_id INT NOT NULL,
  approved_by INT DEFAULT NULL,
  approved_at TIMESTAMP DEFAULT NULL,
  total_hours DECIMAL(5,2) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'PENDING',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (timesheet_id) REFERENCES weekly_timesheet(timesheet_id),
  FOREIGN KEY (clock_in_gps_log_entry_id) REFERENCES gps_log_entry(gps_log_entry_id),
  FOREIGN KEY (clock_out_gps_log_entry_id) REFERENCES gps_log_entry(gps_log_entry_id),
  FOREIGN KEY (shift_type_id) REFERENCES shift_type(shift_type_id),
  FOREIGN KEY (approved_by) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Time Entry
CREATE INDEX idx_time_entry_timesheet_id ON time_entry (timesheet_id);
CREATE INDEX idx_time_entry_date ON time_entry (date);
CREATE INDEX idx_time_entry_shift_type_id ON time_entry (shift_type_id);
CREATE INDEX idx_time_entry_current_state ON time_entry (current_state);

-- Inventory Item Table
CREATE TABLE inventory_item (
  item_id INT AUTO_INCREMENT PRIMARY KEY,
  item_code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  description TEXT DEFAULT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  reorder_level DECIMAL(10,2) DEFAULT NULL,
  safety_stock_level DECIMAL(10,2) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Inventory Item
CREATE INDEX idx_inventory_item_item_code ON inventory_item (item_code);
CREATE INDEX idx_inventory_item_name ON inventory_item (name);
CREATE INDEX idx_inventory_item_is_active ON inventory_item (is_active);

-- Inventory Location Table
CREATE TABLE inventory_location (
  location_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  address VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Inventory Location
CREATE INDEX idx_inventory_location_name ON inventory_location (name);
CREATE INDEX idx_inventory_location_is_active ON inventory_location (is_active);

-- Inventory Stock Table
CREATE TABLE inventory_stock (
  stock_id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  location_id INT NOT NULL,
  quantity_on_hand DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  serial_number VARCHAR(20) DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  FOREIGN KEY (item_id) REFERENCES inventory_item(item_id),
  FOREIGN KEY (location_id) REFERENCES inventory_location(location_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Inventory Stock
CREATE INDEX idx_inventory_stock_item_id ON inventory_stock (item_id);
CREATE INDEX idx_inventory_stock_serial_number ON inventory_stock (serial_number);

-- Inventory Transaction Table
CREATE TABLE inventory_transaction (
  transaction_id INT AUTO_INCREMENT PRIMARY KEY,
  journal_entry_id INT DEFAULT NULL,
  item_id INT NOT NULL,
  from_location_id INT DEFAULT NULL,
  to_location_id INT DEFAULT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  transaction_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  transaction_type_id INT NOT NULL,
  initiated_by INT NOT NULL,
  notes TEXT DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (item_id) REFERENCES inventory_item(item_id),
  FOREIGN KEY (from_location_id) REFERENCES inventory_location(location_id),
  FOREIGN KEY (to_location_id) REFERENCES inventory_location(location_id),
  FOREIGN KEY (initiated_by) REFERENCES user_account(user_id),
  FOREIGN KEY (transaction_type_id) REFERENCES transaction_type(transaction_type_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Inventory Transaction
CREATE INDEX idx_inventory_transaction_item_id ON inventory_transaction (item_id);
CREATE INDEX idx_inventory_transaction_transaction_type_id ON inventory_transaction (transaction_type_id);
CREATE INDEX idx_inventory_transaction_transaction_timestamp ON inventory_transaction (transaction_timestamp);

-- Supplier Table
CREATE TABLE supplier (
  supplier_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  contact_info TEXT DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Supplier
CREATE INDEX idx_supplier_name ON supplier (name);

-- Supplier Item Mapping
CREATE TABLE supplier_item (
  supplier_item_id INT AUTO_INCREMENT PRIMARY KEY,
  supplier_id INT NOT NULL,
  inventory_item_id INT NOT NULL,
  confirmed_price DECIMAL(10,2) NOT NULL,
  last_confirmed_date DATE NOT NULL,
  lead_time_days INT DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id),
  FOREIGN KEY (inventory_item_id) REFERENCES inventory_item(item_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Supplier Item
CREATE INDEX idx_supplier_item_supplier_id ON supplier_item (supplier_id);
CREATE INDEX idx_supplier_item_inventory_item_id ON supplier_item (inventory_item_id);

-- Reorder Table
CREATE TABLE reorder (
  request_id INT AUTO_INCREMENT PRIMARY KEY,
  inventory_item_id INT NOT NULL,
  supplier_id INT NOT NULL,
  requested_quantity DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (inventory_item_id) REFERENCES inventory_item(item_id),
  FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Reorder
CREATE INDEX idx_reorder_inventory_item_id ON reorder (inventory_item_id);
CREATE INDEX idx_reorder_supplier_id ON reorder (supplier_id);

-- Purchase Order Table
CREATE TABLE purchase_order (
  purchase_order_id INT AUTO_INCREMENT PRIMARY KEY,
  supplier_id INT NOT NULL,
  order_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expected_delivery_date TIMESTAMP DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Purchase Order
CREATE INDEX idx_purchase_order_supplier_id ON purchase_order (supplier_id);
CREATE INDEX idx_purchase_order_current_state ON purchase_order (current_state);

-- Purchase Order Line Table
CREATE TABLE purchase_order_line (
  line_id INT AUTO_INCREMENT PRIMARY KEY,
  purchase_order_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  received_quantity DECIMAL(10,2) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  FOREIGN KEY (purchase_order_id) REFERENCES purchase_order(purchase_order_id),
  FOREIGN KEY (item_id) REFERENCES inventory_item(item_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Purchase Order Line
CREATE INDEX idx_purchase_order_line_purchase_order_id ON purchase_order_line (purchase_order_id);
CREATE INDEX idx_purchase_order_line_item_id ON purchase_order_line (item_id);

-- Return Transaction Table
CREATE TABLE return_transaction (
  return_id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  return_reason TEXT DEFAULT NULL,
  return_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  initiated_by INT NOT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'INITIATED',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (item_id) REFERENCES inventory_item(item_id),
  FOREIGN KEY (initiated_by) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Return Transaction
CREATE INDEX idx_return_transaction_item_id ON return_transaction (item_id);
CREATE INDEX idx_return_transaction_initiated_by ON return_transaction (initiated_by);
CREATE INDEX idx_return_transaction_current_state ON return_transaction (current_state);

-- Account Table
CREATE TABLE account (
  account_id INT AUTO_INCREMENT PRIMARY KEY,
  entity_id INT NOT NULL,
  account_code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  account_type_id INT NOT NULL,
  parent_account_id INT DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (effective_from, effective_to),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
  FOREIGN KEY (entity_id) REFERENCES entity(entity_id),
  FOREIGN KEY (account_type_id) REFERENCES account_type(account_type_id),
  FOREIGN KEY (parent_account_id) REFERENCES account(account_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Account
CREATE INDEX idx_account_account_code ON account (account_code);
CREATE INDEX idx_account_name ON account (name);
CREATE INDEX idx_account_account_type_id ON account (account_type_id);
CREATE INDEX idx_account_is_active ON account (is_active);

-- Journal Entry Table
CREATE TABLE journal_entry (
  journal_entry_id INT AUTO_INCREMENT PRIMARY KEY,
  entity_id INT NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  entry_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT NOT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to),
  FOREIGN KEY (entity_id) REFERENCES entity(entity_id),
  FOREIGN KEY (created_by) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Journal Entry
CREATE INDEX idx_journal_entry_entity_id ON journal_entry (entity_id);
CREATE INDEX idx_journal_entry_created_by ON journal_entry (created_by);
CREATE INDEX idx_journal_entry_entry_timestamp ON journal_entry (entry_timestamp);

-- Journal Entry Line Table
CREATE TABLE journal_entry_line (
  journal_entry_line_id INT AUTO_INCREMENT PRIMARY KEY,
  journal_entry_id INT NOT NULL,
  account_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  is_debit TINYINT(1) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entry(journal_entry_id),
  FOREIGN KEY (account_id) REFERENCES account(account_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Journal Entry Line
CREATE INDEX idx_journal_entry_line_journal_entry_id ON journal_entry_line (journal_entry_id);
CREATE INDEX idx_journal_entry_line_account_id ON journal_entry_line (account_id);

-- Journal Entry Mapping Table
CREATE TABLE journal_entry_mapping (
  mapping_id INT AUTO_INCREMENT PRIMARY KEY,
  entity_type VARCHAR(50) NOT NULL,
  entity_id INT NOT NULL,
  journal_entry_id INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entry(journal_entry_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to   TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Journal Entry Mapping
CREATE INDEX idx_journal_entry_mapping_entity_type_entity_id ON journal_entry_mapping (entity_type, entity_id);
CREATE INDEX idx_journal_entry_mapping_journal_entry_id ON journal_entry_mapping (journal_entry_id);

-- Vehicle Table
CREATE TABLE vehicle (
  vehicle_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  purchase_date DATE NOT NULL,
  cost DECIMAL(10,2) NOT NULL,
  depreciation DECIMAL(10,2) DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'ACTIVE',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Vehicle
CREATE INDEX idx_vehicle_name ON vehicle (name);
CREATE INDEX idx_vehicle_current_state ON vehicle (current_state);

-- Vehicle Maintenance Table
CREATE TABLE vehicle_maintenance (
  maintenance_id INT AUTO_INCREMENT PRIMARY KEY,
  vehicle_id INT NOT NULL,
  maintenance_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  description TEXT DEFAULT NULL,
  cost DECIMAL(10,2) NOT NULL,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to),
  FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Vehicle Maintenance
CREATE INDEX idx_vehicle_maintenance_vehicle_id ON vehicle_maintenance (vehicle_id);
CREATE INDEX idx_vehicle_maintenance_maintenance_timestamp ON vehicle_maintenance (maintenance_timestamp);

-- Estimate Table
CREATE TABLE estimate (
  estimate_id INT AUTO_INCREMENT PRIMARY KEY,
  task_id INT DEFAULT NULL,
  details TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  FOREIGN KEY (task_id) REFERENCES task(task_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Estimate
CREATE INDEX idx_estimate_task_id ON estimate (task_id);
CREATE INDEX idx_estimate_current_state ON estimate (current_state);

-- Proposal Table
CREATE TABLE proposal (
  proposal_id INT AUTO_INCREMENT PRIMARY KEY,
  estimate_id INT DEFAULT NULL,
  details TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  FOREIGN KEY (estimate_id) REFERENCES estimate(estimate_id) ON DELETE SET NULL,
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Proposal
CREATE INDEX idx_proposal_estimate_id ON proposal (estimate_id);
CREATE INDEX idx_proposal_current_state ON proposal (current_state);

-- Bid Table
CREATE TABLE bid (
  bid_id INT AUTO_INCREMENT PRIMARY KEY,
  proposal_id INT DEFAULT NULL,
  amount DECIMAL(10,2) NOT NULL,
  details TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'PREPARED',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  FOREIGN KEY (proposal_id) REFERENCES proposal(proposal_id) ON DELETE SET NULL,
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Bid
CREATE INDEX idx_bid_proposal_id ON bid (proposal_id);
CREATE INDEX idx_bid_current_state ON bid (current_state);

-- Contract Table
CREATE TABLE contract (
  contract_id INT AUTO_INCREMENT PRIMARY KEY,
  bid_id INT DEFAULT NULL,
  details TEXT DEFAULT NULL,
  effective_date DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  current_state VARCHAR(50) NOT NULL DEFAULT 'DRAFT',
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  FOREIGN KEY (bid_id) REFERENCES bid(bid_id) ON DELETE SET NULL,
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Contract
CREATE INDEX idx_contract_bid_id ON contract (bid_id);
CREATE INDEX idx_contract_current_state ON contract (current_state);

-- Notification Table
CREATE TABLE notification (
  notification_id INT AUTO_INCREMENT PRIMARY KEY,
  recipient_user_id INT NOT NULL,
  message TEXT NOT NULL,
  notification_type VARCHAR(50) NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  FOREIGN KEY (recipient_user_id) REFERENCES user_account(user_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Notification
CREATE INDEX idx_notification_recipient_user_id ON notification (recipient_user_id);
CREATE INDEX idx_notification_is_read ON notification (is_read);

-- Points Transaction Table
CREATE TABLE points_transaction (
  transaction_id INT AUTO_INCREMENT PRIMARY KEY,
  worker_id INT NOT NULL,
  points INT NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period(effective_from, effective_to),
  FOREIGN KEY (worker_id) REFERENCES worker(worker_id),
  valid_from TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME(valid_from, valid_to)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Points Transaction
CREATE INDEX idx_points_transaction_worker_id ON points_transaction (worker_id);
CREATE INDEX idx_points_transaction_timestamp ON points_transaction (timestamp);

-- Client Log Entry Table
CREATE TABLE client_log_entry (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,
  device_id VARCHAR(255) DEFAULT NULL,
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  log_level VARCHAR(50) NOT NULL,
  message TEXT NOT NULL,
  exception TEXT DEFAULT NULL,
  stack_trace TEXT DEFAULT NULL,
  app_version VARCHAR(50) DEFAULT NULL,
  os_version VARCHAR(50) DEFAULT NULL,
  device_model VARCHAR(100) DEFAULT NULL,
  additional_data JSON DEFAULT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  effective_from DATE NOT NULL DEFAULT '1970-01-01',
  effective_to DATE NOT NULL DEFAULT '9999-12-31',
  PERIOD FOR valid_period (`effective_from`, `effective_to`),
  valid_from  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
  valid_to    TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (`valid_from`, `valid_to`),
  FOREIGN KEY (user_id) REFERENCES user_account(user_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
WITH SYSTEM VERSIONING;

-- Indexes for Client Log Entry
CREATE INDEX idx_client_log_entry_user_id ON client_log_entry (user_id);
CREATE INDEX idx_client_log_entry_device_id ON client_log_entry (device_id);
CREATE INDEX idx_client_log_entry_timestamp ON client_log_entry (timestamp);
CREATE INDEX idx_client_log_entry_log_level ON client_log_entry (log_level);

-- Final Notes
-- System-versioned tables store row-level history with explicit versioning columns.
-- Application-time periods added to enable business logic based on validity periods.
-- Soft deletes implemented with is_deleted columns.
-- Time zone awareness enabled by using TIMESTAMP data types.
-- Multi-entity support for future subsidiaries.

-- End of V1__Comprehensive_Schema.sql
Editor is loading...
Leave a Comment