// Placeholder for install/schema.sql


-- PATCHED: users
CREATE TABLE IF NOT EXISTS users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) UNIQUE NOT NULL,
  full_name VARCHAR(128) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  email VARCHAR(128),
  phone VARCHAR(32),
  status ENUM('active','disabled') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL
) ENGINE=InnoDB;


-- PATCHED: roles/permissions
CREATE TABLE IF NOT EXISTS roles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) UNIQUE NOT NULL,
  description VARCHAR(255) NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS permissions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) UNIQUE NOT NULL,
  description VARCHAR(255) NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY(user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY(role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB;


-- PATCHED: activity_log/timeline_events
CREATE TABLE IF NOT EXISTS activity_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NULL,
  action VARCHAR(64) NOT NULL,
  entity VARCHAR(64) NULL,
  entity_id BIGINT NULL,
  details TEXT NULL,
  ip VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS timeline_events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  patient_id BIGINT NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  title VARCHAR(255) NOT NULL,
  details TEXT NULL,
  ref_table VARCHAR(64) NULL,
  ref_id BIGINT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- PATCHED: patients/visits
CREATE TABLE IF NOT EXISTS patients (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(128) NOT NULL,
  fin VARCHAR(7) UNIQUE,
  birth_date DATE NULL,
  gender ENUM('male','female','unknown') DEFAULT 'unknown',
  phone VARCHAR(32) NULL,
  email VARCHAR(128) NULL,
  address VARCHAR(255) NULL,
  passport_no VARCHAR(32) NULL,
  archive_no VARCHAR(32) NULL,
  military_unit_id BIGINT NULL,
  rank_id BIGINT NULL,
  position_id BIGINT NULL,
  blood_type ENUM('0','A','B','AB','unknown') DEFAULT 'unknown',
  status ENUM('active','archived','deleted') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS visits (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  patient_id BIGINT NOT NULL,
  visit_type ENUM('outpatient','inpatient') DEFAULT 'outpatient',
  dept_id BIGINT NULL,
  doctor_id BIGINT NULL,
  referred_from VARCHAR(128) NULL,
  status ENUM('registered','accepted','completed','cancelled') DEFAULT 'registered',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  FOREIGN KEY (patient_id) REFERENCES patients(id)
) ENGINE=InnoDB;


-- PATCHED: rate_limits
CREATE TABLE IF NOT EXISTS rate_limits (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  key_name VARCHAR(128) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;