-- ============================================================
-- KopierSFlow ERP — 003_sales.sql
-- Importar TERCERO en phpMyAdmin
-- ============================================================

CREATE TABLE customers (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    customer_number   VARCHAR(30),
    name              VARCHAR(200) NOT NULL,
    legal_name        VARCHAR(200),
    type              ENUM('supermarket','retail_chain','bodega','restaurant','b2b','other') DEFAULT 'b2b',
    ein               VARCHAR(20),
    address           TEXT,
    city              VARCHAR(100),
    state             CHAR(2),
    zip               VARCHAR(10),
    phone             VARCHAR(30),
    email             VARCHAR(150),
    website           VARCHAR(200),
    payment_terms     TINYINT UNSIGNED DEFAULT 30,
    credit_limit      DECIMAL(14,4) DEFAULT 0,
    price_list_id     BIGINT UNSIGNED,
    tax_exempt        TINYINT(1)    DEFAULT 0,
    tax_exempt_number VARCHAR(60),
    tax_state         CHAR(2),
    loyalty_tier      ENUM('bronze','silver','gold','platinum') DEFAULT 'bronze',
    salesperson_id    BIGINT UNSIGNED,
    route_id          BIGINT UNSIGNED,
    is_active         TINYINT(1)    DEFAULT 1,
    notes             TEXT,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_name  (company_id, name),
    INDEX idx_state (company_id, state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_contacts (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(150) NOT NULL,
    title       VARCHAR(100),
    email       VARCHAR(150),
    phone       VARCHAR(30),
    is_primary  TINYINT(1) DEFAULT 0,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_price_lists (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    currency    CHAR(3) DEFAULT 'USD',
    is_default  TINYINT(1) DEFAULT 0,
    is_active   TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE price_list_items (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    price_list_id BIGINT UNSIGNED NOT NULL,
    product_id    BIGINT UNSIGNED NOT NULL,
    price         DECIMAL(14,4)  NOT NULL,
    FOREIGN KEY (price_list_id) REFERENCES customer_price_lists(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE price_tier_rates (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    product_id     BIGINT UNSIGNED NOT NULL,
    tier           ENUM('bronze','silver','gold','platinum') NOT NULL,
    price          DECIMAL(14,4) NOT NULL,
    effective_from DATE,
    effective_to   DATE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE price_volume_breaks (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    product_id     BIGINT UNSIGNED NOT NULL,
    min_qty        DECIMAL(14,4) NOT NULL,
    price          DECIMAL(14,4) NOT NULL,
    effective_from DATE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE price_zone_rates (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    product_id     BIGINT UNSIGNED NOT NULL,
    state          CHAR(2) NOT NULL,
    price          DECIMAL(14,4) NOT NULL,
    effective_from DATE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE price_contracts (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    product_id  BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    price       DECIMAL(14,4) NOT NULL,
    start_date  DATE          NOT NULL,
    end_date    DATE,
    is_active   TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE promotions (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    name           VARCHAR(150) NOT NULL,
    type           ENUM('percent_off','amount_off','buy_x_get_y') NOT NULL,
    discount_value DECIMAL(10,4),
    buy_qty        DECIMAL(10,4),
    get_qty        DECIMAL(10,4),
    start_date     DATE NOT NULL,
    end_date       DATE NOT NULL,
    priority       TINYINT DEFAULT 0,
    is_active      TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE promotion_products (
    promotion_id BIGINT UNSIGNED NOT NULL,
    product_id   BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (promotion_id, product_id),
    FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE quotes (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    quote_number    VARCHAR(30)    NOT NULL,
    customer_id     BIGINT UNSIGNED NOT NULL,
    quote_date      DATE           NOT NULL,
    valid_until     DATE,
    status          ENUM('draft','sent','accepted','rejected','expired','converted') DEFAULT 'draft',
    subtotal        DECIMAL(18,4)  DEFAULT 0,
    discount_amount DECIMAL(18,4)  DEFAULT 0,
    tax_amount      DECIMAL(18,4)  DEFAULT 0,
    total           DECIMAL(18,4)  DEFAULT 0,
    notes           TEXT,
    converted_to_so BIGINT UNSIGNED NULL,
    created_by      BIGINT UNSIGNED,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY uq_quote_number (company_id, quote_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sales_orders (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    order_number    VARCHAR(30)    NOT NULL,
    customer_id     BIGINT UNSIGNED NOT NULL,
    quote_id        BIGINT UNSIGNED NULL,
    order_date      DATE           NOT NULL,
    requested_date  DATE,
    status          ENUM('draft','confirmed','picking','packed','shipped','delivered','cancelled') DEFAULT 'draft',
    warehouse_id    BIGINT UNSIGNED,
    route_id        BIGINT UNSIGNED,
    salesperson_id  BIGINT UNSIGNED,
    price_list_id   BIGINT UNSIGNED,
    subtotal        DECIMAL(18,4)  DEFAULT 0,
    discount_amount DECIMAL(18,4)  DEFAULT 0,
    tax_amount      DECIMAL(18,4)  DEFAULT 0,
    delivery_fee    DECIMAL(18,4)  DEFAULT 0,
    total           DECIMAL(18,4)  DEFAULT 0,
    shipping_address TEXT,
    notes           TEXT,
    created_by      BIGINT UNSIGNED,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY uq_order_number (company_id, order_number),
    INDEX idx_status (company_id, status),
    INDEX idx_date   (company_id, order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sales_order_lines (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sales_order_id   BIGINT UNSIGNED NOT NULL,
    company_id       BIGINT UNSIGNED NOT NULL,
    product_id       BIGINT UNSIGNED NOT NULL,
    description      VARCHAR(500),
    quantity         DECIMAL(14,4)  NOT NULL,
    unit_price       DECIMAL(14,4)  NOT NULL,
    discount_pct     DECIMAL(6,4)   DEFAULT 0,
    line_total       DECIMAL(18,4)  NOT NULL,
    unit_of_measure  VARCHAR(20)    DEFAULT 'EA',
    line_order       TINYINT UNSIGNED DEFAULT 0,
    FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoices (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    invoice_number   VARCHAR(30)    NOT NULL,
    customer_id      BIGINT UNSIGNED NOT NULL,
    sales_order_id   BIGINT UNSIGNED NULL,
    invoice_date     DATE           NOT NULL,
    due_date         DATE           NOT NULL,
    status           ENUM('draft','sent','open','partial','paid','overdue','void','credited') DEFAULT 'draft',
    subtotal         DECIMAL(18,4)  DEFAULT 0,
    discount_amount  DECIMAL(18,4)  DEFAULT 0,
    tax_amount       DECIMAL(18,4)  DEFAULT 0,
    delivery_fee     DECIMAL(18,4)  DEFAULT 0,
    total            DECIMAL(18,4)  DEFAULT 0,
    amount_paid      DECIMAL(18,4)  DEFAULT 0,
    payment_terms    TINYINT        DEFAULT 30,
    tax_rate_id      BIGINT UNSIGNED,
    notes            TEXT,
    journal_entry_id BIGINT UNSIGNED,
    stripe_payment_link VARCHAR(500),
    created_by       BIGINT UNSIGNED,
    sent_at          TIMESTAMP NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY uq_invoice_number (company_id, invoice_number),
    INDEX idx_status   (company_id, status),
    INDEX idx_due      (company_id, due_date),
    INDEX idx_customer (company_id, customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoice_lines (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id       BIGINT UNSIGNED NOT NULL,
    company_id       BIGINT UNSIGNED NOT NULL,
    product_id       BIGINT UNSIGNED NOT NULL,
    lot_id           BIGINT UNSIGNED NULL,
    description      VARCHAR(500),
    quantity         DECIMAL(14,4)  NOT NULL,
    unit_price       DECIMAL(14,4)  NOT NULL,
    discount_pct     DECIMAL(6,4)   DEFAULT 0,
    discount_amount  DECIMAL(14,4)  DEFAULT 0,
    line_total       DECIMAL(18,4)  NOT NULL,
    cost_at_sale     DECIMAL(14,4)  COMMENT 'Costo FIFO/FEFO al momento de la venta',
    tax_rate         DECIMAL(8,5)   DEFAULT 0,
    tax_amount       DECIMAL(14,4)  DEFAULT 0,
    unit_of_measure  VARCHAR(20)    DEFAULT 'EA',
    line_order       TINYINT UNSIGNED DEFAULT 0,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE credit_memos (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id          BIGINT UNSIGNED NOT NULL,
    memo_number         VARCHAR(30)    NOT NULL,
    customer_id         BIGINT UNSIGNED NOT NULL,
    original_invoice_id BIGINT UNSIGNED NULL,
    memo_date           DATE           NOT NULL,
    reason              ENUM('return','price_adjustment','spoilage','shortage','other'),
    status              ENUM('draft','applied','void') DEFAULT 'draft',
    subtotal            DECIMAL(18,4)  DEFAULT 0,
    tax_amount          DECIMAL(18,4)  DEFAULT 0,
    total               DECIMAL(18,4)  DEFAULT 0,
    amount_applied      DECIMAL(18,4)  DEFAULT 0,
    notes               TEXT,
    journal_entry_id    BIGINT UNSIGNED,
    created_by          BIGINT UNSIGNED,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    UNIQUE KEY uq_memo_number (company_id, memo_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_gateways (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    provider       ENUM('stripe','paypal','square') NOT NULL,
    is_active      TINYINT(1) DEFAULT 1,
    mode           ENUM('sandbox','live') DEFAULT 'live',
    public_key     VARCHAR(255),
    webhook_secret VARCHAR(255),
    extra_config   JSON,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_company_provider (company_id, provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE online_payment_transactions (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id          BIGINT UNSIGNED NOT NULL,
    invoice_id          BIGINT UNSIGNED,
    customer_id         BIGINT UNSIGNED,
    gateway             ENUM('stripe','paypal','square') NOT NULL,
    external_id         VARCHAR(200)   NOT NULL,
    amount              DECIMAL(18,4)  NOT NULL,
    currency            CHAR(3)  DEFAULT 'USD',
    merchant_fee        DECIMAL(10,4)  DEFAULT 0,
    status              ENUM('pending','succeeded','failed','refunded','chargeback') DEFAULT 'pending',
    payment_method_type VARCHAR(50),
    last4               CHAR(4),
    refunded_amount     DECIMAL(18,4)  DEFAULT 0,
    refunded_at         TIMESTAMP NULL,
    journal_entry_id    BIGINT UNSIGNED,
    webhook_event       VARCHAR(100),
    raw_response        JSON,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_invoice  (invoice_id),
    INDEX idx_external (gateway, external_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE webhooks_log (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   BIGINT UNSIGNED,
    gateway      VARCHAR(30)  NOT NULL,
    event_type   VARCHAR(100) NOT NULL,
    payload      JSON,
    signature    VARCHAR(500),
    verified     TINYINT(1) DEFAULT 0,
    processed    TINYINT(1) DEFAULT 0,
    error        TEXT,
    received_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_gateway (gateway, event_type),
    INDEX idx_received (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;