CREATE TABLE IF NOT EXISTS door_cert_admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS door_cert_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  default_fire_rating VARCHAR(80) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO door_cert_categories (code, name, default_fire_rating) VALUES
('FD', 'Fire Door', 'FD 120'),
('DFD', 'Double Fire Door', 'DFD 120');

CREATE TABLE IF NOT EXISTS door_cert_standards (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO door_cert_standards (name, is_active) VALUES
('UL-10C, NFPA-252 & B.S 476 STANDARD', 1),
('UL-10C, NFPA-252, FM 4100 & B.S 476 STANDARD', 1);

CREATE TABLE IF NOT EXISTS door_cert_projects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_number VARCHAR(40) NOT NULL UNIQUE,
  client_name VARCHAR(180) NOT NULL,
  site_location VARCHAR(220) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS door_cert_doors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  door_id VARCHAR(80) NOT NULL UNIQUE,
  category_id INT UNSIGNED NOT NULL,
  project_id INT UNSIGNED NOT NULL,
  door_number VARCHAR(40) NOT NULL,
  po_number VARCHAR(80) DEFAULT NULL,
  area_location VARCHAR(220) DEFAULT NULL,
  fire_rating VARCHAR(80) DEFAULT NULL,
  standards VARCHAR(255) DEFAULT NULL,
  manufacture_date DATE DEFAULT NULL,
  valid_from DATE DEFAULT NULL,
  valid_to DATE DEFAULT NULL,
  status ENUM('active','expired','suspended') NOT NULL DEFAULT 'active',
  qr_token VARCHAR(96) NOT NULL UNIQUE,
  qr_image_path VARCHAR(255) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_door_category FOREIGN KEY (category_id) REFERENCES door_cert_categories(id),
  CONSTRAINT fk_door_project FOREIGN KEY (project_id) REFERENCES door_cert_projects(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS door_cert_download_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  door_id INT UNSIGNED NOT NULL,
  file_type ENUM('docx','pdf') NOT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_download_door FOREIGN KEY (door_id) REFERENCES door_cert_doors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
