CREATE DATABASE IF NOT EXISTS super_sexta CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE super_sexta;

CREATE TABLE customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 phone VARCHAR(30) NOT NULL UNIQUE,
 email VARCHAR(190) NOT NULL DEFAULT '',
 password_hash VARCHAR(255) NOT NULL,
 status ENUM('ATIVO','BLOQUEADO') NOT NULL DEFAULT 'ATIVO',
 fcm_token TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 INDEX(email)
) ENGINE=InnoDB;

CREATE TABLE auth_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 token CHAR(64) NOT NULL UNIQUE,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE addresses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 label VARCHAR(80) NOT NULL DEFAULT 'Principal',
 cep VARCHAR(12) NULL,
 address_line VARCHAR(255) NOT NULL,
 number VARCHAR(30) NULL,
 complement VARCHAR(100) NULL,
 district VARCHAR(100) NULL,
 city VARCHAR(100) NULL,
 state CHAR(2) NULL,
 reference_point VARCHAR(255) NULL,
 is_default TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 icon VARCHAR(50) NULL,
 active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 category_id BIGINT UNSIGNED NULL,
 name VARCHAR(160) NOT NULL,
 description TEXT NULL,
 price DECIMAL(10,2) NOT NULL,
 stock INT NOT NULL DEFAULT 0,
 image_url VARCHAR(500) NULL,
 featured TINYINT(1) NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 address_id BIGINT UNSIGNED NOT NULL,
 status ENUM('RECEBIDO','PAGAMENTO_CONFIRMADO','PREPARANDO','SAIU_ENTREGA','ENTREGUE','CANCELADO') NOT NULL DEFAULT 'RECEBIDO',
 payment_method ENUM('PIX','CREDIT_CARD','DEBIT_CARD','CASH') NOT NULL DEFAULT 'PIX',
 payment_status ENUM('PENDENTE','PAGO','FALHOU','ESTORNADO') NOT NULL DEFAULT 'PENDENTE',
 gateway_payment_id VARCHAR(190) NULL,
 subtotal DECIMAL(10,2) NOT NULL,
 delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
 discount DECIMAL(10,2) NOT NULL DEFAULT 0,
 total DECIMAL(10,2) NOT NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY(customer_id) REFERENCES customers(id),
 FOREIGN KEY(address_id) REFERENCES addresses(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 product_name VARCHAR(160) NOT NULL,
 qty INT NOT NULL,
 unit_price DECIMAL(10,2) NOT NULL,
 line_total DECIMAL(10,2) NOT NULL,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('MASTER','ATENDENTE','ENTREGADOR') NOT NULL DEFAULT 'ATENDENTE',
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO categories(name,icon) VALUES
('Cestas','basket'),('Arroz e Feijão','rice'),('Higiene','hygiene'),('Limpeza','cleaning');

INSERT INTO products(category_id,name,description,price,stock,featured) VALUES
(1,'Cesta Básica Econômica','Itens essenciais para o dia a dia.',89.90,100,1),
(1,'Cesta Básica Família','Cesta completa para a família.',129.90,100,1),
(1,'Cesta Básica Premium','Seleção maior de produtos.',189.90,80,1);