-- ============================================================
-- KopierSFlow ERP — 001_companies_and_security.sql
-- Importar PRIMERO en phpMyAdmin
-- ============================================================

CREATE TABLE companies (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(200) NOT NULL,
    legal_name        VARCHAR(200),
    ein               VARCHAR(20)         COMMENT 'Employer Identification Number',
    address           TEXT,
    city              VARCHAR(100),
    state             CHAR(2),
    zip               VARCHAR(10),
    phone             VARCHAR(30),
    email             VARCHAR(150),
    website           VARCHAR(200),
    fiscal_year_start TINYINT DEFAULT 1   COMMENT '1=Enero … 12=Diciembre',
    base_currency     CHAR(3)  DEFAULT 'USD',
    timezone          VARCHAR(50)  DEFAULT 'America/New_York',
    language          CHAR(5)      DEFAULT 'es',
    logo_path         VARCHAR(500),
    is_active         TINYINT(1)   DEFAULT 1,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
    id                      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                    VARCHAR(150) NOT NULL,
    email                   VARCHAR(150) NOT NULL UNIQUE,
    password_hash           VARCHAR(255) NOT NULL  COMMENT 'argon2id',
    phone                   VARCHAR(30),
    totp_secret             VARCHAR(255)           COMMENT 'Secreto TOTP encriptado para 2FA',
    totp_enabled            TINYINT(1)  DEFAULT 0,
    is_superadmin           TINYINT(1)  DEFAULT 0,
    is_active               TINYINT(1)  DEFAULT 1,
    last_login_at           TIMESTAMP   NULL,
    password_changed_at     TIMESTAMP   NULL,
    failed_login_attempts   TINYINT     DEFAULT 0,
    locked_until            TIMESTAMP   NULL,
    created_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    is_system   TINYINT(1) DEFAULT 0,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_company_role (company_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module      VARCHAR(60) NOT NULL,
    action      VARCHAR(60) NOT NULL,
    description VARCHAR(255),
    UNIQUE KEY uq_module_action (module, action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
    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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_companies (
    user_id    BIGINT UNSIGNED NOT NULL,
    company_id BIGINT UNSIGNED NOT NULL,
    role_id    BIGINT UNSIGNED NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, company_id),
    FOREIGN KEY (user_id)    REFERENCES users(id)     ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id)    REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED,
    user_id     BIGINT UNSIGNED,
    action      VARCHAR(100) NOT NULL,
    module      VARCHAR(60)  NOT NULL,
    record_type VARCHAR(100),
    record_id   BIGINT UNSIGNED,
    old_values  JSON,
    new_values  JSON,
    ip_address  VARCHAR(45),
    user_agent  VARCHAR(300),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_company_module (company_id, module),
    INDEX idx_created        (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_logs (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id          BIGINT UNSIGNED,
    email_attempted  VARCHAR(150),
    success          TINYINT(1) NOT NULL,
    ip_address       VARCHAR(45),
    user_agent       VARCHAR(300),
    failure_reason   VARCHAR(100),
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip      (ip_address),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    `key`       VARCHAR(100) NOT NULL,
    `value`     TEXT,
    `group`     VARCHAR(60) DEFAULT 'general',
    updated_by  BIGINT UNSIGNED,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_company_key (company_id, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;