-- =====================================================================
--  Liquor ERP — MySQL schema (InnoDB, utf8mb4, foreign keys, indexes)
--  Run this first, then run:  php database/seed.php  to create the admin.
-- =====================================================================

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

SET FOREIGN_KEY_CHECKS = 0;

-- ---------- Users / auth ----------
CREATE TABLE users (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    role        ENUM('super_admin','manager') NOT NULL DEFAULT 'super_admin',
    status      TINYINT(1) NOT NULL DEFAULT 1,
    last_login  DATETIME NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE remember_tokens (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    selector    VARCHAR(32) NOT NULL UNIQUE,
    validator   CHAR(64) NOT NULL,
    expires_at  DATETIME NOT NULL,
    INDEX (user_id),
    CONSTRAINT fk_rt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Shops ----------
CREATE TABLE shops (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(150) NOT NULL,
    license_number VARCHAR(80) NULL,
    gst            VARCHAR(30) NULL,
    owner          VARCHAR(120) NULL,
    address        VARCHAR(255) NULL,
    mobile         VARCHAR(20) NULL,
    email          VARCHAR(150) NULL,
    opening_date   DATE NULL,
    status         TINYINT(1) NOT NULL DEFAULT 1,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_shop_status (status)
) ENGINE=InnoDB;

-- ---------- Brands ----------
CREATE TABLE brands (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(150) NOT NULL,
    company   VARCHAR(150) NULL,
    category  VARCHAR(30) NOT NULL DEFAULT 'FL',   -- FL / BEER
    size_ml   INT UNSIGNED NOT NULL,
    status    TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_brand (name, size_ml, category),
    INDEX idx_brand_cat (category)
) ENGINE=InnoDB;

-- ---------- Suppliers ----------
CREATE TABLE suppliers (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(150) NOT NULL,
    address   VARCHAR(255) NULL,
    phone     VARCHAR(20) NULL,
    email     VARCHAR(150) NULL,
    gst       VARCHAR(30) NULL,
    status    TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_supplier_name (name)
) ENGINE=InnoDB;

-- ---------- Import batches ----------
CREATE TABLE import_batches (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id       INT UNSIGNED NOT NULL,
    period_month  TINYINT UNSIGNED NOT NULL,
    period_year   SMALLINT UNSIGNED NOT NULL,
    uploaded_by   INT UNSIGNED NULL,
    original_name VARCHAR(255) NOT NULL,
    archive_name  VARCHAR(255) NULL,
    status        ENUM('imported','failed') NOT NULL DEFAULT 'imported',
    imported_rows INT UNSIGNED NOT NULL DEFAULT 0,
    error_text    TEXT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_batch_period (shop_id, period_year, period_month),
    CONSTRAINT fk_batch_shop FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    CONSTRAINT fk_batch_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- Sales ----------
CREATE TABLE sales (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id    INT UNSIGNED NOT NULL,
    brand_id   INT UNSIGNED NOT NULL,
    sale_date  DATE NOT NULL,
    quantity   DECIMAL(12,2) NOT NULL DEFAULT 0,
    rate       DECIMAL(12,2) NOT NULL DEFAULT 0,
    amount     DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sale_date (sale_date),
    INDEX idx_sale_shop (shop_id),
    INDEX idx_sale_brand (brand_id),
    CONSTRAINT fk_sale_shop  FOREIGN KEY (shop_id)  REFERENCES shops(id)  ON DELETE CASCADE,
    CONSTRAINT fk_sale_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Purchases ----------
CREATE TABLE purchases (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id       INT UNSIGNED NOT NULL,
    brand_id      INT UNSIGNED NOT NULL,
    supplier_id   INT UNSIGNED NULL,
    purchase_date DATE NOT NULL,
    quantity      DECIMAL(12,2) NOT NULL DEFAULT 0,
    rate          DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_amount  DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pur_date (purchase_date),
    INDEX idx_pur_shop (shop_id),
    INDEX idx_pur_supplier (supplier_id),
    CONSTRAINT fk_pur_shop     FOREIGN KEY (shop_id)     REFERENCES shops(id)     ON DELETE CASCADE,
    CONSTRAINT fk_pur_brand    FOREIGN KEY (brand_id)    REFERENCES brands(id)    ON DELETE CASCADE,
    CONSTRAINT fk_pur_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- Expenses ----------
CREATE TABLE expenses (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id      INT UNSIGNED NOT NULL,
    expense_date DATE NOT NULL,
    category     VARCHAR(80) NULL,     -- Salary / Electricity / Internet / Fuel ...
    amount       DECIMAL(14,2) NOT NULL DEFAULT 0,
    note         VARCHAR(255) NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_exp_date (expense_date),
    INDEX idx_exp_shop (shop_id),
    CONSTRAINT fk_exp_shop FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Supplier payments ----------
CREATE TABLE supplier_payments (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id      INT UNSIGNED NULL,
    supplier_id  INT UNSIGNED NULL,
    payment_date DATE NOT NULL,
    amount       DECIMAL(14,2) NOT NULL DEFAULT 0,
    note         VARCHAR(255) NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pay_date (payment_date),
    INDEX idx_pay_supplier (supplier_id),
    CONSTRAINT fk_pay_shop     FOREIGN KEY (shop_id)     REFERENCES shops(id)     ON DELETE SET NULL,
    CONSTRAINT fk_pay_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- Stock (current position per shop+brand) ----------
CREATE TABLE stock (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id      INT UNSIGNED NOT NULL,
    brand_id     INT UNSIGNED NOT NULL,
    opening_qty  DECIMAL(12,2) NOT NULL DEFAULT 0,
    purchase_qty DECIMAL(12,2) NOT NULL DEFAULT 0,
    transfer_in  DECIMAL(12,2) NOT NULL DEFAULT 0,
    transfer_out DECIMAL(12,2) NOT NULL DEFAULT 0,
    sale_qty     DECIMAL(12,2) NOT NULL DEFAULT 0,
    damage_qty   DECIMAL(12,2) NOT NULL DEFAULT 0,
    closing_qty  DECIMAL(12,2) NOT NULL DEFAULT 0,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_stock (shop_id, brand_id),
    CONSTRAINT fk_stock_shop  FOREIGN KEY (shop_id)  REFERENCES shops(id)  ON DELETE CASCADE,
    CONSTRAINT fk_stock_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Stock movements (audit trail) ----------
CREATE TABLE stock_movements (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shop_id       INT UNSIGNED NOT NULL,
    brand_id      INT UNSIGNED NOT NULL,
    movement_type ENUM('purchase','transfer_in','transfer_out','sale','damage') NOT NULL,
    quantity      DECIMAL(12,2) NOT NULL,
    ref_date      DATE NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mv_shop_brand (shop_id, brand_id),
    INDEX idx_mv_date (ref_date),
    CONSTRAINT fk_mv_shop  FOREIGN KEY (shop_id)  REFERENCES shops(id)  ON DELETE CASCADE,
    CONSTRAINT fk_mv_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Audit log ----------
CREATE TABLE audit_logs (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NULL,
    action     VARCHAR(60) NOT NULL,
    detail     VARCHAR(500) NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_action (action),
    INDEX idx_audit_date (created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;