-- Database tables for Cashflow StockMaster Application (Multi-tenant SaaS)
-- Execute these SQL commands in your database

-- Specialized cashbook users table (Super admin, Business owners, Staff)
-- Separate from your existing users table to avoid conflicts
CREATE TABLE IF NOT EXISTS cashbook_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(255),
    role ENUM('super_admin', 'business_owner', 'staff') NOT NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    business_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    remember_token VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role (role),
    INDEX idx_status (status),
    INDEX idx_business_id (business_id),
    INDEX idx_email (email)
);

-- Business table
CREATE TABLE IF NOT EXISTS cashbook_businesses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    address TEXT,
    phone VARCHAR(255),
    email VARCHAR(255),
    website VARCHAR(255),
    logo VARCHAR(255),
    owner_id BIGINT UNSIGNED NOT NULL,
    subscription_status ENUM('active', 'inactive', 'suspended', 'trial') DEFAULT 'trial',
    subscription_expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_owner_id (owner_id),
    INDEX idx_subscription_status (subscription_status),
    FOREIGN KEY (owner_id) REFERENCES cashbook_users(id) ON DELETE CASCADE
);

-- Staff permissions table
CREATE TABLE IF NOT EXISTS cashbook_staff_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    permission_name VARCHAR(255) NOT NULL,
    permission_value BOOLEAN DEFAULT true,
    business_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_permission (user_id, permission_name, business_id),
    INDEX idx_user_id (user_id),
    INDEX idx_business_id (business_id),
    FOREIGN KEY (user_id) REFERENCES cashbook_users(id) ON DELETE CASCADE,
    FOREIGN KEY (business_id) REFERENCES cashbook_businesses(id) ON DELETE CASCADE
);

-- Cash transactions table
CREATE TABLE IF NOT EXISTS cashbook_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(15,2) NOT NULL,
    category VARCHAR(255) NOT NULL,
    description TEXT,
    type ENUM('income', 'expense') NOT NULL,
    transaction_date DATE NOT NULL,
    transaction_time TIME,
    business_id BIGINT UNSIGNED NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_date (transaction_date),
    INDEX idx_business_id (business_id),
    INDEX idx_created_by (created_by),
    FOREIGN KEY (business_id) REFERENCES cashbook_businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES cashbook_users(id) ON DELETE CASCADE
);

-- Inventory items table
CREATE TABLE IF NOT EXISTS cashbook_inventory (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    cost_price DECIMAL(15,2) NOT NULL,
    selling_price DECIMAL(15,2) NOT NULL,
    category VARCHAR(255) NOT NULL,
    low_stock_threshold INT NOT NULL DEFAULT 10,
    date_added DATE NOT NULL,
    business_id BIGINT UNSIGNED NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_quantity (quantity),
    INDEX idx_category (category),
    INDEX idx_business_id (business_id),
    INDEX idx_created_by (created_by),
    FOREIGN KEY (business_id) REFERENCES cashbook_businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES cashbook_users(id) ON DELETE CASCADE
);

-- Staff members table (keeping for backward compatibility and additional staff info)
CREATE TABLE IF NOT EXISTS cashbook_staff_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    position VARCHAR(255) NOT NULL,
    salary DECIMAL(15,2) NOT NULL,
    join_date DATE NOT NULL,
    business_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_business_id (business_id),
    FOREIGN KEY (user_id) REFERENCES cashbook_users(id) ON DELETE CASCADE,
    FOREIGN KEY (business_id) REFERENCES cashbook_businesses(id) ON DELETE CASCADE
);

-- Personal access tokens for API authentication (cashbook specific)
CREATE TABLE IF NOT EXISTS cashbook_personal_access_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tokenable_type VARCHAR(255) NOT NULL DEFAULT 'cashbook_user',
    tokenable_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    abilities TEXT,
    last_used_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tokenable (tokenable_type, tokenable_id),
    INDEX idx_token (token),
    FOREIGN KEY (tokenable_id) REFERENCES cashbook_users(id) ON DELETE CASCADE
);

-- Insert default super admin user (Change password after first login)
-- Default password is 'password' - CHANGE THIS IMMEDIATELY AFTER FIRST LOGIN
INSERT INTO cashbook_users (name, email, password, role, status) VALUES 
('Super Admin', 'admin@cashflowstockmaster.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin', 'active')
ON DUPLICATE KEY UPDATE name = name;

-- Available permissions for staff members
-- Can be inserted as default permissions or managed through admin interface
/*
Common permissions:
- can_add_cash_in
- can_edit_cash_in
- can_delete_cash_in
- can_add_cash_out
- can_edit_cash_out
- can_delete_cash_out
- can_add_inventory
- can_edit_inventory
- can_delete_inventory
- can_view_reports
- can_export_reports
- can_manage_staff (for senior staff)
*/
