-- ============================================================
-- Marketplace MVC - Schema Completo (MySQL 5.7+ / 8.0)
-- ============================================================
CREATE DATABASE IF NOT EXISTS marketplace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE marketplace;

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS audit_logs, payments, subscriptions, plans, reports, chats, favorites, product_images, products, categories, settings, password_resets, users, admins;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  phone VARCHAR(30),
  whatsapp VARCHAR(30),
  avatar VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(2),
  plan_id INT DEFAULT 1,
  status ENUM('active','blocked','pending') DEFAULT 'active',
  email_verified_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(status), INDEX(city), INDEX(state)
) ENGINE=InnoDB;

CREATE TABLE password_resets (
  email VARCHAR(180) NOT NULL,
  token VARCHAR(120) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(email)
) ENGINE=InnoDB;

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  icon VARCHAR(60),
  parent_id INT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  slug VARCHAR(80) NOT NULL UNIQUE,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  max_ads INT NOT NULL DEFAULT 3,       -- -1 = ilimitado
  max_images INT NOT NULL DEFAULT 3,
  featured_ads INT NOT NULL DEFAULT 0,
  duration_days INT NOT NULL DEFAULT 30,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  starts_at DATE NOT NULL,
  ends_at DATE NOT NULL,
  status ENUM('pending','active','expired','cancelled') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB;

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  subscription_id INT NULL,
  product_id INT NULL,
  method VARCHAR(32) NOT NULL DEFAULT 'pix',
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
  external_id VARCHAR(120),
  pix_code TEXT,
  paid_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  category_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  city VARCHAR(100),
  state VARCHAR(2),
  condition_type ENUM('novo','usado','seminovo') DEFAULT 'usado',
  status ENUM('pending','active','sold','rejected','inactive') DEFAULT 'pending',
  is_featured TINYINT(1) DEFAULT 0,
  featured_until DATE NULL,
  views INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id),
  INDEX(status), INDEX(city), INDEX(state), INDEX(category_id), INDEX(is_featured)
) ENGINE=InnoDB;

CREATE TABLE product_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  file VARCHAR(255) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE favorites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (user_id, product_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE chats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  message TEXT NOT NULL,
  read_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX(product_id, sender_id, receiver_id)
) ENGINE=InnoDB;

CREATE TABLE reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  user_id INT NOT NULL,
  reason VARCHAR(200) NOT NULL,
  details TEXT,
  status ENUM('open','resolved','dismissed') DEFAULT 'open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE settings (
  `key` VARCHAR(80) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  actor_type ENUM('user','admin','system') DEFAULT 'user',
  actor_id INT,
  action VARCHAR(80),
  entity VARCHAR(80),
  entity_id INT,
  ip VARCHAR(45),
  meta TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(actor_id), INDEX(action), INDEX(entity)
) ENGINE=InnoDB;

-- ============================================================
-- SEEDS
-- ============================================================
-- Senha para todos os demos: senha123
INSERT INTO admins (name,email,password_hash) VALUES
('Administrador','admin@marketplace.local','$2y$12$VwMeMHO0Fb9YF0Fy1B9Iw.KtWT1yDpkZ5Uv3c2DodgQ8rFtPrUQsa');

INSERT INTO plans (name,slug,price,max_ads,max_images,featured_ads,duration_days) VALUES
('Grátis','free',0.00,3,3,0,30),
('Premium','premium',29.90,-1,10,3,30),
('Profissional','pro',79.90,-1,15,10,30);

INSERT INTO users (name,email,password_hash,phone,whatsapp,city,state,plan_id) VALUES
('Cliente Demo','cliente@marketplace.local','$2y$12$VwMeMHO0Fb9YF0Fy1B9Iw.KtWT1yDpkZ5Uv3c2DodgQ8rFtPrUQsa','11999990001','11999990001','São Paulo','SP',1),
('Vendedor Premium','premium@marketplace.local','$2y$12$VwMeMHO0Fb9YF0Fy1B9Iw.KtWT1yDpkZ5Uv3c2DodgQ8rFtPrUQsa','11999990002','11999990002','Rio de Janeiro','RJ',2);

INSERT INTO categories (name,slug,icon,sort_order) VALUES
('Eletrônicos','eletronicos','bi-phone',1),
('Veículos','veiculos','bi-car-front',2),
('Imóveis','imoveis','bi-house',3),
('Moda','moda','bi-bag',4),
('Casa e Jardim','casa-jardim','bi-flower1',5),
('Esportes','esportes','bi-bicycle',6),
('Serviços','servicos','bi-tools',7),
('Outros','outros','bi-three-dots',8);

INSERT INTO products (user_id,category_id,title,slug,description,price,city,state,condition_type,status,is_featured) VALUES
(2,1,'iPhone 14 Pro 256GB','iphone-14-pro-256gb','Aparelho em perfeito estado, com nota fiscal e caixa.',4899.00,'Rio de Janeiro','RJ','seminovo','active',1),
(2,2,'Honda Civic EXL 2020','honda-civic-exl-2020','Único dono, revisões em dia, 45mil km.',119900.00,'Rio de Janeiro','RJ','usado','active',1),
(1,4,'Tênis Nike Air Max','tenis-nike-air-max','Tamanho 42, pouco usado.',389.00,'São Paulo','SP','usado','active',0),
(1,1,'Notebook Dell Inspiron 15','notebook-dell-inspiron-15','i7, 16GB RAM, SSD 512GB',3499.00,'São Paulo','SP','usado','active',0),
(2,5,'Sofá 3 lugares retrátil','sofa-3-lugares-retratil','Tecido suede, cor cinza.',1299.00,'Rio de Janeiro','RJ','usado','active',0);

INSERT INTO settings (`key`,`value`) VALUES
('site_name','Marketplace Brasil'),
('site_description','O maior portal de classificados do Brasil'),
('commission_percent','5'),
('whatsapp_support','5511999999999'),
('pix_key','contato@marketplace.local');