-- Migration: Initial Database Setup
-- Version: 001
-- Description: Create all tables and initial data for Textile Manager

-- ============================================================================
-- MIGRATION START
-- ============================================================================

-- Create migration tracking table
CREATE TABLE IF NOT EXISTS migrations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    version VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Record this migration
INSERT IGNORE INTO migrations (version, description) VALUES 
('001', 'Initial database setup with all tables');

-- ============================================================================
-- SHOPS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS shops (
    id INT PRIMARY KEY AUTO_INCREMENT,
    shop_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    address TEXT,
    contact VARCHAR(50),
    logo_url VARCHAR(500),
    owner_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_shops_email (email),
    INDEX idx_shops_owner (owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- USERS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    phone VARCHAR(50),
    role ENUM('owner', 'manager', 'employee') NOT NULL DEFAULT 'employee',
    shop_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_users_email (email),
    INDEX idx_users_shop (shop_id),
    INDEX idx_users_role (role),
    INDEX idx_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key constraints
ALTER TABLE shops ADD CONSTRAINT fk_shops_owner 
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE users ADD CONSTRAINT fk_users_shop 
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE;

-- ============================================================================
-- CUSTOMERS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    address TEXT,
    shop_id INT NOT NULL,
    total_orders INT DEFAULT 0,
    total_spent DECIMAL(10,2) DEFAULT 0.00,
    last_order_date DATE NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_customers_shop (shop_id),
    INDEX idx_customers_phone (phone),
    INDEX idx_customers_name (name),
    INDEX idx_customers_total_spent (total_spent),
    INDEX idx_customers_last_order (last_order_date),
    FULLTEXT idx_customers_search (name, phone, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CUSTOMER MEASUREMENTS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS customer_measurements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    measurements TEXT NOT NULL,
    notes TEXT,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_measurements_customer (customer_id),
    INDEX idx_measurements_shop (shop_id),
    INDEX idx_measurements_item (item_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- INVENTORY TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS inventory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    type ENUM('Cotton', 'Silk', 'Wool', 'Linen', 'Other') NOT NULL,
    color VARCHAR(100) NOT NULL,
    price_per_meter DECIMAL(8,2) NOT NULL,
    quantity DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    low_stock_threshold DECIMAL(8,2) NOT NULL DEFAULT 10.00,
    supplier VARCHAR(255),
    purchase_date DATE,
    expiry_date DATE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_inventory_shop (shop_id),
    INDEX idx_inventory_type (type),
    INDEX idx_inventory_quantity (quantity),
    INDEX idx_inventory_low_stock (low_stock_threshold),
    FULLTEXT idx_inventory_search (name, color, supplier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TAILORING ITEMS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS tailoring_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category ENUM('Men', 'Women', 'Kids', 'Other') NOT NULL,
    sewing_price DECIMAL(8,2) NOT NULL,
    artisan_wage DECIMAL(8,2) NOT NULL,
    description TEXT,
    estimated_time_hours INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_tailoring_shop (shop_id),
    INDEX idx_tailoring_category (category),
    INDEX idx_tailoring_active (is_active),
    INDEX idx_tailoring_price (sewing_price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- EMPLOYEES TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    role ENUM('Tailor', 'Manager', 'Cutter', 'Assistant') NOT NULL,
    monthly_salary DECIMAL(8,2),
    hire_date DATE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    address TEXT,
    emergency_contact VARCHAR(255),
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_employees_shop (shop_id),
    INDEX idx_employees_role (role),
    INDEX idx_employees_active (is_active),
    INDEX idx_employees_hire_date (hire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ORDERS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    memo_id VARCHAR(100),
    customer_id INT NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(50) NOT NULL,
    type ENUM('pickup', 'delivery') NOT NULL DEFAULT 'pickup',
    status ENUM('New', 'In Progress', 'Ready for Delivery', 'Delivered', 'Completed', 'Cancelled') NOT NULL DEFAULT 'New',
    order_date DATE NOT NULL,
    delivery_date DATE,
    delivery_address TEXT,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_artisan_wage DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    artisan_id INT,
    artisan_name VARCHAR(255),
    cutting_master_id INT,
    cutting_master_name VARCHAR(255),
    artisan_assigned_date DATE,
    artisan_completed_date DATE,
    cutting_completed_date DATE,
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    notes TEXT,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (artisan_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (cutting_master_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_orders_shop (shop_id),
    INDEX idx_orders_customer (customer_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_date (order_date),
    INDEX idx_orders_delivery (delivery_date),
    INDEX idx_orders_artisan (artisan_id),
    INDEX idx_orders_cutting_master (cutting_master_id),
    INDEX idx_orders_priority (priority),
    INDEX idx_orders_shop_status_date (shop_id, status, order_date),
    INDEX idx_orders_shop_customer_date (shop_id, customer_id, order_date),
    FULLTEXT idx_orders_search (memo_id, customer_name, notes)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ORDER ITEMS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(8,2) NOT NULL,
    artisan_wage DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    measurements TEXT,
    fabric_used DECIMAL(8,2) DEFAULT 0.00,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_order_items_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- INCOME TRANSACTIONS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS income_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    transaction_date DATE NOT NULL,
    order_id INT,
    payment_method ENUM('cash', 'card', 'upi', 'bank_transfer', 'other') DEFAULT 'cash',
    is_editable BOOLEAN DEFAULT TRUE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_income_shop (shop_id),
    INDEX idx_income_date (transaction_date),
    INDEX idx_income_category (category),
    INDEX idx_income_order (order_id),
    INDEX idx_income_payment_method (payment_method),
    INDEX idx_income_shop_date (shop_id, transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- EXPENSE TRANSACTIONS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    person VARCHAR(255),
    transaction_date DATE NOT NULL,
    employee_id INT,
    receipt_url VARCHAR(500),
    payment_method ENUM('cash', 'card', 'upi', 'bank_transfer', 'other') DEFAULT 'cash',
    is_editable BOOLEAN DEFAULT TRUE,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_expense_shop (shop_id),
    INDEX idx_expense_date (transaction_date),
    INDEX idx_expense_category (category),
    INDEX idx_expense_employee (employee_id),
    INDEX idx_expense_payment_method (payment_method),
    INDEX idx_expense_shop_date (shop_id, transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SETTINGS TABLES
-- ============================================================================
CREATE TABLE IF NOT EXISTS appearance_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    theme ENUM('light', 'dark') DEFAULT 'light',
    font ENUM('inter', 'manrope', 'system', 'poppins', 'pt-sans') DEFAULT 'inter',
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    UNIQUE KEY unique_shop_appearance (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notification_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sms_enabled BOOLEAN DEFAULT FALSE,
    sms_provider ENUM('twilio', 'other') DEFAULT 'twilio',
    sms_api_key VARCHAR(255),
    sms_api_secret VARCHAR(255),
    sms_sender_id VARCHAR(50),
    sms_message_template TEXT,
    whatsapp_enabled BOOLEAN DEFAULT FALSE,
    whatsapp_api_key VARCHAR(255),
    whatsapp_sender_number VARCHAR(50),
    whatsapp_message_template TEXT,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    UNIQUE KEY unique_shop_notifications (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- AUDIT AND REPORTING TABLES
-- ============================================================================
CREATE TABLE IF NOT EXISTS audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action ENUM('create', 'update', 'delete') NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id INT NOT NULL,
    user_id INT NOT NULL,
    user_name VARCHAR(255) NOT NULL,
    changes JSON,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_audit_shop (shop_id),
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS report_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    report_type ENUM('daily', 'weekly', 'monthly', 'yearly') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_income DECIMAL(12,2) DEFAULT 0.00,
    total_expenses DECIMAL(12,2) DEFAULT 0.00,
    net_profit DECIMAL(12,2) DEFAULT 0.00,
    order_count INT DEFAULT 0,
    customer_count INT DEFAULT 0,
    data JSON,
    shop_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_report_shop (shop_id),
    INDEX idx_report_type (report_type),
    INDEX idx_report_dates (start_date, end_date),
    UNIQUE KEY unique_shop_report (shop_id, report_type, start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- MIGRATION END
-- ============================================================================