-- =====================================================================
-- Esquema de base de datos para "MiWeb" (acortador de enlaces + pages)
-- Compatible con MySQL 5.7+ / MariaDB en hosting compartido
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Tabla: users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50)  NOT NULL,
    email           VARCHAR(150) NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    role            ENUM('user','admin') NOT NULL DEFAULT 'user',
    status          ENUM('active','blocked') NOT NULL DEFAULT 'active',
    reset_token     VARCHAR(64)  NULL,
    reset_expires   DATETIME     NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabla: links
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS links (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    short_code      VARCHAR(64) NOT NULL,
    original_url    TEXT NOT NULL,
    is_custom       TINYINT(1) NOT NULL DEFAULT 0,
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    total_visits    INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_visit_at   DATETIME NULL,
    UNIQUE KEY uq_links_short_code (short_code),
    KEY idx_links_user (user_id),
    CONSTRAINT fk_links_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabla: visits (una fila por visita a un enlace corto)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS visits (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    link_id         INT UNSIGNED NOT NULL,
    visited_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    visit_date      DATE NOT NULL,
    ip_address      VARCHAR(45) NULL,
    country         VARCHAR(80) NULL,
    device          VARCHAR(50) NULL,
    browser         VARCHAR(50) NULL,
    os              VARCHAR(50) NULL,
    referrer        VARCHAR(255) NULL,
    KEY idx_visits_link (link_id),
    KEY idx_visits_date (visit_date),
    CONSTRAINT fk_visits_link FOREIGN KEY (link_id) REFERENCES links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabla: pages ("Bitly Pages" - biolinks)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pages (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    slug            VARCHAR(64) NOT NULL,
    display_name    VARCHAR(100) NOT NULL,
    description     VARCHAR(255) NULL,
    avatar_url      VARCHAR(255) NULL,
    theme_color     VARCHAR(20) NOT NULL DEFAULT '#4f46e5',
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    total_visits    INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_pages_slug (slug),
    KEY idx_pages_user (user_id),
    CONSTRAINT fk_pages_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabla: page_buttons (botones/enlaces dentro de una Page)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS page_buttons (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_id         INT UNSIGNED NOT NULL,
    label           VARCHAR(100) NOT NULL,
    url             TEXT NOT NULL,
    icon            VARCHAR(50) NULL,
    position        INT UNSIGNED NOT NULL DEFAULT 0,
    total_clicks    INT UNSIGNED NOT NULL DEFAULT 0,
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_buttons_page (page_id),
    CONSTRAINT fk_buttons_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabla: page_visits (visitas a la página pública /m/{slug})
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS page_visits (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_id         INT UNSIGNED NOT NULL,
    visited_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    visit_date      DATE NOT NULL,
    ip_address      VARCHAR(45) NULL,
    referrer        VARCHAR(255) NULL,
    KEY idx_pagevisits_page (page_id),
    CONSTRAINT fk_pagevisits_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabla: page_button_clicks (log de clics por botón, para estadísticas)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS page_button_clicks (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    button_id       INT UNSIGNED NOT NULL,
    clicked_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address      VARCHAR(45) NULL,
    KEY idx_clicks_button (button_id),
    CONSTRAINT fk_clicks_button FOREIGN KEY (button_id) REFERENCES page_buttons(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- Usuario administrador de ejemplo (cambia el password luego de instalar)
-- Password por defecto: Admin1234!  (hash generado con password_hash PHP)
-- ---------------------------------------------------------------------
-- INSERT INTO users (username, email, password_hash, role, status)
-- VALUES ('admin', 'admin@miweb.com', '$2y$10$REEMPLAZAR_CON_HASH_REAL', 'admin', 'active');