-- Kayal Aqua Fish Business Management Database
-- Database: u752449863_kastore

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'manager', 'staff') DEFAULT 'staff',
    phone VARCHAR(15),
    status ENUM('active', 'inactive') DEFAULT 'active',
    remember_token VARCHAR(100) NULL,
    remember_expires DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Business segments
CREATE TABLE IF NOT EXISTS business_segments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories for expenses and revenue
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type ENUM('expense', 'revenue') NOT NULL,
    segment_id INT NULL,
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_segment (segment_id),
    INDEX idx_type (type)
);

-- Investments and Loans
CREATE TABLE IF NOT EXISTS investments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('investment', 'loan') NOT NULL,
    title VARCHAR(200) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    interest_rate DECIMAL(5,2) DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    segment_id INT NULL,
    description TEXT,
    lender VARCHAR(200) NULL,
    tenure_months INT DEFAULT 0,
    emi_amount DECIMAL(10,2) DEFAULT 0,
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_segment (segment_id),
    INDEX idx_created_by (created_by),
    INDEX idx_type (type),
    INDEX idx_status (status)
);

-- Expenses
CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    category_id INT NULL,
    segment_id INT NULL,
    expense_date DATE NOT NULL,
    description TEXT,
    vendor VARCHAR(200) NULL,
    payment_method VARCHAR(50) NULL,
    receipt_image VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_segment (segment_id),
    INDEX idx_created_by (created_by),
    INDEX idx_expense_date (expense_date)
);

-- Sales/Revenue
CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    quantity DECIMAL(10,2) DEFAULT 1,
    unit_price DECIMAL(10,2) NULL,
    category_id INT NULL,
    segment_id INT NULL,
    sale_date DATE NOT NULL,
    customer_name VARCHAR(100) NULL,
    customer_phone VARCHAR(15) NULL,
    description TEXT,
    invoice_number VARCHAR(50) NULL,
    receipt_image VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_segment (segment_id),
    INDEX idx_created_by (created_by),
    INDEX idx_sale_date (sale_date),
    INDEX idx_invoice (invoice_number)
);

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Activity logs table
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_created_at (created_at)
);

-- Insert default business segments (only if they don't exist)
INSERT IGNORE INTO business_segments (id, name, description) VALUES
(1, 'Fish Sales', 'Fresh fish retail and wholesale'),
(2, 'Dry Fish Sales', 'Processed dry fish products'),
(3, 'Ornamental Fish Sales', 'Decorative fish for aquariums'),
(4, 'Fish Fry/Roast Shop', 'Cooked fish products'),
(5, 'Fish Cutting Service', 'Fish processing and cutting service');

-- Insert default categories (only if they don't exist)
INSERT IGNORE INTO categories (id, name, type, segment_id, description) VALUES
-- Revenue categories
(1, 'Fresh Fish Sales', 'revenue', 1, 'Revenue from fresh fish sales'),
(2, 'Dry Fish Sales', 'revenue', 2, 'Revenue from dry fish products'),
(3, 'Ornamental Fish Sales', 'revenue', 3, 'Revenue from ornamental fish'),
(4, 'Fried Fish Sales', 'revenue', 4, 'Revenue from fried fish products'),
(5, 'Cutting Service', 'revenue', 5, 'Revenue from fish cutting service'),

-- Expense categories
(6, 'Fish Purchase', 'expense', 1, 'Cost of purchasing fish'),
(7, 'Transportation', 'expense', NULL, 'Transportation costs'),
(8, 'Utilities', 'expense', NULL, 'Electricity, water, etc.'),
(9, 'Staff Salaries', 'expense', NULL, 'Employee salaries'),
(10, 'Equipment', 'expense', NULL, 'Equipment and tools'),
(11, 'Maintenance', 'expense', NULL, 'Maintenance and repairs'),
(12, 'Marketing', 'expense', NULL, 'Marketing and advertising'),
(13, 'Rent', 'expense', NULL, 'Shop/facility rent'),
(14, 'Other', 'expense', NULL, 'Other miscellaneous expenses');

-- Insert default settings (only if they don't exist)
INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
('company_name', 'Kayal Aqua', 'Company name'),
('currency', '₹', 'Default currency symbol'),
('date_format', 'Y-m-d', 'Default date format'),
('timezone', 'Asia/Kolkata', 'Default timezone');