-- Quality Assurance Module Migration
-- Tables for managing quality inspections, tests, standards, and non-conformances
-- Designed for agriculture wholesale and packing industry

-- QA Standards table (defines quality criteria for products)
CREATE TABLE IF NOT EXISTS qa_standards (
    id SERIAL PRIMARY KEY,
    org_id UUID NOT NULL REFERENCES orgs(org_id),
    standard_code VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100), -- e.g., 'size', 'weight', 'appearance', 'freshness', 'packaging'
    product_category_id UUID,
    criteria JSONB, -- Detailed criteria: {min_weight: 100, max_weight: 150, color: 'green', etc}
    status VARCHAR(20) DEFAULT 'active', -- 'active', 'draft', 'archived'
    effective_date DATE,
    expiry_date DATE,
    created_by UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isdeleted BOOLEAN NOT NULL DEFAULT false,
    updatedat TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE(org_id, standard_code)
);

-- QA Inspections table (records of quality inspections performed)
CREATE TABLE IF NOT EXISTS qa_inspections (
    id SERIAL PRIMARY KEY,
    org_id UUID NOT NULL REFERENCES orgs(org_id),
    inspection_number VARCHAR(50) NOT NULL,
    inspection_type VARCHAR(50) NOT NULL, -- 'receiving', 'in-process', 'final', 'random', 'customer-complaint'
    inspection_date TIMESTAMP NOT NULL,
    product_id UUID REFERENCES products(product_id),
    batch_number VARCHAR(100),
    supplier_id UUID,
    location_id UUID,
    quantity_inspected DECIMAL(10,2),
    unit VARCHAR(20),
    inspector_id UUID,
    standard_id INTEGER REFERENCES qa_standards(id),
    overall_result VARCHAR(20), -- 'pass', 'fail', 'conditional', 'pending'
    defect_rate DECIMAL(5,2), -- Percentage
    notes TEXT,
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'in-progress', 'completed', 'cancelled'
    priority VARCHAR(20) DEFAULT 'normal', -- 'low', 'normal', 'high', 'urgent'
    metadata JSONB, -- Additional flexible data
    created_by UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isdeleted BOOLEAN NOT NULL DEFAULT false,
    updatedat TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE(org_id, inspection_number)
);

-- QA Tests table (specific tests conducted during inspections)
CREATE TABLE IF NOT EXISTS qa_tests (
    id SERIAL PRIMARY KEY,
    org_id UUID NOT NULL REFERENCES orgs(org_id),
    inspection_id INTEGER REFERENCES qa_inspections(id) ON DELETE CASCADE,
    test_code VARCHAR(50) NOT NULL,
    test_name VARCHAR(255) NOT NULL,
    test_category VARCHAR(100), -- 'physical', 'chemical', 'microbiological', 'sensory', 'visual'
    parameter VARCHAR(100), -- What is being tested: 'weight', 'size', 'color', 'temperature', 'pH', etc
    expected_value VARCHAR(100),
    actual_value VARCHAR(100),
    tolerance VARCHAR(50), -- Acceptable range
    result VARCHAR(20), -- 'pass', 'fail', 'marginal'
    test_method VARCHAR(255),
    equipment_used VARCHAR(255),
    tested_by UUID,
    tested_at TIMESTAMP,
    notes TEXT,
    attachments JSONB, -- Array of file references
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isdeleted BOOLEAN NOT NULL DEFAULT false,
    updatedat TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- QA Results table (detailed measurements and observations)
CREATE TABLE IF NOT EXISTS qa_results (
    id SERIAL PRIMARY KEY,
    org_id UUID NOT NULL REFERENCES orgs(org_id),
    inspection_id INTEGER REFERENCES qa_inspections(id) ON DELETE CASCADE,
    test_id INTEGER REFERENCES qa_tests(id) ON DELETE CASCADE,
    sample_number INTEGER,
    measurement_type VARCHAR(100), -- 'weight', 'length', 'width', 'temperature', 'count', etc
    measurement_value DECIMAL(10,4),
    measurement_unit VARCHAR(20),
    pass_fail VARCHAR(10), -- 'pass', 'fail'
    grade VARCHAR(20), -- 'A', 'B', 'C', 'reject'
    notes TEXT,
    recorded_by UUID,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isdeleted BOOLEAN NOT NULL DEFAULT false,
    updatedat TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- QA Non-Conformances table (issues and corrective actions)
CREATE TABLE IF NOT EXISTS qa_non_conformances (
    id SERIAL PRIMARY KEY,
    org_id UUID NOT NULL REFERENCES orgs(org_id),
    nc_number VARCHAR(50) NOT NULL,
    inspection_id INTEGER REFERENCES qa_inspections(id),
    test_id INTEGER REFERENCES qa_tests(id),
    detected_date TIMESTAMP NOT NULL,
    category VARCHAR(100), -- 'product-defect', 'process-deviation', 'documentation', 'supplier-issue', 'equipment-failure'
    severity VARCHAR(20), -- 'minor', 'major', 'critical'
    description TEXT NOT NULL,
    root_cause TEXT,
    immediate_action TEXT, -- What was done immediately
    corrective_action TEXT, -- Long-term fix
    preventive_action TEXT, -- How to prevent recurrence
    responsible_person_id UUID,
    due_date DATE,
    completion_date DATE,
    status VARCHAR(20) DEFAULT 'open', -- 'open', 'investigating', 'action-taken', 'verified', 'closed'
    verification_notes TEXT,
    verified_by UUID,
    verified_at TIMESTAMP,
    cost_impact DECIMAL(10,2),
    attachments JSONB,
    created_by UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isdeleted BOOLEAN NOT NULL DEFAULT false,
    updatedat TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE(org_id, nc_number)
);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_qa_inspections_org ON qa_inspections(org_id);
CREATE INDEX IF NOT EXISTS idx_qa_inspections_date ON qa_inspections(inspection_date);
CREATE INDEX IF NOT EXISTS idx_qa_inspections_product ON qa_inspections(product_id);
CREATE INDEX IF NOT EXISTS idx_qa_inspections_status ON qa_inspections(status);
CREATE INDEX IF NOT EXISTS idx_qa_tests_inspection ON qa_tests(inspection_id);
CREATE INDEX IF NOT EXISTS idx_qa_results_inspection ON qa_results(inspection_id);
CREATE INDEX IF NOT EXISTS idx_qa_nc_org ON qa_non_conformances(org_id);
CREATE INDEX IF NOT EXISTS idx_qa_nc_status ON qa_non_conformances(status);
CREATE INDEX IF NOT EXISTS idx_qa_standards_org ON qa_standards(org_id);

-- Tables are now ready for sync via LocalSyncManager
-- Register them in your JavaScript code using addTables() or getManager()

-- Add comments for documentation
COMMENT ON TABLE qa_standards IS 'Quality standards and criteria for products';
COMMENT ON TABLE qa_inspections IS 'Quality inspection records';
COMMENT ON TABLE qa_tests IS 'Individual tests performed during inspections';
COMMENT ON TABLE qa_results IS 'Detailed measurement results';
COMMENT ON TABLE qa_non_conformances IS 'Non-conformance reports and corrective actions';
