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


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 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;

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 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);
