-- Cestão Valença
-- Importar com o banco appcentercast_cestaovalenca já selecionado no phpMyAdmin.
-- Este arquivo NÃO cria banco de dados e NÃO executa USE.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS 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 idx_customer_email(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS 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,
 CONSTRAINT fk_auth_customer
   FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS 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 DEFAULT 'Valença',
 state CHAR(2) NULL DEFAULT 'BA',
 reference_point VARCHAR(255) NULL,
 is_default TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 CONSTRAINT fk_address_customer
   FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS 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,
 CONSTRAINT fk_product_category
   FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS 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','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,
 CONSTRAINT fk_order_customer
   FOREIGN KEY(customer_id) REFERENCES customers(id),
 CONSTRAINT fk_order_address
   FOREIGN KEY(address_id) REFERENCES addresses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS 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,
 CONSTRAINT fk_item_order
   FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
 CONSTRAINT fk_item_product
   FOREIGN KEY(product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO categories(name,icon,active)
SELECT 'Cestas','basket',1
WHERE NOT EXISTS (SELECT 1 FROM categories WHERE name='Cestas');

INSERT INTO categories(name,icon,active)
SELECT 'Alimentos','food',1
WHERE NOT EXISTS (SELECT 1 FROM categories WHERE name='Alimentos');

INSERT INTO categories(name,icon,active)
SELECT 'Higiene','hygiene',1
WHERE NOT EXISTS (SELECT 1 FROM categories WHERE name='Higiene');

INSERT INTO categories(name,icon,active)
SELECT 'Limpeza','cleaning',1
WHERE NOT EXISTS (SELECT 1 FROM categories WHERE name='Limpeza');

INSERT INTO products(category_id,name,description,price,stock,featured,active)
SELECT c.id,'Cesta Econômica','Itens essenciais para o dia a dia.',89.90,100,1,1
FROM categories c
WHERE c.name='Cestas'
  AND NOT EXISTS (SELECT 1 FROM products WHERE name='Cesta Econômica')
LIMIT 1;

INSERT INTO products(category_id,name,description,price,stock,featured,active)
SELECT c.id,'Cesta Família','Cesta completa para a família.',129.90,100,1,1
FROM categories c
WHERE c.name='Cestas'
  AND NOT EXISTS (SELECT 1 FROM products WHERE name='Cesta Família')
LIMIT 1;

INSERT INTO products(category_id,name,description,price,stock,featured,active)
SELECT c.id,'Cesta Premium','Seleção maior de produtos para sua casa.',189.90,80,1,1
FROM categories c
WHERE c.name='Cestas'
  AND NOT EXISTS (SELECT 1 FROM products WHERE name='Cesta Premium')
LIMIT 1;

SET FOREIGN_KEY_CHECKS=1;


-- FASE 06 - catálogo, estoque, fotos e banners
ALTER TABLE products
    ADD COLUMN IF NOT EXISTS sku VARCHAR(80) NULL AFTER category_id,
    ADD COLUMN IF NOT EXISTS unit VARCHAR(40) NULL DEFAULT 'un' AFTER price,
    ADD COLUMN IF NOT EXISTS min_stock INT NOT NULL DEFAULT 0 AFTER stock,
    ADD COLUMN IF NOT EXISTS updated_at DATETIME NULL AFTER created_at;

CREATE TABLE IF NOT EXISTS stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    movement_type ENUM('ENTRADA','SAIDA','AJUSTE') NOT NULL,
    quantity INT NOT NULL,
    reason VARCHAR(255) NULL,
    admin_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_stock_product(product_id),
    INDEX idx_stock_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS banners (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(160) NOT NULL,
    subtitle VARCHAR(255) NULL,
    image_url VARCHAR(500) NULL,
    action_type ENUM('NONE','PRODUCT','CATEGORY','URL') NOT NULL DEFAULT 'NONE',
    action_value VARCHAR(500) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO banners(title,subtitle,action_type,sort_order,active)
SELECT 'Economia que chega na sua porta','Cestas básicas com entrega rápida em Valença','CATEGORY',1,1
WHERE NOT EXISTS (SELECT 1 FROM banners WHERE title='Economia que chega na sua porta');


-- FASE 07 - entregadores e rastreamento de entrega
CREATE TABLE IF NOT EXISTS drivers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(190) NULL,
    password_hash VARCHAR(255) NOT NULL,
    vehicle VARCHAR(120) NULL,
    plate VARCHAR(20) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    last_lat DECIMAL(10,7) NULL,
    last_lng DECIMAL(10,7) NULL,
    last_location_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS delivery_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL UNIQUE,
    driver_id BIGINT UNSIGNED NOT NULL,
    status ENUM('ATRIBUIDA','RETIRADO','EM_ROTA','ENTREGUE','FALHOU') NOT NULL DEFAULT 'ATRIBUIDA',
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    picked_up_at DATETIME NULL,
    out_for_delivery_at DATETIME NULL,
    delivered_at DATETIME NULL,
    delivery_note VARCHAR(255) NULL,
    FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY(driver_id) REFERENCES drivers(id) ON DELETE RESTRICT,
    INDEX idx_delivery_driver(driver_id),
    INDEX idx_delivery_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- FASE 08 - central de notificações
CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NULL,
    driver_id BIGINT UNSIGNED NULL,
    order_id BIGINT UNSIGNED NULL,
    channel ENUM('APP','PUSH') NOT NULL DEFAULT 'APP',
    title VARCHAR(160) NOT NULL,
    message VARCHAR(500) NOT NULL,
    event_key VARCHAR(80) NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY(driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_notify_customer(customer_id,read_at),
    INDEX idx_notify_driver(driver_id,read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS driver_tokens(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 driver_id BIGINT UNSIGNED NOT NULL,
 token CHAR(64) NOT NULL UNIQUE,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(driver_id) REFERENCES drivers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- FASE 10 - cupons, taxas e agendamento
CREATE TABLE IF NOT EXISTS coupons (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(50) NOT NULL UNIQUE,
 description VARCHAR(255) NULL,
 discount_type ENUM('PERCENT','FIXED') NOT NULL DEFAULT 'FIXED',
 discount_value DECIMAL(10,2) NOT NULL,
 min_order DECIMAL(10,2) NOT NULL DEFAULT 0,
 max_discount DECIMAL(10,2) NULL,
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 usage_limit INT NULL,
 used_count INT NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS delivery_zones (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 match_type ENUM('DISTRICT','CEP_PREFIX') NOT NULL DEFAULT 'DISTRICT',
 match_value VARCHAR(120) NOT NULL,
 fee DECIMAL(10,2) NOT NULL DEFAULT 0,
 min_order DECIMAL(10,2) NOT NULL DEFAULT 0,
 estimated_minutes INT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE orders
 ADD COLUMN IF NOT EXISTS coupon_code VARCHAR(50) NULL AFTER discount,
 ADD COLUMN IF NOT EXISTS scheduled_for DATETIME NULL AFTER notes;

INSERT INTO delivery_zones(name,match_type,match_value,fee,min_order,estimated_minutes,active)
SELECT 'Valença Centro','DISTRICT','Centro',8.00,0,45,1
WHERE NOT EXISTS (SELECT 1 FROM delivery_zones WHERE name='Valença Centro');


-- FASE 11 - recuperação de senha
CREATE TABLE IF NOT EXISTS password_reset_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 token CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,
 used_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 INDEX idx_reset_customer(customer_id),
 INDEX idx_reset_expires(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- FASE 12 - favoritos, avaliações e recompra
CREATE TABLE IF NOT EXISTS favorites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_favorite(customer_id,product_id),
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 customer_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 order_id BIGINT UNSIGNED NULL,
 rating TINYINT UNSIGNED NOT NULL,
 comment VARCHAR(700) NULL,
 status ENUM('PENDENTE','APROVADA','REJEITADA') NOT NULL DEFAULT 'APROVADA',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_review(customer_id,product_id,order_id),
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE SET NULL,
 INDEX idx_reviews_product(product_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- FASE 13 - uploads
CREATE TABLE IF NOT EXISTS media_uploads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admin_id BIGINT UNSIGNED NULL,
 file_name VARCHAR(255) NOT NULL,
 relative_path VARCHAR(500) NOT NULL,
 mime_type VARCHAR(120) NOT NULL,
 file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- FASE 14 - configurações da loja
CREATE TABLE IF NOT EXISTS store_settings (
 id TINYINT UNSIGNED PRIMARY KEY,
 store_name VARCHAR(160) NOT NULL DEFAULT 'Cestão Valença',
 phone VARCHAR(30) NULL,
 whatsapp VARCHAR(30) NULL,
 address VARCHAR(255) NULL,
 minimum_order DECIMAL(10,2) NOT NULL DEFAULT 0,
 default_delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 10.00,
 maintenance_mode TINYINT(1) NOT NULL DEFAULT 0,
 maintenance_message VARCHAR(255) NULL,
 allow_scheduling TINYINT(1) NOT NULL DEFAULT 1,
 updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS store_hours (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 weekday TINYINT UNSIGNED NOT NULL,
 is_open TINYINT(1) NOT NULL DEFAULT 1,
 open_time TIME NULL,
 close_time TIME NULL,
 UNIQUE KEY uq_weekday(weekday)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO store_settings(id,store_name,minimum_order,default_delivery_fee,maintenance_mode,allow_scheduling)
SELECT 1,'Cestão Valença',0,10.00,0,1
WHERE NOT EXISTS (SELECT 1 FROM store_settings WHERE id=1);

INSERT INTO store_hours(weekday,is_open,open_time,close_time)
SELECT 0,0,NULL,NULL WHERE NOT EXISTS(SELECT 1 FROM store_hours WHERE weekday=0);
INSERT INTO store_hours(weekday,is_open,open_time,close_time)
SELECT 1,1,'08:00:00','18:00:00' WHERE NOT EXISTS(SELECT 1 FROM store_hours WHERE weekday=1);
INSERT INTO store_hours(weekday,is_open,open_time,close_time)
SELECT 2,1,'08:00:00','18:00:00' WHERE NOT EXISTS(SELECT 1 FROM store_hours WHERE weekday=2);
INSERT INTO store_hours(weekday,is_open,open_time,close_time)
SELECT 3,1,'08:00:00','18:00:00' WHERE NOT EXISTS(SELECT 1 FROM store_hours WHERE weekday=3);
INSERT INTO store_hours(weekday,is_open,open_time,close_time)
SELECT 4,1,'08:00:00','18:00:00' WHERE NOT EXISTS(SELECT 1 FROM store_hours WHERE weekday=4);
INSERT INTO store_hours(weekday,is_open,open_time,close_time)
SELECT 5,1,'08:00:00','18:00:00' WHERE NOT EXISTS(SELECT 1 FROM store_hours WHERE weekday=5);
INSERT INTO store_hours(weekday,is_open,open_time,close_time)
SELECT 6,1,'08:00:00','14:00:00' WHERE NOT EXISTS(SELECT 1 FROM store_hours WHERE weekday=6);