-- Inventario Reconteo App (MySQL 5.7+/8.0) - PHP 7.0 compatible
-- Charset
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','monitor','empleado') NOT NULL DEFAULT 'empleado',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inventory_sessions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('abierta','cerrada') NOT NULL DEFAULT 'abierta',
  PRIMARY KEY (id),
  KEY idx_sessions_created_by (created_by),
  CONSTRAINT fk_sessions_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS system_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  session_id INT UNSIGNED NOT NULL,
  item_name VARCHAR(255) NULL,
  variation VARCHAR(255) NULL,
  category VARCHAR(120) NULL,
  cost_price DECIMAL(12,2) NULL,
  selling_price DECIMAL(12,2) NULL,
  qty_system INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_system_session (session_id),
  KEY idx_system_qty (qty_system),
  CONSTRAINT fk_system_session FOREIGN KEY (session_id) REFERENCES inventory_sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS item_codes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  system_item_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(80) NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_code (code),
  KEY idx_codes_item (system_item_id),
  CONSTRAINT fk_codes_item FOREIGN KEY (system_item_id) REFERENCES system_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS physical_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  session_id INT UNSIGNED NOT NULL,
  code VARCHAR(80) NOT NULL,
  vitrina VARCHAR(120) NULL,
  counted_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_phys_session (session_id),
  KEY idx_phys_code (code),
  KEY idx_phys_vitrina (vitrina),
  CONSTRAINT fk_phys_session FOREIGN KEY (session_id) REFERENCES inventory_sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recount_tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  session_id INT UNSIGNED NOT NULL,
  code VARCHAR(80) NOT NULL,
  status ENUM('seleccionado','asignado','entregado','en_revision','listo','cancelado') NOT NULL DEFAULT 'seleccionado',
  created_by INT UNSIGNED NOT NULL,         -- admin
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_task_session (session_id),
  KEY idx_task_code (code),
  KEY idx_task_status (status),
  CONSTRAINT fk_task_session FOREIGN KEY (session_id) REFERENCES inventory_sessions(id),
  CONSTRAINT fk_task_created_by FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recount_assignments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  task_id BIGINT UNSIGNED NOT NULL,
  assigned_to_user_id INT UNSIGNED NOT NULL, -- empleado
  assigned_by_user_id INT UNSIGNED NOT NULL, -- admin o monitor
  assigned_at DATETIME NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_assign_task (task_id),
  KEY idx_assign_to (assigned_to_user_id),
  KEY idx_assign_active (active),
  CONSTRAINT fk_assign_task FOREIGN KEY (task_id) REFERENCES recount_tasks(id),
  CONSTRAINT fk_assign_to FOREIGN KEY (assigned_to_user_id) REFERENCES users(id),
  CONSTRAINT fk_assign_by FOREIGN KEY (assigned_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recount_deliveries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  task_id BIGINT UNSIGNED NOT NULL,
  delivered_by_user_id INT UNSIGNED NOT NULL, -- empleado
  delivered_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_delivery_task (task_id),
  KEY idx_delivered_by (delivered_by_user_id),
  CONSTRAINT fk_delivery_task FOREIGN KEY (task_id) REFERENCES recount_tasks(id),
  CONSTRAINT fk_delivery_by FOREIGN KEY (delivered_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recount_results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  task_id BIGINT UNSIGNED NOT NULL,
  second_count_qty INT NULL,
  decision ENUM('ok','diferencia_confirmada','pendiente') NOT NULL DEFAULT 'pendiente',
  comment TEXT NULL,
  monitor_user_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_result_task (task_id),
  KEY idx_result_decision (decision),
  CONSTRAINT fk_result_task FOREIGN KEY (task_id) REFERENCES recount_tasks(id),
  CONSTRAINT fk_result_monitor FOREIGN KEY (monitor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
