-- =====================================================================
-- ARCA Hosting — Esquema de Base de Datos (MySQL 8)
-- Fase 0/1: núcleo, auth, auditoría, multi-tenant base y clientes.
-- Las tablas de fases futuras (dominios, hosting, facturación, etc.)
-- se incluyen ya para que las relaciones (FK) queden estables desde el
-- inicio y no requieran migraciones destructivas más adelante.
-- =====================================================================


CREATE DATABASE IF NOT EXISTS arca_hosting
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE arca_hosting;

-- ---------------------------------------------------------------------
-- MULTI-TENANT: empresas/marcas que operan sobre la misma instalación
-- ---------------------------------------------------------------------
CREATE TABLE companies (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,
    legal_name      VARCHAR(200) NULL,
    tax_id          VARCHAR(50)  NULL COMMENT 'RFC',
    email           VARCHAR(150) NULL,
    phone           VARCHAR(30)  NULL,
    currency        CHAR(3) NOT NULL DEFAULT 'MXN',
    timezone        VARCHAR(60) NOT NULL DEFAULT 'America/Mexico_City',
    logo_path       VARCHAR(255) NULL,
    active          TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE roles (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(80) NOT NULL UNIQUE,
    description VARCHAR(255) NULL
) ENGINE=InnoDB;

CREATE TABLE role_permission (
    role_id       BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE company_user (
    company_id BIGINT UNSIGNED NOT NULL,
    user_id    BIGINT UNSIGNED NOT NULL,
    role_id    BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (company_id, user_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Control de intentos de acceso (fuerza bruta) independiente del usuario
-- (permite bloquear también por IP aunque el email no exista)
CREATE TABLE login_attempts (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(150) NOT NULL,
    ip_address  VARCHAR(45) NOT NULL,
    success     TINYINT(1) NOT NULL,
    user_agent  VARCHAR(255) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_attempts_email (email, created_at),
    INDEX idx_login_attempts_ip (ip_address, created_at)
) ENGINE=InnoDB;

-- Bitácora general de todo el sistema (append-only)
CREATE TABLE audit_log (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   BIGINT UNSIGNED NULL,
    user_id      BIGINT UNSIGNED NULL,
    action       VARCHAR(100) NOT NULL COMMENT 'ej. client.created, invoice.voided',
    entity_type  VARCHAR(80)  NULL,
    entity_id    BIGINT UNSIGNED NULL,
    ip_address   VARCHAR(45) NULL,
    details_json JSON NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_company (company_id, created_at),
    INDEX idx_audit_entity (entity_type, entity_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE client_documents (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id   BIGINT UNSIGNED NOT NULL,
    file_name   VARCHAR(255) NOT NULL,
    file_path   VARCHAR(255) NOT NULL,
    uploaded_by BIGINT UNSIGNED NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE server_metrics (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    server_id   BIGINT UNSIGNED NOT NULL,
    cpu_percent DECIMAL(5,2) NULL,
    ram_percent DECIMAL(5,2) NULL,
    disk_percent DECIMAL(5,2) NULL,
    is_reachable TINYINT(1) NOT NULL DEFAULT 1,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
    INDEX idx_metrics_server_time (server_id, recorded_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- DOMINIOS (Fase 3)
-- ---------------------------------------------------------------------
CREATE TABLE domains (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    client_id         BIGINT UNSIGNED NOT NULL,
    domain_name       VARCHAR(190) NOT NULL,
    tld               VARCHAR(30) NOT NULL,
    registrar         VARCHAR(80) NULL,
    registered_at     DATE NULL,
    expires_at        DATE NULL,
    auto_renew        TINYINT(1) NOT NULL DEFAULT 1,
    nameservers       JSON NULL,
    dns_provider      ENUM('cloudflare','other') NULL DEFAULT 'cloudflare',
    ssl_status        ENUM('none','pending','active','expired') NOT NULL DEFAULT 'none',
    cost              DECIMAL(10,2) NULL,
    price             DECIMAL(10,2) NULL,
    status            ENUM('active','expired','pending_transfer','cancelled') NOT NULL DEFAULT 'active',
    notes             TEXT NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_domain (domain_name),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- SERVICIOS / HOSTING (Fase 2 y 4)
-- ---------------------------------------------------------------------
CREATE TABLE service_plans (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(120) NOT NULL,
    type        ENUM('hosting','email','m365','gws','ssl','cloudflare','dev','maintenance',
                      'backup','consulting','mobile_app','domain') NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    billing_cycle ENUM('monthly','quarterly','biannual','annual','one_time') NOT NULL DEFAULT 'monthly',
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE client_services (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    client_id       BIGINT UNSIGNED NOT NULL,
    service_plan_id BIGINT UNSIGNED NOT NULL,
    server_id       BIGINT UNSIGNED NULL,
    cpanel_username VARCHAR(60) NULL,
    disk_used_mb    INT UNSIGNED NULL,
    bandwidth_used_mb INT UNSIGNED NULL,
    status          ENUM('pending','active','suspended','terminated') NOT NULL DEFAULT 'pending',
    next_due_date   DATE NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (service_plan_id) REFERENCES service_plans(id) ON DELETE RESTRICT,
    FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- FACTURACIÓN (Fase 5) + CFDI
-- ---------------------------------------------------------------------
CREATE TABLE invoices (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id    BIGINT UNSIGNED NOT NULL,
    client_id     BIGINT UNSIGNED NOT NULL,
    folio         VARCHAR(30) NOT NULL,
    subtotal      DECIMAL(12,2) NOT NULL,
    tax_amount    DECIMAL(12,2) NOT NULL DEFAULT 0,
    total         DECIMAL(12,2) NOT NULL,
    currency      CHAR(3) NOT NULL DEFAULT 'MXN',
    status        ENUM('draft','pending','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
    due_date      DATE NOT NULL,
    cfdi_uuid     VARCHAR(60) NULL COMMENT 'UUID de timbrado fiscal, si aplica',
    cfdi_xml_path VARCHAR(255) NULL,
    cfdi_pdf_path VARCHAR(255) NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_invoice_folio (company_id, folio),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE invoice_items (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id  BIGINT UNSIGNED NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity    DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_price  DECIMAL(12,2) NOT NULL,
    total       DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    invoice_id      BIGINT UNSIGNED NOT NULL,
    gateway         ENUM('stripe','paypal','mercadopago','openpay','manual') NOT NULL,
    gateway_reference VARCHAR(150) NULL,
    amount          DECIMAL(12,2) NOT NULL,
    status          ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
    paid_at         DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- TICKETS DE SOPORTE (Fase 8)
-- ---------------------------------------------------------------------
CREATE TABLE tickets (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    client_id   BIGINT UNSIGNED NOT NULL,
    subject     VARCHAR(200) NOT NULL,
    category    VARCHAR(80) NULL,
    priority    ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    status      ENUM('open','pending','answered','closed') NOT NULL DEFAULT 'open',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE ticket_messages (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id   BIGINT UNSIGNED NOT NULL,
    author_type ENUM('client','staff') NOT NULL,
    author_id   BIGINT UNSIGNED NOT NULL,
    message     TEXT NOT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- COLA DE TAREAS (jobs) — Fase 7
-- ---------------------------------------------------------------------
CREATE TABLE jobs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue       VARCHAR(60) NOT NULL DEFAULT 'default',
    job_type    VARCHAR(100) NOT NULL COMMENT 'ej. SendInvoiceEmail, ProvisionHosting',
    payload_json JSON NOT NULL,
    attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status      ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
    available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_jobs_status_time (status, available_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Datos semilla mínimos para poder iniciar sesión hoy mismo
-- ---------------------------------------------------------------------
INSERT INTO companies (name, legal_name, currency, timezone)
VALUES ('ARCA Hosting', 'ARCA Hosting S.A. de C.V.', 'MXN', 'America/Mexico_City');

INSERT INTO roles (name, description) VALUES
  ('superadmin', 'Acceso total al sistema'),
  ('admin', 'Administrador de la empresa'),
  ('support', 'Agente de soporte');

INSERT INTO company_user (company_id, user_id, role_id) VALUES (1, 1, 1);
