CREATE DATABASE IF NOT EXISTS meeting_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE meeting_system;

DROP TABLE IF EXISTS booking_invitees;
DROP TABLE IF EXISTS booking_zoom_meetings;
DROP TABLE IF EXISTS zoom_integrations;
DROP TABLE IF EXISTS attendance_records;
DROP TABLE IF EXISTS attendance_sessions;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS rooms;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS organizations;

CREATE TABLE organizations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name_mn VARCHAR(160) NOT NULL,
  name_en VARCHAR(160),
  register_no VARCHAR(80) NOT NULL,
  logo_url VARCHAR(255),
  address_mn VARCHAR(255),
  address_en VARCHAR(255),
  phone VARCHAR(20),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_org_register (register_no),
  UNIQUE KEY uq_org_name_mn (name_mn)
) ENGINE=InnoDB;

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  role ENUM('super_admin', 'admin', 'staff') NOT NULL DEFAULT 'staff',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB;

CREATE TABLE departments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  name VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_departments_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uq_department_org_name (organization_id, name)
) ENGINE=InnoDB;

CREATE TABLE rooms (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  name VARCHAR(120) NOT NULL,
  location VARCHAR(255) NULL,
  capacity INT NOT NULL DEFAULT 10,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rooms_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uq_room_org_name (organization_id, name)
) ENGINE=InnoDB;

CREATE TABLE employees (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  department_id BIGINT NULL,
  employee_code VARCHAR(64) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NULL,
  office_phone VARCHAR(20) NULL,
  mobile_phone VARCHAR(20) NULL,
  room_number VARCHAR(20) NULL,
  user_id BIGINT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_employees_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_employees_dept FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_employees_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
  UNIQUE KEY uq_emp_org_code (organization_id, employee_code)
) ENGINE=InnoDB;

CREATE TABLE bookings (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  room_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  agenda TEXT NULL,
  organizer_name VARCHAR(120) NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bookings_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_bookings_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_bookings_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  INDEX idx_booking_room_time (room_id, start_time, end_time),
  INDEX idx_booking_org_range (organization_id, start_time, end_time)
) ENGINE=InnoDB;

CREATE TABLE attendance_sessions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id BIGINT NOT NULL UNIQUE,
  qr_token VARCHAR(100) NOT NULL UNIQUE,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_attendance_session_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE attendance_records (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id BIGINT NOT NULL,
  employee_id BIGINT NULL,
  employee_code VARCHAR(64) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  checked_in_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_attendance_record_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_attendance_record_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL ON UPDATE CASCADE,
  UNIQUE KEY uq_attendance_once (booking_id, employee_code),
  INDEX idx_attendance_booking (booking_id)
) ENGINE=InnoDB;

CREATE TABLE booking_invitees (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id BIGINT NOT NULL,
  employee_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_invitees_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_invitees_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uq_booking_invitee (booking_id, employee_id),
  INDEX idx_invitees_booking (booking_id)
) ENGINE=InnoDB;

CREATE TABLE zoom_integrations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  account_id VARCHAR(140) NOT NULL,
  client_id VARCHAR(140) NOT NULL,
  client_secret VARCHAR(255) NOT NULL,
  zoom_user_id VARCHAR(120) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_zoom_integrations_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uq_zoom_integrations_org (organization_id)
) ENGINE=InnoDB;

CREATE TABLE booking_zoom_meetings (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id BIGINT NOT NULL,
  organization_id BIGINT NOT NULL,
  zoom_meeting_id VARCHAR(64) NOT NULL,
  join_url VARCHAR(500) NOT NULL,
  start_url TEXT NULL,
  password VARCHAR(64) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_booking_zoom_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_booking_zoom_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uq_booking_zoom_booking (booking_id),
  UNIQUE KEY uq_booking_zoom_meeting (zoom_meeting_id),
  INDEX idx_booking_zoom_org (organization_id)
) ENGINE=InnoDB;

CREATE TABLE organization_settings (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT NOT NULL,
  work_hour_start TIME DEFAULT '09:00',
  work_hour_end TIME DEFAULT '18:00',
  default_meeting_duration INT DEFAULT 60,
  advance_booking_days INT DEFAULT 30,
  auto_approve_booking BOOLEAN DEFAULT FALSE,
  email_notifications BOOLEAN DEFAULT TRUE,
  qr_check_in_enabled BOOLEAN DEFAULT TRUE,
  auto_zoom_meeting BOOLEAN DEFAULT FALSE,
  early_check_in_minutes INT DEFAULT 15,
  late_check_in_minutes INT DEFAULT 15,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_org_settings_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE KEY uq_org_settings_org (organization_id)
) ENGINE=InnoDB;
