-- MySQL 8.0+ (ajustável para 5.7 se necessário)
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32) NOT NULL UNIQUE
);

INSERT IGNORE INTO roles (id, name) VALUES
(1,'admin'),(2,'gestor'),(3,'operador'),(4,'leitor');

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY(user_id, role_id),
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS entities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_no VARCHAR(50) DEFAULT NULL,
  class VARCHAR(50) DEFAULT NULL,
  name VARCHAR(200) NOT NULL,
  cnpj VARCHAR(32) DEFAULT NULL,
  address VARCHAR(200) DEFAULT NULL,
  contact_name VARCHAR(120) DEFAULT NULL,
  phone VARCHAR(64) DEFAULT NULL,
  email VARCHAR(160) DEFAULT NULL,
  collaborators TEXT NULL,
  notes TEXT NULL,
  archived_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS partners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  cnpj VARCHAR(32) DEFAULT NULL,
  address VARCHAR(200) DEFAULT NULL,
  contact_name VARCHAR(120) DEFAULT NULL,
  phone VARCHAR(64) DEFAULT NULL,
  email VARCHAR(160) DEFAULT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS attendances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  birth_date DATE NULL,
  entity_id INT NULL,
  school_class VARCHAR(60) NULL,
  responsible_name VARCHAR(160) NULL,
  symptoms TEXT NULL,
  pretest_date DATE NULL,
  pretest_result VARCHAR(120) NULL,
  pre_od VARCHAR(64) NULL,
  pre_oe VARCHAR(64) NULL,
  clinic_date DATE NULL,
  clinic_partner_id INT NULL,
  clinic_od VARCHAR(64) NULL,
  clinic_oe VARCHAR(64) NULL,
  clinic_notes TEXT NULL,
  glasses_delivery_date DATE NULL,
  deleted_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_att_entity FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE SET NULL,
  CONSTRAINT fk_att_partner FOREIGN KEY (clinic_partner_id) REFERENCES partners(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(80) NOT NULL,
  detail TEXT NULL,
  ip_addr VARCHAR(64) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id, action, created_at)
);

-- Usuário admin (senha será definida pelo painel Admin -> editar usuário)
INSERT INTO users (id, name, email, is_active) VALUES (1,'Administrador','admin@rotaryitapema.org.br',1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT IGNORE INTO user_roles (user_id, role_id) VALUES (1,1);