CREATE DATABASE IF NOT EXISTS bitacora
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE bitacora;

CREATE TABLE IF NOT EXISTS usuarios (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nombre VARCHAR(150) DEFAULT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_usuario_activo (usuario, activo)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS frases (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    texto TEXT NOT NULL,
    autor VARCHAR(150) DEFAULT NULL,
    categoria VARCHAR(100) DEFAULT NULL,
    contexto TEXT DEFAULT NULL,
    origen VARCHAR(255) DEFAULT NULL,
    tags VARCHAR(500) DEFAULT NULL,

    favorita TINYINT(1) NOT NULL DEFAULT 0,
    visible TINYINT(1) NOT NULL DEFAULT 1,

    fecha_frase DATE DEFAULT NULL,

    created_by BIGINT UNSIGNED DEFAULT NULL,
    updated_by BIGINT UNSIGNED DEFAULT NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FULLTEXT KEY ft_frases (texto, autor, contexto, origen, tags),
    INDEX idx_autor (autor),
    INDEX idx_categoria (categoria),
    INDEX idx_favorita (favorita),
    INDEX idx_visible (visible),
    INDEX idx_fecha_frase (fecha_frase),
    INDEX idx_created_at (created_at),

    CONSTRAINT fk_frases_created_by
        FOREIGN KEY (created_by) REFERENCES usuarios(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_frases_updated_by
        FOREIGN KEY (updated_by) REFERENCES usuarios(id)
        ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;
