-- ============================================================
-- DIMARK Brain · Super Cerebro — Instalación de base de datos
-- Ejecuta este script en phpMyAdmin o MySQL CLI
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- USUARIOS
CREATE TABLE IF NOT EXISTS `dm_users` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(120) NOT NULL,
  `email`         VARCHAR(180) NOT NULL UNIQUE,
  `password`      VARCHAR(255) NOT NULL,
  `role`          ENUM('admin','client') NOT NULL DEFAULT 'client',
  `active`        TINYINT(1) NOT NULL DEFAULT 1,
  `avatar_color`  VARCHAR(10) DEFAULT '#E8272A',
  `last_login`    DATETIME DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CLIENTES (empresas)
CREATE TABLE IF NOT EXISTS `dm_clients` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(160) NOT NULL,
  `industry`      VARCHAR(100) DEFAULT '',
  `url`           VARCHAR(255) DEFAULT '',
  `color`         VARCHAR(10) DEFAULT '#4a9ef8',
  `services`      VARCHAR(255) DEFAULT '',   -- CSV: meta,google,seo,wp,shopify
  `meta_account`  VARCHAR(80) DEFAULT '',
  `google_cid`    VARCHAR(80) DEFAULT '',
  `gsc_domain`    VARCHAR(255) DEFAULT '',
  `active`        TINYINT(1) NOT NULL DEFAULT 1,
  `notes`         TEXT DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- RELACIÓN USUARIO ↔ CLIENTE
CREATE TABLE IF NOT EXISTS `dm_user_clients` (
  `user_id`       INT UNSIGNED NOT NULL,
  `client_id`     INT UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`, `client_id`),
  FOREIGN KEY (`user_id`)   REFERENCES `dm_users`(`id`)   ON DELETE CASCADE,
  FOREIGN KEY (`client_id`) REFERENCES `dm_clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- LOG DE ACTIVIDAD
CREATE TABLE IF NOT EXISTS `dm_activity` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`       INT UNSIGNED DEFAULT NULL,
  `client_id`     INT UNSIGNED DEFAULT NULL,
  `action`        VARCHAR(120) NOT NULL,
  `detail`        TEXT DEFAULT NULL,
  `ip`            VARCHAR(45) DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`)   REFERENCES `dm_users`(`id`)   ON DELETE SET NULL,
  FOREIGN KEY (`client_id`) REFERENCES `dm_clients`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- REPORTES GENERADOS
CREATE TABLE IF NOT EXISTS `dm_reports` (
  `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `client_id`     INT UNSIGNED NOT NULL,
  `title`         VARCHAR(200) NOT NULL,
  `period`        VARCHAR(50) DEFAULT '',
  `content`       LONGTEXT DEFAULT NULL,
  `file_path`     VARCHAR(255) DEFAULT NULL,
  `created_by`    INT UNSIGNED DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`)  REFERENCES `dm_clients`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `dm_users`(`id`)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET foreign_key_checks = 1;

-- ============================================================
-- DATOS INICIALES — Admin DIMARK
-- Contraseña: dimark2025  (cámbiala después del primer login)
-- ============================================================
INSERT IGNORE INTO `dm_users` (`name`, `email`, `password`, `role`, `avatar_color`)
VALUES (
  'Admin DIMARK',
  'admin@dimark.co',
  '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uHxslg947',
  'admin',
  '#E8272A'
);

-- Clientes de demo
INSERT IGNORE INTO `dm_clients` (`name`, `industry`, `url`, `color`, `services`) VALUES
('Clínica Dental MDE', 'Salud', 'sanadentodontologos.com.co', '#4a9ef8', 'meta,google,seo,wp'),
('Segurihotel',        'Software', 'segurihotel.com',           '#22c97a', 'google,seo,wp'),
('Inmobiliaria MDE',   'Inmobiliaria', 'informeinmobiliario.com','#f5c543', 'meta,seo'),
('OkVet',              'Veterinaria', 'okvet.co',                '#9b7ff4', 'google,wp'),
('Tienda Premium',     'Ecommerce', 'tiendapremium.co',          '#f97316', 'meta,google,shopify');
