CREATE DATABASE IF NOT EXISTS association_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE association_app;

CREATE TABLE IF NOT EXISTS associations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    address VARCHAR(255) NOT NULL,
    siret VARCHAR(14) NULL,
    rna VARCHAR(20) NULL,
    logo_path VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS association_years (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NOT NULL,
    start_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_association_years_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT uq_association_year UNIQUE (association_id, start_date)
);

CREATE TABLE IF NOT EXISTS member_associations (
    member_id INT UNSIGNED NOT NULL,
    association_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (member_id, association_id),
    CONSTRAINT fk_member_association_member
        FOREIGN KEY (member_id) REFERENCES members(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_member_association_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS year_memberships (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_year_id INT UNSIGNED NOT NULL,
    member_id INT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    membership_taken_at DATE NULL,
    membership_paid_at DATE NULL,
    payment_method ENUM('cheque', 'especes', 'virement') NOT NULL DEFAULT 'cheque',
    is_board_member TINYINT(1) NOT NULL DEFAULT 0,
    board_role VARCHAR(120) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_year_membership_year
        FOREIGN KEY (association_year_id) REFERENCES association_years(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_year_membership_member
        FOREIGN KEY (member_id) REFERENCES members(id)
        ON DELETE CASCADE,
    CONSTRAINT uq_year_membership_member UNIQUE (association_year_id, member_id)
);

CREATE TABLE IF NOT EXISTS events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NOT NULL,
    association_year_id INT UNSIGNED NOT NULL,
    event_type ENUM('reunion', 'ca', 'assemblee_generale', 'autre') NOT NULL DEFAULT 'reunion',
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    event_date DATE NOT NULL,
    event_time TIME NOT NULL,
    location VARCHAR(255) NULL,
    agenda TEXT NULL,
    report TEXT NULL,
    include_bureau TINYINT(1) NOT NULL DEFAULT 0,
    include_ca TINYINT(1) NOT NULL DEFAULT 0,
    include_adherents TINYINT(1) NOT NULL DEFAULT 0,
    include_contacts TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_events_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_events_association_year
        FOREIGN KEY (association_year_id) REFERENCES association_years(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS event_invitees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id INT UNSIGNED NOT NULL,
    member_id INT UNSIGNED NOT NULL,
    first_name VARCHAR(120) NOT NULL,
    last_name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NULL,
    invitation_sent_at DATE NULL,
    response_status ENUM('non_repondu', 'present', 'non_present') NOT NULL DEFAULT 'non_repondu',
    attendees_count INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_event_invitees_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_event_invitees_member
        FOREIGN KEY (member_id) REFERENCES members(id)
        ON DELETE CASCADE,
    CONSTRAINT uq_event_invitee UNIQUE (event_id, member_id)
);

CREATE TABLE IF NOT EXISTS treasury_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_treasury_category_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT uq_treasury_category UNIQUE (association_id, name)
);

CREATE TABLE IF NOT EXISTS treasury_entries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    association_id INT UNSIGNED NOT NULL,
    association_year_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    entry_date DATE NOT NULL,
    label VARCHAR(255) NOT NULL,
    event_id INT UNSIGNED NULL,
    category_id INT UNSIGNED NOT NULL,
    expense_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    income_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    year_membership_id INT UNSIGNED NULL,
    import_ref VARCHAR(190) NULL,
    receipt_path VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_treasury_entry_association
        FOREIGN KEY (association_id) REFERENCES associations(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_treasury_entry_year
        FOREIGN KEY (association_year_id) REFERENCES association_years(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_treasury_entry_account
        FOREIGN KEY (account_id) REFERENCES treasury_accounts(id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_treasury_entry_event
        FOREIGN KEY (event_id) REFERENCES events(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_treasury_entry_category
        FOREIGN KEY (category_id) REFERENCES treasury_categories(id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_treasury_entry_membership
        FOREIGN KEY (year_membership_id) REFERENCES year_memberships(id)
        ON DELETE SET NULL,
    CONSTRAINT uq_treasury_import_ref UNIQUE (association_id, account_id, import_ref)
);


ALTER TABLE year_memberships
    ADD COLUMN IF NOT EXISTS is_board_member TINYINT(1) NOT NULL DEFAULT 0 AFTER membership_paid_at,
    ADD COLUMN IF NOT EXISTS board_role VARCHAR(120) NULL AFTER is_board_member;

ALTER TABLE year_memberships
    ADD COLUMN IF NOT EXISTS payment_method ENUM('cheque', 'especes', 'virement') NOT NULL DEFAULT 'cheque' AFTER membership_paid_at;

ALTER TABLE year_memberships
    MODIFY membership_taken_at DATE NULL;

ALTER TABLE events
    ADD COLUMN IF NOT EXISTS include_bureau TINYINT(1) NOT NULL DEFAULT 0 AFTER agenda,
    ADD COLUMN IF NOT EXISTS include_ca TINYINT(1) NOT NULL DEFAULT 0 AFTER include_bureau,
    ADD COLUMN IF NOT EXISTS include_adherents TINYINT(1) NOT NULL DEFAULT 0 AFTER include_ca,
    ADD COLUMN IF NOT EXISTS include_contacts TINYINT(1) NOT NULL DEFAULT 0 AFTER include_adherents;

ALTER TABLE events
    ADD COLUMN IF NOT EXISTS report TEXT NULL AFTER agenda;

ALTER TABLE treasury_entries
    ADD COLUMN IF NOT EXISTS account_id INT UNSIGNED NOT NULL AFTER association_year_id;

ALTER TABLE treasury_entries
    ADD COLUMN IF NOT EXISTS import_ref VARCHAR(190) NULL AFTER year_membership_id;

-- Re-ventilation des ecritures sur les exercices comptables selon la date d'ecriture.
UPDATE treasury_entries te
INNER JOIN association_years ay
    ON ay.association_id = te.association_id
   AND te.entry_date >= ay.start_date
   AND te.entry_date < DATE_ADD(ay.start_date, INTERVAL 1 YEAR)
SET te.association_year_id = ay.id;

-- Controle: ecritures hors plage d'exercice (a traiter manuellement si resultat non vide).
SELECT te.id, te.association_id, te.entry_date
FROM treasury_entries te
LEFT JOIN association_years ay
    ON ay.id = te.association_year_id
WHERE ay.id IS NULL
   OR ay.association_id <> te.association_id
   OR te.entry_date < ay.start_date
   OR te.entry_date >= DATE_ADD(ay.start_date, INTERVAL 1 YEAR);
