-- phpMyAdmin SQL Dump
-- version 5.2.1deb3
-- https://www.phpmyadmin.net/
--
-- Servidor: galappxy-admin.cluster-co7jnudpcxvn.us-east-1.rds.amazonaws.com:3306
-- Tiempo de generación: 15-02-2026 a las 15:03:40
-- Versión del servidor: 8.0.39
-- Versión de PHP: 8.4.6

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Base de datos: `galappxy_auth`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `AUTH_POLICY`
--

CREATE TABLE `AUTH_POLICY` (
  `id` bigint UNSIGNED NOT NULL,
  `id_company` bigint UNSIGNED DEFAULT NULL,
  `id_branch` bigint UNSIGNED DEFAULT NULL,
  `allow_user_email` tinyint(1) NOT NULL DEFAULT '1',
  `allow_user_phone` tinyint NOT NULL DEFAULT '0',
  `allow_profile_username` tinyint(1) NOT NULL DEFAULT '1',
  `allow_profile_code` tinyint(1) NOT NULL DEFAULT '1',
  `require_verified_email` tinyint(1) NOT NULL DEFAULT '0',
  `require_verified_phone` tinyint(1) NOT NULL DEFAULT '0',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `DOMAIN`
--

CREATE TABLE `DOMAIN` (
  `id` bigint UNSIGNED NOT NULL,
  `host` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cookie_root` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('active','inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `type` enum('neutral','company','app') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'neutral',
  `id_company` bigint UNSIGNED DEFAULT NULL,
  `id_brand` bigint UNSIGNED DEFAULT NULL,
  `login_mode` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ui_json` json DEFAULT NULL,
  `branches_cache_json` json DEFAULT NULL,
  `branches_cache_updated_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `LOGIN_ATTEMPT`
--

CREATE TABLE `LOGIN_ATTEMPT` (
  `id` bigint UNSIGNED NOT NULL,
  `identifier` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `identifier_type` enum('email','phone','username','code') COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_company` bigint UNSIGNED DEFAULT NULL,
  `ip` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT '0',
  `failure_reason` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `PASSWORD_HISTORY`
--

CREATE TABLE `PASSWORD_HISTORY` (
  `id` bigint UNSIGNED NOT NULL,
  `id_user` bigint UNSIGNED NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `PROFILE`
--

CREATE TABLE `PROFILE` (
  `id` bigint UNSIGNED NOT NULL,
  `id_user` bigint UNSIGNED DEFAULT NULL,
  `id_core_user` bigint UNSIGNED NOT NULL,
  `id_core_profile` bigint UNSIGNED NOT NULL,
  `id_company` bigint UNSIGNED NOT NULL,
  `id_branch_primary` bigint UNSIGNED NOT NULL,
  `code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `username` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('active','inactive','blocked','deleted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `REFRESH_TOKEN`
--

CREATE TABLE `REFRESH_TOKEN` (
  `id` bigint UNSIGNED NOT NULL,
  `id_user` bigint UNSIGNED NOT NULL,
  `id_core_profile_active` bigint UNSIGNED DEFAULT NULL,
  `id_company_active` bigint UNSIGNED DEFAULT NULL,
  `id_branch_active` bigint UNSIGNED DEFAULT NULL,
  `session_token` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token_hash` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `device_info` json DEFAULT NULL,
  `status` enum('active','inactive','blocked','deleted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `last_used_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `USER`
--

CREATE TABLE `USER` (
  `id` bigint UNSIGNED NOT NULL,
  `id_core_user` bigint UNSIGNED NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email_verified` tinyint(1) NOT NULL DEFAULT '0',
  `phone_verified` tinyint(1) NOT NULL DEFAULT '0',
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('active','inactive','blocked','deleted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `VERIFICATION_CODE`
--

CREATE TABLE `VERIFICATION_CODE` (
  `id` bigint UNSIGNED NOT NULL,
  `id_user` bigint UNSIGNED NOT NULL,
  `code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `purpose` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` datetime NOT NULL,
  `status` enum('active','inactive','blocked','deleted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `AUTH_POLICY`
--
ALTER TABLE `AUTH_POLICY`
  ADD PRIMARY KEY (`id`);

--
-- Indices de la tabla `DOMAIN`
--
ALTER TABLE `DOMAIN`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_domain_host` (`host`),
  ADD KEY `idx_domain_status` (`status`),
  ADD KEY `idx_domain_type` (`type`),
  ADD KEY `idx_domain_company` (`id_company`),
  ADD KEY `idx_domain_app` (`id_brand`) USING BTREE;

--
-- Indices de la tabla `LOGIN_ATTEMPT`
--
ALTER TABLE `LOGIN_ATTEMPT`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_identifier_ts` (`identifier`,`created_at`),
  ADD KEY `idx_ip_ts` (`ip`,`created_at`),
  ADD KEY `idx_company_ts` (`id_company`,`created_at`);

--
-- Indices de la tabla `PASSWORD_HISTORY`
--
ALTER TABLE `PASSWORD_HISTORY`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_ts` (`id_user`,`created_at`);

--
-- Indices de la tabla `PROFILE`
--
ALTER TABLE `PROFILE`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_profile_core` (`id_core_profile`),
  ADD UNIQUE KEY `uk_profile_username` (`username`),
  ADD KEY `idx_profile_status` (`status`),
  ADD KEY `idx_profile_company` (`id_company`),
  ADD KEY `idx_profile_branch_primary` (`id_branch_primary`),
  ADD KEY `idx_profile_user` (`id_user`),
  ADD KEY `idx_profile_core_user` (`id_core_user`);

--
-- Indices de la tabla `REFRESH_TOKEN`
--
ALTER TABLE `REFRESH_TOKEN`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_refresh_session_token` (`session_token`),
  ADD UNIQUE KEY `uk_refresh_token_hash` (`token_hash`),
  ADD KEY `idx_refresh_user` (`id_user`),
  ADD KEY `idx_refresh_expires` (`expires_at`),
  ADD KEY `idx_refresh_revoked` (`revoked_at`),
  ADD KEY `idx_refresh_status` (`status`),
  ADD KEY `idx_refresh_profile_active` (`id_core_profile_active`),
  ADD KEY `idx_refresh_company_active` (`id_company_active`),
  ADD KEY `idx_refresh_branch_active` (`id_branch_active`);

--
-- Indices de la tabla `USER`
--
ALTER TABLE `USER`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_user_email` (`email`),
  ADD UNIQUE KEY `uk_user_phone` (`phone`),
  ADD KEY `idx_user_core` (`id_core_user`),
  ADD KEY `idx_user_status` (`status`);

--
-- Indices de la tabla `VERIFICATION_CODE`
--
ALTER TABLE `VERIFICATION_CODE`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_vc_user` (`id_user`),
  ADD KEY `idx_vc_purpose` (`purpose`),
  ADD KEY `idx_vc_expires` (`expires_at`),
  ADD KEY `idx_vc_status` (`status`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `AUTH_POLICY`
--
ALTER TABLE `AUTH_POLICY`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `DOMAIN`
--
ALTER TABLE `DOMAIN`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `LOGIN_ATTEMPT`
--
ALTER TABLE `LOGIN_ATTEMPT`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `PASSWORD_HISTORY`
--
ALTER TABLE `PASSWORD_HISTORY`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `PROFILE`
--
ALTER TABLE `PROFILE`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `REFRESH_TOKEN`
--
ALTER TABLE `REFRESH_TOKEN`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `USER`
--
ALTER TABLE `USER`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `VERIFICATION_CODE`
--
ALTER TABLE `VERIFICATION_CODE`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `PASSWORD_HISTORY`
--
ALTER TABLE `PASSWORD_HISTORY`
  ADD CONSTRAINT `PASSWORD_HISTORY_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `USER` (`id`);

--
-- Filtros para la tabla `PROFILE`
--
ALTER TABLE `PROFILE`
  ADD CONSTRAINT `fk_auth_profile_user` FOREIGN KEY (`id_user`) REFERENCES `USER` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Filtros para la tabla `REFRESH_TOKEN`
--
ALTER TABLE `REFRESH_TOKEN`
  ADD CONSTRAINT `fk_refresh_user` FOREIGN KEY (`id_user`) REFERENCES `USER` (`id`);

--
-- Filtros para la tabla `VERIFICATION_CODE`
--
ALTER TABLE `VERIFICATION_CODE`
  ADD CONSTRAINT `fk_vc_user` FOREIGN KEY (`id_user`) REFERENCES `USER` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;