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

USE innovahere_reviews;

CREATE TABLE companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    website VARCHAR(255) NULL,
    owner_email VARCHAR(190) NOT NULL,
    api_key CHAR(64) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    public_id CHAR(24) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(190) NOT NULL,
    title VARCHAR(120) NOT NULL,
    review_text TEXT NOT NULL,
    rating TINYINT UNSIGNED NOT NULL,
    status ENUM('pending','approved','rejected','deleted') NOT NULL DEFAULT 'pending',
    source_url VARCHAR(255) NULL,
    ip_hash CHAR(64) NULL,
    moderation_token_hash CHAR(64) NULL,
    moderation_expires_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_at DATETIME NULL,
    rejected_at DATETIME NULL,
    deleted_at DATETIME NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_reviews_company
      FOREIGN KEY (company_id) REFERENCES companies(id)
      ON DELETE CASCADE,
    INDEX idx_reviews_company_status (company_id, status),
    INDEX idx_reviews_created_at (created_at)
) ENGINE=InnoDB;

CREATE TABLE admin_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id BIGINT UNSIGNED NULL,
    review_id BIGINT UNSIGNED NULL,
    action VARCHAR(50) NOT NULL,
    details JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_review (review_id),
    CONSTRAINT fk_audit_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_review FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Después de crear el usuario, reemplace el hash usando tools/create-password.php.
-- Ejemplo de compañía:
-- INSERT INTO companies (name, slug, website, owner_email, api_key)
-- VALUES ('Jhenn Cleaning Services','jhenn-cleaning','https://jhenncleaning.com',
-- 'Jhennifer26pz@gmail.com', SHA2(CONCAT(UUID(), RAND()), 256));