-- ============================================================================
-- LASER CUT SAAS - COMPLETE DATABASE SCHEMA
-- Multi-Tenant Industrial E-Commerce Platform
-- MySQL 5.7+ / MariaDB 10.2+
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- ============================================================================
-- CORE TENANT TABLES
-- ============================================================================

-- Tenants (Master table - no tenant_id)
CREATE TABLE IF NOT EXISTS `tenants` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL COMMENT 'Company name',
  `slug` VARCHAR(100) NOT NULL UNIQUE COMMENT 'URL-friendly identifier',
  `subdomain` VARCHAR(100) NULL UNIQUE COMMENT 'Subdomain identifier',
  `domain` VARCHAR(255) NULL UNIQUE COMMENT 'Custom domain',
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) NULL,
  `contact_name` VARCHAR(255) NOT NULL,
  `logo_url` VARCHAR(500) NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active, 0=inactive',
  `trial_ends_at` DATETIME NULL,
  `subscription_plan` VARCHAR(50) NULL DEFAULT 'basic',
  `subscription_status` ENUM('trial', 'active', 'suspended', 'cancelled') DEFAULT 'trial',
  `max_products` INT UNSIGNED DEFAULT 100,
  `max_orders_per_month` INT UNSIGNED DEFAULT 500,
  `storage_limit_mb` INT UNSIGNED DEFAULT 1024,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_slug` (`slug`),
  INDEX `idx_subdomain` (`subdomain`),
  INDEX `idx_domain` (`domain`),
  INDEX `idx_active` (`active`),
  INDEX `idx_subscription_status` (`subscription_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tenant Settings
CREATE TABLE IF NOT EXISTS `tenant_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NULL,
  `setting_type` ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_tenant_setting` (`tenant_id`, `setting_key`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_setting_key` (`setting_key`),
  CONSTRAINT `fk_tenant_settings_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- USER & PERMISSION TABLES
-- ============================================================================

-- Roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `is_system_role` TINYINT(1) DEFAULT 0 COMMENT '1=cannot be deleted',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_slug_per_tenant` (`tenant_id`, `slug`),
  INDEX `idx_tenant_id` (`tenant_id`),
  CONSTRAINT `fk_roles_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `module` VARCHAR(50) NOT NULL COMMENT 'products, orders, customers, etc',
  `description` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_slug` (`slug`),
  INDEX `idx_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Role Permissions (junction table)
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_permission` (`tenant_id`, `role_id`, `permission_id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_role_id` (`role_id`),
  INDEX `idx_permission_id` (`permission_id`),
  CONSTRAINT `fk_role_permissions_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `role_id` INT UNSIGNED NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) NULL,
  `avatar_url` VARCHAR(500) NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `email_verified_at` DATETIME NULL,
  `last_login_at` DATETIME NULL,
  `last_login_ip` VARCHAR(45) NULL,
  `remember_token` VARCHAR(100) NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_email_per_tenant` (`tenant_id`, `email`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_role_id` (`role_id`),
  INDEX `idx_email` (`email`),
  INDEX `idx_active` (`active`),
  CONSTRAINT `fk_users_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CUSTOMER TABLES
-- ============================================================================

-- Customers
CREATE TABLE IF NOT EXISTS `customers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NULL COMMENT 'Linked user account if registered',
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) NULL,
  `cpf_cnpj` VARCHAR(20) NULL COMMENT 'Tax ID',
  `company_name` VARCHAR(255) NULL,
  `customer_type` ENUM('individual', 'business') DEFAULT 'individual',
  `notes` TEXT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `total_orders` INT UNSIGNED DEFAULT 0,
  `total_spent` DECIMAL(12, 2) DEFAULT 0.00,
  `last_order_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_email_per_tenant` (`tenant_id`, `email`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_email` (`email`),
  INDEX `idx_cpf_cnpj` (`cpf_cnpj`),
  INDEX `idx_active` (`active`),
  CONSTRAINT `fk_customers_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customers_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customer Addresses
CREATE TABLE IF NOT EXISTS `customer_addresses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  `address_type` ENUM('billing', 'shipping', 'both') DEFAULT 'both',
  `label` VARCHAR(100) NULL COMMENT 'Casa, Trabalho, etc',
  `recipient_name` VARCHAR(255) NULL,
  `street` VARCHAR(255) NOT NULL,
  `number` VARCHAR(50) NOT NULL,
  `complement` VARCHAR(255) NULL,
  `neighborhood` VARCHAR(100) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `state` VARCHAR(2) NOT NULL,
  `zipcode` VARCHAR(10) NOT NULL,
  `country` VARCHAR(2) DEFAULT 'BR',
  `is_default` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_customer_id` (`customer_id`),
  INDEX `idx_zipcode` (`zipcode`),
  INDEX `idx_is_default` (`is_default`),
  CONSTRAINT `fk_customer_addresses_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customer_addresses_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PRODUCT CATALOG TABLES  
-- ============================================================================

-- Categories
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `parent_id` INT UNSIGNED NULL,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `image_url` VARCHAR(500) NULL,
  `icon` VARCHAR(100) NULL,
  `display_order` INT DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_parent_id` (`parent_id`),
  INDEX `idx_slug` (`slug`),
  INDEX `idx_active` (`active`),
  INDEX `idx_display_order` (`display_order`),
  CONSTRAINT `fk_categories_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Materials
CREATE TABLE IF NOT EXISTS `materials` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL COMMENT 'MDF 3mm, Acrílico 5mm, etc',
  `code` VARCHAR(50) NULL,
  `type` VARCHAR(50) NULL COMMENT 'MDF, Acrílico, Madeira, etc',
  `thickness_mm` DECIMAL(5, 2) NULL,
  `cost_per_unit` DECIMAL(10, 2) DEFAULT 0.00,
  `unit` VARCHAR(20) DEFAULT 'cm²' COMMENT 'cm², peça, etc',
  `stock_quantity` DECIMAL(10, 2) DEFAULT 0,
  `min_stock_alert` DECIMAL(10, 2) NULL,
  `supplier_name` VARCHAR(255) NULL,
  `notes` TEXT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_active` (`active`),
  CONSTRAINT `fk_materials_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products (continuing in next message due to length...)