-- Base de datos Portal de Abogados v1.1.0-CLEAN
CREATE DATABASE IF NOT EXISTS portal_abogados CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE portal_abogados;

DROP TABLE IF EXISTS hearing_attachments;
DROP TABLE IF EXISTS hearing_notifications;
DROP TABLE IF EXISTS hearings;
DROP TABLE IF EXISTS hearing_types;
DROP TABLE IF EXISTS cases;
DROP TABLE IF EXISTS courts;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id                 INT AUTO_INCREMENT PRIMARY KEY,
  name               VARCHAR(150) NOT NULL,
  email              VARCHAR(150) NOT NULL UNIQUE,
  pj_email           VARCHAR(255),
  password_hash      VARCHAR(255) NOT NULL,
  role               ENUM('ADMIN','ABOGADO','ASISTENTE') NOT NULL DEFAULT 'ABOGADO',
  assigned_lawyer_id INT NULL,
  phone              VARCHAR(50),
  is_active          TINYINT(1) NOT NULL DEFAULT 1,
  pj_import_enabled  TINYINT(1) NOT NULL DEFAULT 1,
  mail_provider      ENUM('OFFICE365','GMAIL','OTHER') NOT NULL DEFAULT 'OFFICE365',
  mail_imap_host     VARCHAR(255) DEFAULT NULL,
  mail_imap_port     INT DEFAULT NULL,
  mail_imap_encryption ENUM('ssl','tls','none') NOT NULL DEFAULT 'ssl',
  mail_imap_user     VARCHAR(255) DEFAULT NULL,
  mail_imap_pass     VARCHAR(255) DEFAULT NULL,
  created_at         DATETIME NOT NULL,
  updated_at         DATETIME NULL,
  CONSTRAINT fk_users_assigned_lawyer FOREIGN KEY (assigned_lawyer_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE clients (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  rut        VARCHAR(20),
  email      VARCHAR(150),
  phone      VARCHAR(50),
  notes      TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE courts (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(200) NOT NULL,
  city       VARCHAR(100),
  address    VARCHAR(255),
  court_type VARCHAR(100),
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cases (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  code       VARCHAR(100) NOT NULL,
  title      VARCHAR(255) NOT NULL,
  client_id  INT NOT NULL,
  court_id   INT NULL,
  subject    VARCHAR(255),
  status     ENUM('ACTIVO','CERRADO','ARCHIVADO') DEFAULT 'ACTIVO',
  fee_total  DECIMAL(15,2) NULL,
  notes      TEXT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_cases_client FOREIGN KEY (client_id) REFERENCES clients(id),
  CONSTRAINT fk_cases_court  FOREIGN KEY (court_id)  REFERENCES courts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hearing_types (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO hearing_types (name, created_at) VALUES
 ('Audiencia Preparatoria', NOW()),
 ('Juicio Oral', NOW()),
 ('Comparendo de Conciliación', NOW()),
 ('Audiencia Única', NOW());

CREATE TABLE hearings (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  case_id         INT NOT NULL,
  lawyer_id       INT NOT NULL,
  hearing_type_id INT NOT NULL,
  date_time       DATETIME NOT NULL,
  end_date_time   DATETIME NULL,
  modality        ENUM('FISICA','ZOOM','TEAMS','GOOGLE_MEET','TELEFONICA','OTRA') NOT NULL DEFAULT 'FISICA',
  location        VARCHAR(255),
  room            VARCHAR(100),
  zoom_link       VARCHAR(255),
  city            VARCHAR(100),
  status          ENUM('PROGRAMADA','REPROGRAMADA','REALIZADA','SUSPENDIDA','CANCELADA') NOT NULL DEFAULT 'PROGRAMADA',
  notes           TEXT,
  notify_48h      TINYINT(1) NOT NULL DEFAULT 1,
  notified_48h_at DATETIME NULL,
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NULL,
  CONSTRAINT fk_hearings_case   FOREIGN KEY (case_id)   REFERENCES cases(id),
  CONSTRAINT fk_hearings_lawyer FOREIGN KEY (lawyer_id) REFERENCES users(id),
  CONSTRAINT fk_hearings_type   FOREIGN KEY (hearing_type_id) REFERENCES hearing_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hearing_notifications (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  hearing_id    INT NOT NULL,
  sent_to       VARCHAR(150) NOT NULL,
  sent_at       DATETIME NOT NULL,
  status        ENUM('OK','ERROR') NOT NULL,
  error_message TEXT,
  CONSTRAINT fk_notifications_hearing FOREIGN KEY (hearing_id) REFERENCES hearings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hearing_attachments (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  hearing_id    INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  file_name     VARCHAR(255) NOT NULL,
  file_path     VARCHAR(255) NOT NULL,
  uploaded_at   DATETIME NOT NULL,
  CONSTRAINT fk_attachments_hearing FOREIGN KEY (hearing_id) REFERENCES hearings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Usuario administrador demo (admin@demo.local / admin123)
INSERT INTO users (name, email, password_hash, role, phone, is_active, created_at)
VALUES (
  'Administrador Demo',
  'admin@demo.local',
  '$2y$10$Lk2FfZrgjFoWZqvdA0n9x.tqNNNhBW12p_w9Dnpl4LETW2lYsXtCa',
  'ADMIN',
  '',
  1,
  NOW()
);

-- Pagos / abonos por caso
CREATE TABLE case_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NOT NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  method VARCHAR(50) NOT NULL,
  notes TEXT,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_case_payments_case FOREIGN KEY (case_id) REFERENCES cases(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Adjuntos a nivel de caso
CREATE TABLE case_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  uploaded_at DATETIME NOT NULL,
  CONSTRAINT fk_case_attachments_case FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historial de importaciones desde archivos del Poder Judicial
CREATE TABLE pj_imports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  origin_user_id INT NULL,
  file_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  mail_subject VARCHAR(255),
  mail_from VARCHAR(255),
  mail_to VARCHAR(255),
  rows_total INT NOT NULL DEFAULT 0,
  rows_imported INT NOT NULL DEFAULT 0,
  rows_skipped INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  notes TEXT,
  CONSTRAINT fk_pj_imports_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_pj_imports_origin_user FOREIGN KEY (origin_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Costos / gastos asociados al caso
CREATE TABLE case_expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_id INT NOT NULL,
  expense_date DATE NOT NULL,
  concept VARCHAR(255) NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  paid_by ENUM('ESTUDIO','CLIENTE','TERCERO') DEFAULT 'ESTUDIO',
  notes TEXT,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_case_expenses_case FOREIGN KEY (case_id) REFERENCES cases(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Para instalaciones ya existentes, asegúrese de agregar la columna fee_total si no existe:
-- ALTER TABLE cases ADD COLUMN fee_total DECIMAL(15,2) NULL AFTER status;

-- Adjuntos asociados a gastos del caso
CREATE TABLE case_expense_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  expense_id INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  uploaded_at DATETIME NOT NULL,
  CONSTRAINT fk_case_expense_attachments_expense FOREIGN KEY (expense_id) REFERENCES case_expenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Para instalaciones ya existentes, cree esta tabla de forma manual si es necesario.

-- Notas para instalaciones ya existentes
-- Ajustar tabla users para correo IMAP por usuario y asociación Asistente -> Abogado:
-- ALTER TABLE users
--   ADD COLUMN pj_email VARCHAR(255) AFTER email,
--   ADD COLUMN assigned_lawyer_id INT NULL AFTER role,
--   ADD COLUMN pj_import_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER is_active,
--   ADD COLUMN mail_provider ENUM('OFFICE365','GMAIL','OTHER') NOT NULL DEFAULT 'OFFICE365' AFTER pj_import_enabled,
--   ADD COLUMN mail_imap_host VARCHAR(255) DEFAULT NULL AFTER mail_provider,
--   ADD COLUMN mail_imap_port INT DEFAULT NULL AFTER mail_imap_host,
--   ADD COLUMN mail_imap_encryption ENUM('ssl','tls','none') NOT NULL DEFAULT 'ssl' AFTER mail_imap_port,
--   ADD COLUMN mail_imap_user VARCHAR(255) DEFAULT NULL AFTER mail_imap_encryption,
--   ADD COLUMN mail_imap_pass VARCHAR(255) DEFAULT NULL AFTER mail_imap_user,
--   ADD CONSTRAINT fk_users_assigned_lawyer FOREIGN KEY (assigned_lawyer_id) REFERENCES users(id);

-- Ajustar tabla pj_imports para guardar información básica del correo y el usuario origen:
-- ALTER TABLE pj_imports
--   ADD COLUMN origin_user_id INT NULL AFTER user_id,
--   ADD COLUMN mail_subject VARCHAR(255) AFTER stored_path,
--   ADD COLUMN mail_from VARCHAR(255) AFTER mail_subject,
--   ADD COLUMN mail_to VARCHAR(255) AFTER mail_from,
--   ADD CONSTRAINT fk_pj_imports_origin_user FOREIGN KEY (origin_user_id) REFERENCES users(id);