CREATE TABLE organizations (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE drivers (
  organization_id CHAR(36) NOT NULL,
  id CHAR(36) NOT NULL,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (organization_id, id),
  CONSTRAINT drivers_organization_fk FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE service_jobs (
  organization_id CHAR(36) NOT NULL,
  id CHAR(36) NOT NULL,
  driver_id CHAR(36) NULL,
  company_name VARCHAR(200) NOT NULL,
  address TEXT NULL,
  service_date DATE NULL,
  preferred_service_time VARCHAR(20) NULL,
  service_type VARCHAR(150) NULL,
  google_maps_url TEXT NULL,
  status ENUM('scheduled','assigned','en_route','started','completed','cancelled') NOT NULL DEFAULT 'assigned',
  completion_notes TEXT NULL,
  completed_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (organization_id, id),
  KEY service_jobs_driver_status_idx (organization_id, driver_id, status, service_date),
  CONSTRAINT service_jobs_organization_fk FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE driver_attendance (
  organization_id CHAR(36) NOT NULL,
  driver_id CHAR(36) NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present') NOT NULL DEFAULT 'present',
  marked_at DATETIME NOT NULL,
  PRIMARY KEY (organization_id, driver_id, attendance_date),
  CONSTRAINT attendance_driver_fk FOREIGN KEY (organization_id, driver_id) REFERENCES drivers(organization_id, id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE driver_action_receipts (
  organization_id CHAR(36) NOT NULL,
  driver_id CHAR(36) NOT NULL,
  client_action_id BIGINT NOT NULL,
  action_type VARCHAR(50) NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (organization_id, driver_id, client_action_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE driver_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id CHAR(36) NOT NULL,
  driver_id CHAR(36) NOT NULL,
  job_id CHAR(36) NULL,
  event_type VARCHAR(50) NOT NULL,
  payload JSON NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY driver_events_organization_idx (organization_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO organizations (id, name) VALUES ('00000000-0000-4000-8000-000000000001', 'DrainFlow Office');