-- ============================================================
-- SalonPro X - Database Views
-- ============================================================

USE `salonprox_db`;

-- View: Active memberships with customer info
CREATE OR REPLACE VIEW `v_active_memberships` AS
SELECT
    cm.id AS membership_id,
    c.id AS customer_id,
    c.first_name AS customer_first_name,
    c.last_name AS customer_last_name,
    c.email AS customer_email,
    c.phone AS customer_phone,
    mt.name AS tier_name,
    mt.discount_percent,
    cm.start_date,
    cm.end_date,
    DATEDIFF(cm.end_date, CURDATE()) AS days_remaining,
    cm.status
FROM `customer_memberships` cm
JOIN `customers` c ON c.id = cm.customer_id
JOIN `membership_tiers` mt ON mt.id = cm.tier_id
WHERE cm.status = 'active';

-- View: Today's appointments
CREATE OR REPLACE VIEW `v_todays_appointments` AS
SELECT
    a.id,
    a.start_datetime,
    a.end_datetime,
    a.status,
    c.id AS customer_id,
    c.first_name AS customer_first_name,
    c.last_name AS customer_last_name,
    c.phone AS customer_phone,
    c.photo AS customer_photo,
    GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') AS services,
    GROUP_CONCAT(DISTINCT e.first_name, ' ', e.last_name SEPARATOR ', ') AS employees
FROM `appointments` a
JOIN `customers` c ON c.id = a.customer_id
LEFT JOIN `appointment_services` asi ON asi.appointment_id = a.id
LEFT JOIN `services` s ON s.id = asi.service_id
LEFT JOIN `employees` e ON e.id = asi.employee_id
WHERE DATE(a.start_datetime) = CURDATE()
GROUP BY a.id, a.start_datetime, a.end_datetime, a.status,
    c.id, c.first_name, c.last_name, c.phone, c.photo
ORDER BY a.start_datetime ASC;

-- View: Invoice summary
CREATE OR REPLACE VIEW `v_invoice_summary` AS
SELECT
    i.id,
    i.invoice_number,
    i.subtotal,
    i.tax_total,
    i.discount_total,
    i.grand_total,
    i.paid_amount,
    i.balance_amount,
    i.payment_status,
    i.created_at,
    c.id AS customer_id,
    c.first_name AS customer_first_name,
    c.last_name AS customer_last_name,
    c.phone AS customer_phone,
    u.first_name AS created_by_name
FROM `invoices` i
JOIN `customers` c ON c.id = i.customer_id
LEFT JOIN `users` u ON u.id = i.created_by;

-- View: Employee commission summary
CREATE OR REPLACE VIEW `v_employee_commission_summary` AS
SELECT
    e.id AS employee_id,
    e.first_name,
    e.last_name,
    e.job_title,
    COUNT(ec.id) AS total_transactions,
    COALESCE(SUM(CASE WHEN ec.paid_status = 'pending' THEN ec.commission_amount ELSE 0 END), 0) AS pending_commission,
    COALESCE(SUM(CASE WHEN ec.paid_status = 'paid' THEN ec.commission_amount ELSE 0 END), 0) AS paid_commission,
    COALESCE(SUM(ec.commission_amount), 0) AS total_commission
FROM `employees` e
LEFT JOIN `employee_commissions` ec ON ec.employee_id = e.id
WHERE e.is_active = 1
GROUP BY e.id, e.first_name, e.last_name, e.job_title;

-- View: Monthly revenue summary
CREATE OR REPLACE VIEW `v_monthly_revenue` AS
SELECT
    DATE_FORMAT(i.created_at, '%Y-%m') AS month,
    YEAR(i.created_at) AS year,
    MONTH(i.created_at) AS month_num,
    COUNT(DISTINCT i.id) AS invoice_count,
    COALESCE(SUM(i.subtotal), 0) AS subtotal,
    COALESCE(SUM(i.tax_total), 0) AS tax,
    COALESCE(SUM(i.discount_total), 0) AS discounts,
    COALESCE(SUM(i.grand_total), 0) AS revenue,
    COALESCE(SUM(i.paid_amount), 0) AS collected,
    COALESCE(SUM(i.balance_amount), 0) AS outstanding
FROM `invoices` i
GROUP BY DATE_FORMAT(i.created_at, '%Y-%m'), YEAR(i.created_at), MONTH(i.created_at)
ORDER BY year DESC, month_num DESC;

-- View: Product stock status
CREATE OR REPLACE VIEW `v_product_stock_status` AS
SELECT
    p.id,
    p.name,
    p.sku,
    p.barcode,
    p.current_stock,
    p.reorder_level,
    p.min_stock,
    p.max_stock,
    pc.name AS category_name,
    CASE
        WHEN p.current_stock <= 0 THEN 'out_of_stock'
        WHEN p.current_stock <= p.reorder_level THEN 'low_stock'
        WHEN p.current_stock >= p.max_stock THEN 'overstocked'
        ELSE 'in_stock'
    END AS stock_status,
    CASE
        WHEN p.current_stock <= 0 THEN '#DC3545'
        WHEN p.current_stock <= p.reorder_level THEN '#FFC107'
        WHEN p.current_stock >= p.max_stock THEN '#17A2B8'
        ELSE '#28A745'
    END AS status_color
FROM `products` p
LEFT JOIN `product_categories` pc ON pc.id = p.category_id
WHERE p.track_inventory = 1 AND p.is_active = 1;

-- View: Daily cash flow
CREATE OR REPLACE VIEW `v_daily_cash_flow` AS
SELECT
    DATE(cb.transaction_date) AS date,
    COALESCE(SUM(CASE WHEN cb.type = 'inflow' THEN cb.amount ELSE 0 END), 0) AS total_inflow,
    COALESCE(SUM(CASE WHEN cb.type = 'outflow' THEN cb.amount ELSE 0 END), 0) AS total_outflow,
    COALESCE(SUM(CASE WHEN cb.type = 'inflow' THEN cb.amount ELSE -cb.amount END), 0) AS net_flow
FROM `cash_book` cb
GROUP BY DATE(cb.transaction_date)
ORDER BY date DESC;

-- View: Appointment calendar events (for FullCalendar/calendar display)
CREATE OR REPLACE VIEW `v_calendar_events` AS
SELECT
    a.id AS event_id,
    CONCAT(c.first_name, ' ', c.last_name) AS title,
    a.start_datetime AS start,
    a.end_datetime AS `end`,
    a.status,
    CASE a.status
        WHEN 'pending' THEN '#FFC107'
        WHEN 'confirmed' THEN '#17A2B8'
        WHEN 'checked_in' THEN '#28A745'
        WHEN 'in_progress' THEN '#C8A44E'
        WHEN 'completed' THEN '#6C757D'
        WHEN 'cancelled' THEN '#DC3545'
        WHEN 'no_show' THEN '#343A40'
    END AS color,
    c.id AS customer_id,
    c.phone AS customer_phone,
    GROUP_CONCAT(DISTINCT CONCAT(s.name, ' (', e.first_name, ')') SEPARATOR ' | ') AS description
FROM `appointments` a
JOIN `customers` c ON c.id = a.customer_id
LEFT JOIN `appointment_services` asi ON asi.appointment_id = a.id
LEFT JOIN `services` s ON s.id = asi.service_id
LEFT JOIN `employees` e ON e.id = asi.employee_id
GROUP BY a.id, c.id, c.first_name, c.last_name, c.phone;
