-- schema.sql (import in cPanel phpMyAdmin)
CREATE TABLE viviendas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(50) NOT NULL UNIQUE,
  direccion VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE residentes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vivienda_id INT UNSIGNED NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  email VARCHAR(160) DEFAULT NULL,
  telefono VARCHAR(30) DEFAULT NULL,
  estatus ENUM('activo','inactivo') DEFAULT 'activo',
  vigencia_pago_hasta DATETIME DEFAULT NULL,
  puede_ver_finanzas TINYINT(1) DEFAULT 0,
  CONSTRAINT fk_res_viv FOREIGN KEY (vivienda_id) REFERENCES viviendas(id)
) ENGINE=InnoDB;

CREATE TABLE usuarios (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  residente_id INT UNSIGNED DEFAULT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  rol ENUM('admin','operador','residente','lector') NOT NULL,
  estatus ENUM('activo','inactivo') DEFAULT 'activo',
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_usr_res FOREIGN KEY (residente_id) REFERENCES residentes(id)
) ENGINE=InnoDB;

CREATE TABLE lectores (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  ubicacion VARCHAR(120) DEFAULT NULL,
  jwt_kid VARCHAR(40) NOT NULL,
  jwt_public_key TEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE qr (
  uuid CHAR(36) PRIMARY KEY,
  vivienda_id INT UNSIGNED NOT NULL,
  tipo ENUM('residente','visitante') NOT NULL,
  uso_unico TINYINT(1) DEFAULT 0,
  expira_en DATETIME NOT NULL,
  consumido_at DATETIME DEFAULT NULL,
  revocado_at DATETIME DEFAULT NULL,
  firma VARCHAR(64) NOT NULL,
  creado_por INT UNSIGNED DEFAULT NULL,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_qr_viv FOREIGN KEY (vivienda_id) REFERENCES viviendas(id),
  INDEX (vivienda_id),
  INDEX (expira_en)
) ENGINE=InnoDB;

CREATE TABLE visitantes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  identificador VARCHAR(120) DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE qr_visitante (
  uuid CHAR(36) PRIMARY KEY,
  visitante_id INT UNSIGNED NOT NULL,
  ventana_inicio DATETIME NOT NULL,
  ventana_fin DATETIME NOT NULL,
  CONSTRAINT fk_qrv_qr FOREIGN KEY (uuid) REFERENCES qr(uuid) ON DELETE CASCADE,
  CONSTRAINT fk_qrv_vis FOREIGN KEY (visitante_id) REFERENCES visitantes(id)
) ENGINE=InnoDB;

CREATE TABLE eventos_acceso (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid_qr CHAR(36) NOT NULL,
  vivienda_id INT UNSIGNED NOT NULL,
  lector_id INT UNSIGNED DEFAULT NULL,
  resultado ENUM('permitido','denegado') NOT NULL,
  motivo VARCHAR(120) NOT NULL,
  ip_lector VARCHAR(45) DEFAULT NULL,
  ubicacion_lector VARCHAR(120) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  fecha_hora TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (uuid_qr), INDEX (fecha_hora)
) ENGINE=InnoDB;

CREATE TABLE pagos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  residente_id INT UNSIGNED NOT NULL,
  vivienda_id INT UNSIGNED NOT NULL,
  concepto VARCHAR(160) NOT NULL,
  monto DECIMAL(12,2) NOT NULL,
  metodo ENUM('efectivo','transferencia','tarjeta','otro') NOT NULL,
  referencia VARCHAR(160) DEFAULT NULL,
  fecha_pago DATETIME NOT NULL,
  comprobante_path VARCHAR(255) DEFAULT NULL,
  creado_por INT UNSIGNED NOT NULL,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE gastos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vivienda_id INT UNSIGNED DEFAULT NULL,
  categoria VARCHAR(80) NOT NULL,
  concepto VARCHAR(160) NOT NULL,
  monto DECIMAL(12,2) NOT NULL,
  fecha_gasto DATETIME NOT NULL,
  comprobante_path VARCHAR(255) DEFAULT NULL,
  creado_por INT UNSIGNED NOT NULL,
  creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permisos_finanzas (
  residente_id INT UNSIGNED PRIMARY KEY,
  puede_ver_pagos TINYINT(1) DEFAULT 0,
  puede_ver_gastos TINYINT(1) DEFAULT 0
) ENGINE=InnoDB;