-- ============================================================================
-- PUFF PUFF PASS — BAZA DANYCH MYSQL / PHPMYADMIN
-- System POS, Kalkulator, Magazyn, Zmiany Pracowników i Podział Zysków
-- ============================================================================

CREATE DATABASE IF NOT EXISTS `puffpuffpass_pos` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `puffpuffpass_pos`;

-- ----------------------------------------------------------------------------
-- 1. TABELA USTAWIEŃ SYSTEMU I PODZIAŁU ZYSKÓW
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key` VARCHAR(64) NOT NULL,
  `setting_value` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('split_seller_pct', '90', 'Procent zysku dla pracownika / sprzedawcy (0-100%)'),
('split_vault_pct', '10', 'Procent zysku do szafki firmy (0-100%)'),
('cost_to_vault', '1', 'Czy 100% kosztu towaru zawsze wraca do szafki (1 = tak, 0 = nie)'),
('receipt_prefix', 'PPP-', 'Prefiks numeracji paragonów fiskalnych')
ON DUPLICATE KEY UPDATE `setting_key`=`setting_key`;

-- ----------------------------------------------------------------------------
-- 2. TABELA KATEGORII I PRODUKTÓW
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(64) NOT NULL UNIQUE,
  `name` VARCHAR(128) NOT NULL,
  `category` VARCHAR(64) NOT NULL,
  `category_label` VARCHAR(128) NOT NULL,
  `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `cost` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `stock_quantity` INT NOT NULL DEFAULT 999,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `products` (`code`, `name`, `category`, `category_label`, `price`, `cost`) VALUES
-- Nasiona
('seed-blue-dream', 'Nasiono Blue Dream', 'seeds', 'Nasiona Weedu', 5000.00, 3500.00),
('seed-purple-haze', 'Nasiono Purple Haze', 'seeds', 'Nasiona Weedu', 3500.00, 2000.00),
('seed-white-widow', 'Nasiono White Widow', 'seeds', 'Nasiona Weedu', 3000.00, 2550.00),
('seed-og-kush', 'Nasiono Og Kush', 'seeds', 'Nasiona Weedu', 2500.00, 1450.00),

-- Jedzenie & Picie
('food-ciastko-weed', 'Ciastko Weed', 'food', 'Jedzenie & Picie', 3500.00, 0.00),
('food-ciastko-puff', 'Ciastko Puff', 'food', 'Jedzenie & Picie', 3500.00, 0.00),
('food-herbata', 'Herbata Ziołowa', 'food', 'Jedzenie & Picie', 5500.00, 0.00),
('food-zelki', 'Żelki Weed', 'food', 'Jedzenie & Picie', 5500.00, 0.00),

-- Produkty & Akcesoria
('acc-nawadnianie', 'System Nawadniania', 'accessories', 'Produkty & Akcesoria', 60000.00, 49500.00),
('acc-doniczka', 'Doniczka', 'accessories', 'Produkty & Akcesoria', 40000.00, 31000.00),
('acc-namiot', 'Namiot Uprawny', 'accessories', 'Produkty & Akcesoria', 200000.00, 165000.00),
('acc-bongo', 'Bongo', 'accessories', 'Produkty & Akcesoria', 20000.00, 11000.00),
('acc-stol', 'Stół do pakowania', 'accessories', 'Produkty & Akcesoria', 170000.00, 165000.00),
('acc-bibulka', 'Bibułka', 'accessories', 'Produkty & Akcesoria', 1500.00, 550.00),
('acc-woreczki', 'Pistalkowe Woreczki', 'accessories', 'Produkty & Akcesoria', 2000.00, 880.00),
('acc-zapalniczka', 'Zapalniczka', 'accessories', 'Produkty & Akcesoria', 200.00, 110.00),

-- Jointy
('joint-blue-dream', 'Joint Blue Dream', 'joints', 'Gotowe Jointy', 7500.00, 0.00),
('joint-purple-haze', 'Joint Purple Haze', 'joints', 'Gotowe Jointy', 7500.00, 0.00),
('joint-white-widow', 'Joint White Widow', 'joints', 'Gotowe Jointy', 7500.00, 0.00),
('joint-og-kush', 'Joint Og Kush', 'joints', 'Gotowe Jointy', 7500.00, 0.00),

-- Nawozy
('fert-premium', 'Nawóz Premium', 'fertilizers', 'Nawozy Uprawne', 10000.00, 3300.00),
('fert-plonowy', 'Nawóz Plonowy', 'fertilizers', 'Nawozy Uprawne', 10000.00, 4400.00)
ON DUPLICATE KEY UPDATE `price`=VALUES(`price`), `cost`=VALUES(`cost`);

-- ----------------------------------------------------------------------------
-- 3. TABELA FIRM PARTNERSKICH I RABATÓW
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `companies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(128) NOT NULL UNIQUE,
  `discount_zarzad` INT NOT NULL DEFAULT 0,
  `discount_pracownik` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `companies` (`name`, `discount_zarzad`, `discount_pracownik`) VALUES
('Brak', 0, 0),
('LSC', 25, 20),
('Armor', 25, 15),
('Burger Shot', 20, 15),
('Tropical', 25, 15),
('Piekarnia', 30, 20),
('Vesuvio', 25, 15)
ON DUPLICATE KEY UPDATE `discount_zarzad`=VALUES(`discount_zarzad`), `discount_pracownik`=VALUES(`discount_pracownik`);

-- ----------------------------------------------------------------------------
-- 4. TABELA ZMIAN PRACOWNIKÓW (SHIFTS)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `shifts` (
  `id` VARCHAR(64) PRIMARY KEY,
  `seller_name` VARCHAR(128) NOT NULL,
  `start_time` BIGINT NOT NULL,
  `end_time` BIGINT DEFAULT NULL,
  `work_ms` BIGINT DEFAULT 0,
  `break_ms` BIGINT DEFAULT 0,
  `total_sales` DECIMAL(12,2) DEFAULT 0.00,
  `status` ENUM('active', 'break', 'finished') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 5. TABELA TRANSAKCJI / PARAGONÓW (SALES)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `receipt_no` VARCHAR(64) NOT NULL UNIQUE,
  `shift_id` VARCHAR(64) DEFAULT NULL,
  `seller_name` VARCHAR(128) NOT NULL,
  `company_name` VARCHAR(128) NOT NULL DEFAULT 'Brak',
  `role_name` VARCHAR(64) NOT NULL DEFAULT 'Pracownik',
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_pct` INT NOT NULL DEFAULT 0,
  `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,       -- ILE PŁACI KLIENT
  `cost_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,        -- KOSZT ZAKUPU TOWARU
  `profit_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,      -- CAŁKOWITY ZYSK NETTO
  `to_vault` DECIMAL(12,2) NOT NULL DEFAULT 0.00,           -- ILE DO SZAFKI (KOSZT + CZĘŚĆ ZYSKU FIRMY)
  `to_seller` DECIMAL(12,2) NOT NULL DEFAULT 0.00,          -- ILE DLA SIEBIE (WYPŁATA / PREMIA)
  `split_ratio` VARCHAR(32) NOT NULL DEFAULT '50/50',
  `timestamp_ms` BIGINT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_seller` (`seller_name`),
  INDEX `idx_receipt` (`receipt_no`),
  INDEX `idx_timestamp` (`timestamp_ms`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 6. TABELA POZYCJI TRANSAKCJI (SALE ITEMS)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sale_id` INT NOT NULL,
  `receipt_no` VARCHAR(64) NOT NULL,
  `product_name` VARCHAR(128) NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(12,2) NOT NULL,
  `unit_cost` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_value` DECIMAL(12,2) NOT NULL,
  `total_cost` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `item_profit` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  FOREIGN KEY (`sale_id`) REFERENCES `sales`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 7. WIDOK PODSUMOWANIA SZAFKI I WYPŁAT PRACOWNIKÓW
-- ----------------------------------------------------------------------------
CREATE OR REPLACE VIEW `v_financial_summary` AS
SELECT 
  COUNT(s.id) AS total_receipts,
  COALESCE(SUM(s.subtotal), 0) AS gross_turnover,
  COALESCE(SUM(s.discount_amount), 0) AS total_discounts,
  COALESCE(SUM(s.total_amount), 0) AS client_paid_total,     -- ŁĄCZNIE WPŁACONO OD KLIENTÓW
  COALESCE(SUM(s.cost_amount), 0) AS total_stock_cost,       -- KOSZTY ODNOWIENIA MAGAZYNU
  COALESCE(SUM(s.profit_amount), 0) AS total_net_profit,     -- ŁĄCZNY ZYSK
  COALESCE(SUM(s.to_vault), 0) AS total_to_vault,            -- ŁĄCZNIE DO SZAFKI FIRMY
  COALESCE(SUM(s.to_seller), 0) AS total_to_sellers          -- ŁĄCZNIE WYPŁACONE PRACOWNIKOM
FROM `sales` s;