-- Cantina Escolar Colégio Cristão Plenitude
-- Estrutura inicial para MySQL 8+
-- Charset recomendado: utf8mb4

CREATE DATABASE IF NOT EXISTS cantina_colplenitude
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE cantina_colplenitude;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS stock_movements;
DROP TABLE IF EXISTS pix_charges;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS wallet_transactions;
DROP TABLE IF EXISTS wallets;
DROP TABLE IF EXISTS student_restrictions;
DROP TABLE IF EXISTS student_limits;
DROP TABLE IF EXISTS parents_students;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS app_settings;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(191) NOT NULL,
  password_hash CHAR(64) NOT NULL,
  role ENUM('admin','operator','parent') NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE students (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  enrollment VARCHAR(80) NOT NULL,
  class_name VARCHAR(80) NULL,
  shift VARCHAR(50) NULL,
  qr_code VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_students_enrollment (enrollment)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parents_students (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_user_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  relationship_type VARCHAR(50) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_parent_student (parent_user_id, student_id),
  CONSTRAINT fk_ps_parent FOREIGN KEY (parent_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ps_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_categories_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id BIGINT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  sale_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stock_qty DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  active TINYINT(1) NOT NULL DEFAULT 1,
  blocked_for_preorder TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_products_category (category_id),
  CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wallets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT UNSIGNED NOT NULL,
  balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  blocked TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_wallet_student (student_id),
  CONSTRAINT fk_wallet_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wallet_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  wallet_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(40) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_wallet_transactions_wallet (wallet_id),
  CONSTRAINT fk_wallet_tx_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_limits (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT UNSIGNED NOT NULL,
  daily_limit DECIMAL(10,2) NULL,
  weekly_limit DECIMAL(10,2) NULL,
  monthly_limit DECIMAL(10,2) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_student_limits_student (student_id),
  CONSTRAINT fk_student_limits_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_restrictions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_student_restriction (student_id, product_id),
  KEY idx_student_restrictions_product (product_id),
  CONSTRAINT fk_sr_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_sr_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_movements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  type ENUM('in','adjustment','loss','sale') NOT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  reason VARCHAR(255) NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_stock_product (product_id),
  KEY idx_stock_actor (actor_user_id),
  CONSTRAINT fk_stock_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_stock_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(40) NOT NULL DEFAULT 'counter',
  status VARCHAR(40) NOT NULL DEFAULT 'created',
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  payment_method VARCHAR(40) NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_orders_student (student_id),
  CONSTRAINT fk_orders_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_order_items_order (order_id),
  KEY idx_order_items_product (product_id),
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pix_charges (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  kind VARCHAR(40) NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  parent_user_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  pix_code LONGTEXT NULL,
  qr_payload LONGTEXT NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NULL,
  paid_at DATETIME NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'internal',
  external_id VARCHAR(191) NULL,
  ticket_url LONGTEXT NULL,
  last_payload_json LONGTEXT NULL,
  PRIMARY KEY (id),
  KEY idx_pix_student (student_id),
  KEY idx_pix_parent (parent_user_id),
  KEY idx_pix_order (order_id),
  KEY idx_pix_external (external_id),
  CONSTRAINT fk_pix_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_pix_parent FOREIGN KEY (parent_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_pix_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity VARCHAR(100) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  details_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_actor (actor_user_id),
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_settings (
  `key` VARCHAR(120) NOT NULL,
  `value` LONGTEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (`key`, `value`, updated_at) VALUES
('app_base_url', 'https://cantina.colplenitude.com.br', NOW()),
('mercado_pago_use_real_pix', 'true', NOW()),
('mercado_pago_access_token', '', NOW()),
('mercado_pago_public_key', '', NOW()),
('mercado_pago_webhook_token', 'troque-este-token', NOW()),
('payment_method_wallet', 'true', NOW()),
('payment_method_cash', 'true', NOW()),
('payment_method_pix', 'true', NOW()),
('payment_method_credit_card', 'true', NOW()),
('payment_method_debit_card', 'true', NOW())
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`), updated_at = VALUES(updated_at);

INSERT INTO users (name, email, password_hash, role, active, created_at) VALUES
('Administrador', 'admin@cantina.local', SHA2('123456', 256), 'admin', 1, NOW()),
('Operador', 'operador@cantina.local', SHA2('123456', 256), 'operator', 1, NOW()),
('Responsável Teste', 'responsavel@teste.com', SHA2('123456', 256), 'parent', 1, NOW());

INSERT INTO categories (name, active) VALUES
('Lanches', 1),
('Bebidas', 1),
('Doces', 1);

INSERT INTO students (name, enrollment, class_name, shift, qr_code, active, created_at)
VALUES ('Aluno Exemplo', '2026001', '1º Ano A', 'Manhã', 'student:2026001', 1, NOW());

INSERT INTO wallets (student_id, balance, blocked, updated_at)
SELECT id, 100.00, 0, NOW() FROM students WHERE enrollment = '2026001';

INSERT INTO student_limits (student_id, daily_limit, weekly_limit, monthly_limit, updated_at)
SELECT id, 30.00, 120.00, 400.00, NOW() FROM students WHERE enrollment = '2026001';

INSERT INTO parents_students (parent_user_id, student_id, relationship_type)
SELECT u.id, s.id, 'responsável'
FROM users u, students s
WHERE u.email = 'responsavel@teste.com'
  AND s.enrollment = '2026001';

INSERT INTO products (category_id, name, description, sale_price, stock_qty, active, blocked_for_preorder)
SELECT c.id, 'Sanduíche Natural', 'Pão integral com recheio leve', 8.50, 50.00, 1, 0
FROM categories c WHERE c.name = 'Lanches';

INSERT INTO products (category_id, name, description, sale_price, stock_qty, active, blocked_for_preorder)
SELECT c.id, 'Suco Natural', 'Suco da casa', 6.00, 40.00, 1, 0
FROM categories c WHERE c.name = 'Bebidas';