-- ERP + PDV SaaS — Core Schema (v0)
-- Regras do contrato:
-- - Apenas tabelas core SaaS: erp_tenant, erp_store, erp_pdv, erp_user, erp_role, erp_user_role
-- - InnoDB + utf8mb4
-- - PKs, FKs e índices principais
-- - Sem seeds, sem tabelas operacionais (products/stock/sales/cash)

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- Opcional: para ambientes que exigem ordem de drop/create
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS erp_user_role;
DROP TABLE IF EXISTS erp_role;
DROP TABLE IF EXISTS erp_user;
DROP TABLE IF EXISTS erp_pdv;
DROP TABLE IF EXISTS erp_store;
DROP TABLE IF EXISTS erp_tenant;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE erp_tenant (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tenant_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE erp_store (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_store_tenant_id (tenant_id),
  CONSTRAINT fk_store_tenant
    FOREIGN KEY (tenant_id) REFERENCES erp_tenant(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE erp_pdv (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pdv_tenant_store (tenant_id, store_id),
  KEY idx_pdv_store_id (store_id),
  CONSTRAINT fk_pdv_tenant
    FOREIGN KEY (tenant_id) REFERENCES erp_tenant(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_pdv_store
    FOREIGN KEY (store_id) REFERENCES erp_store(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE erp_user (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_tenant_email (tenant_id, email),
  KEY idx_user_tenant_email (tenant_id, email),
  CONSTRAINT fk_user_tenant
    FOREIGN KEY (tenant_id) REFERENCES erp_tenant(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE erp_role (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(150) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_role_tenant_code (tenant_id, code),
  KEY idx_role_tenant_code (tenant_id, code),
  CONSTRAINT fk_role_tenant
    FOREIGN KEY (tenant_id) REFERENCES erp_tenant(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE erp_user_role (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user_role_tenant_user (tenant_id, user_id),
  KEY idx_user_role_role_id (role_id),
  CONSTRAINT fk_user_role_tenant
    FOREIGN KEY (tenant_id) REFERENCES erp_tenant(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_user_role_user
    FOREIGN KEY (user_id) REFERENCES erp_user(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_user_role_role
    FOREIGN KEY (role_id) REFERENCES erp_role(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;