USE contabilidade_core;

CREATE TABLE IF NOT EXISTS client_periods (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    fiscal_year_id INT UNSIGNED NOT NULL,
    period_year SMALLINT UNSIGNED NOT NULL,
    period_month TINYINT UNSIGNED NOT NULL,
    starts_on DATE NOT NULL,
    ends_on DATE NOT NULL,
    status ENUM('open','closed') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_client_period_month (client_id, period_year, period_month),
    KEY idx_client_period_status (status),
    CONSTRAINT fk_client_period_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_period_fiscal_year FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id),
    CONSTRAINT chk_client_period_month CHECK (period_month BETWEEN 1 AND 12),
    CONSTRAINT chk_client_period_dates CHECK (starts_on <= ends_on)
) ENGINE=InnoDB;

ALTER TABLE accounting_entries ADD COLUMN IF NOT EXISTS client_id INT UNSIGNED NULL AFTER company_id;
ALTER TABLE accounting_entries ADD COLUMN IF NOT EXISTS client_period_id INT UNSIGNED NULL AFTER client_id;

ALTER TABLE client_monthly_links ADD COLUMN IF NOT EXISTS client_period_id INT UNSIGNED NULL AFTER client_id;

INSERT IGNORE INTO client_periods (client_id, fiscal_year_id, period_year, period_month, starts_on, ends_on, status)
SELECT DISTINCT l.client_id, f.id, l.period_year, l.period_month,
       STR_TO_DATE(CONCAT(l.period_year, '-', LPAD(l.period_month, 2, '0'), '-01'), '%Y-%m-%d') AS starts_on,
       LAST_DAY(STR_TO_DATE(CONCAT(l.period_year, '-', LPAD(l.period_month, 2, '0'), '-01'), '%Y-%m-%d')) AS ends_on,
       'open'
FROM client_monthly_links l
JOIN fiscal_years f ON l.period_year BETWEEN YEAR(f.starts_on) AND YEAR(f.ends_on);

UPDATE client_monthly_links l
JOIN client_periods p ON p.client_id = l.client_id
    AND p.period_year = l.period_year
    AND p.period_month = l.period_month
SET l.client_period_id = p.id
WHERE l.client_period_id IS NULL;