-- Kibbutz Presence Management System schema
-- Run this against your database (e.g. kibbutz_presence)

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS residents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  last_name VARCHAR(100) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  gender ENUM('ז', 'נ') DEFAULT NULL,
  age TINYINT UNSIGNED DEFAULT NULL,
  is_parent TINYINT(1) NOT NULL DEFAULT 0,
  family_affiliation VARCHAR(100) DEFAULT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(255) DEFAULT NULL,
  residency_type ENUM('חבר', 'תושב', 'אורח') NOT NULL DEFAULT 'חבר',
  is_present TINYINT(1) NOT NULL DEFAULT 0,
  last_update DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by VARCHAR(20) DEFAULT NULL COMMENT 'מספר טלפון/מזהה של מי שערך (אדמין או משתמש קצה)',
  notes TEXT,
  KEY idx_residents_phone (phone),
  KEY idx_residents_last_update (last_update)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  otp_code VARCHAR(255) DEFAULT NULL,
  otp_expiry DATETIME DEFAULT NULL,
  KEY idx_admins_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

